Which DMV should I use?

Ever wondered which dynamic management view gives you information about xxxxx?

e.g. you know that you want to find out which processes are currently blocking other processes (which was handled by the “blocked” column of “sysprocesses” in older SQL versions) but which view now holds this data?

If you simply join the system_views to the system_columns and filter on the column name (with a wild card) you will see all the dmv’s that contain that column name.

Using this technique you should be able to guess which of these is the view you want.

SELECT
AV.name
FROM          sys.system_views AV
INNER JOIN    sys.system_columns C ON AV.object_id = C.object_id
WHERE         C.name like '%block%'

  • dm_exec_requests
  • dm_xe_sessions
  • dm_os_waiting_tasks
  • dm_os_waiting_tasks
  • dm_os_waiting_tasks
  • dm_resource_governor_workload_groups
  • xml_schema_elements
  • xml_schema_elements
  • xml_schema_elements
  • xml_schema_types
  • xml_schema_types
  • sysprocesses

The correct answer in this case is “dm_exec_requests”

If you want to join this to find out who the user is then query with filter = ‘Login’ and you’ll get ‘dm_exec_sessions’ in the list (and some other close but not relevant answers).

Its not an infallible method but it’s better than guessing and probably quicker than using help!

To speed things up more add this to your tools in SSMS

To speed things up more add this to your tools in SSMS
Create a stored procedure in your admin database if you have one (or put it in master db)

-- =============================================
-- Author:David Bridge
-- Create date:October 2011
-- Description:Find a DMV with a column name like this @SearchText
-- =============================================
CREATE PROCEDURE DBA_FindDMV
@SearchText varchar(128)
AS
BEGIN
SET NOCOUNT ON;
SELECT AV.name
FROM sys.system_views AV
INNER JOIN sys.system_columns C ON AV.object_id = C.object_id
WHERE C.name like '%' + @SearchText + '%'
END

Then add it in SSMS using menu:tools->options->environment->keyboard and put the fully qualified procedure name in the keyboard shortcuts. E.g. CTRL+4 = admin.dbo.DBA_FindDMV

Now in a query window just type the search text, highlight it and press CTRL+4

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s