Partilhar via

A Microsoft SQL Server DMV/DMF Cheat-Sheet

  • Artigo

I am asked this question by Microsoft SQL Server customers again and again: “What dynamic management views (DMV) or dynamic management functions (DMF) should I use?” The short answer is, as many as you need. The detailed answer is, well, it depends. There are well over 150 DMV/DMFs spread across 20 some categories in 2012 now, and it is a daunting challenge trying to remember all these. I don’t think I have used all of these, I don’t think I ever will. The use of these really stems from what issues you are trying to deal with or what information you need from SQL Server. Since the use for most DMVs is scenarios based, I am listing here DMV/DMFs that I think every DBA should get familiar with. Now I am not saying this is an exhaustive list, but I find it a good starting point.

SQL Server DMV/DMFs that every DBA should know

SQL Server 2012 Dynamic Management Views and Functions

Execution Related

  • sys.dm_exec_connections
  • sys.dm_exec_sessions
  • sys.dm_exec_requests
  • sys.dm_exec_cached_plans
  • sys.dm_exec_query_plans
  • sys.dm_exec_sql_text
  • sys.dm_exec_query_stats

Index Related

  • sys.dm_db_index_physical_stats
  • sys.dm_db_index_usage_stats

SQL Server Operating System

  • sys.dm_os_performance_counters
  • sys.dm_os_schedulers
  • sys.dm_os_nodes
  • sys.dm_os_waiting_tasks
  • sys.dm_os_wait_stats

I/O Related

  • sys.dm_io_virtual_file_stats

Using DMV/DMFs

Instead of just giving you random SQL statements against the DMV/DMFs listed above, I am going to walk through the DMV and build the corresponding SQL statement based on information I want to collect.

For starters we will look to answer the following question: who is connected to SQL Server? We have in particular two DMVs to give us that information; sys.dm_exec_connections and sys.dm_exec_sessions. The sys.dm_exec_connections returns information for only user connections, whereas sys.dm_exec_sessions returns information for both user connections and system sessions. There is one-to-one relationship between the two DMVs. When looking at just the sys.dm_exec_sessions we can identify the system sessions by looking at the session_id column; values greater than or equal to 51 are user connections and be low are system processes.

Statement #1: Get a Listing of All Connections:
 SELECT *
 FROM sys.dm_exec_connections

Statement #2: Get a Listing of All Sessions

 SELECT *
 FROM sys.dm_exec_sessions

Both statements return everything from the DMV, sample output is below.

Output of Statement #1:

clip_image002

Output of Statement #2:

clip_image004

My default DMV returns too much information, so I am going to re-write both statements to return the information I am most interested in.

Statement #3: Get a listing of all user connections:
  SELECT c.session_id
 , c.auth_scheme
 , c.node_affinity
 , s.login_name
 , db_name(s.database_id) AS database_name
 , CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'Read Uncomitted'
 WHEN 2 THEN 'Read Committed'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END AS transaction_isolation_level
 , s.status
 , c.most_recent_sql_handle
 FROM sys.dm_exec_connections c
 INNER JOIN sys.dm_exec_sessions s
 ON c.session_id = s.session_id

So I have combined Statement #1 and Statement #2 into above SQL statement, selecting the fields with key information.

Field Name Reason For Selection
auth_scheme Let us know what protocol they are coming in on: NTML, KERBEROS, or SQL.
node_affinity This is important to find out who is using the DAC connection (I’ll explain below).
transaction_isolation_level I want to know this, especially if I am dealing with a Blocking Issue and it is because users are using Serializable isolation level.
most_recent_sql_handle Now yes we still have DBCC INPUTBUFFER (); however I like this a lot better because I can join into other DMVs; will show you examples later.

Note we are only getting user connection information; in case we needed details for system sessions we will have to change INNER JOIN to RIGHT OUTER JOIN instead.

Now we know who is connected, but we also want to know what are they doing? The STATUS field in the sys.dm_exec_sessions can tell us if the session is actively running something or is sleeping. Sleeping means the last task on the session has completed and is waiting for next batch of work (aka Idle Connection). And if it’s running we can find out what is the being run by the session, by diving into sys.dm_exec_requests; again I am going to expand Stmt3 to include new DMV now.

Statement #4: Get a listing of all user connections with their request details:
  SELECT c.session_id
 , c.auth_scheme
 , c.node_affinity
 , r.scheduler_id
 , s.login_name
 , db_name(s.database_id) AS database_name
 , CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'Read Uncomitted'
 WHEN 2 THEN 'Read Committed'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END AS transaction_isolation_level
 , s.status AS SessionStatus
 , r.status AS RequestStatus
 , CASE WHEN r.sql_handle IS NULL THEN
 c.most_recent_sql_handle
 ELSE
 r.sql_handle
 END AS sql_handle
 , r.cpu_time
 , r.reads
 , r.writes
 , r.logical_reads
 , r.total_elapsed_time
 FROM sys.dm_exec_connections c
 INNER JOIN sys.dm_exec_sessions s
 ON c.session_id = s.session_id
 LEFT JOIN sys.dm_exec_requests r
 ON c.session_id = r.session_id

I think this SQL statement gives us enough information to see what is going on in SQL Server. If we needed to dig in further we can use other DMVs or this one. So one question I often get asked is why use all these DMV? Why not sys.sysprocessesor sp_who or sp_who2?

  • Sysprocesses will be phased on in a future version, so it’s better to get use to the DMV before it is completely gone.
  • Sp_who and sp_who2 do not provide as much information as the DMV/DMF. I have taken out a lot of the information from each DMV/DMF, but after you know what you are looking for you can always dig further into the DMV/DMFs.

In this article we looked at getting information on who is connected and what request they are running. In my next post I’ll talk about how to get the T-SQL Code they are running and even the execution plans for those statements.

Also available on OpsVault.com and WordPress Blog.

Comments

  • Anonymous
    February 03, 2015
    very good explanation. really like it thanks

  • Anonymous
    February 23, 2015
    Kuch khas nahi ... Thik thik che bhaila

  • Anonymous
    June 29, 2015
    Could you please explain how data is stored inside a DMV? and How many possible rows that we can see inside a DMV

  • Anonymous
    June 29, 2015
    Kishor, DMV represent the memory structures with in SQL Server.  Therefore there is no hard limit on number of rows that can be stored within DMV.  For example, sys.dm_os_schedulers will return 1 row per visisual CPU + internal schedulers.  sys.dm_os_nodes will return 1 row per numa node with in the server + 1 for internal numa node created for sql server. Only DMV that have limitation really are sys.dm_db_missing_index_*. I hope I answered your question.  Cheers! -- Mohit.

  • Anonymous
    January 07, 2016
    The comment has been removed

  • Anonymous
    January 11, 2016
    Sorry for late reply ElVino; agreed.  r.database_id is more appropriate there then s.database_id. Thanks.

  • Anonymous
    January 28, 2016
    Hi How do I get to your next blog? Regards

  • Anonymous
    January 29, 2016
    Hi David,   Sorry I never finished this series.  Please keep an eye out on this blog / http://sqlcan.wordpress.com.  I'll be continuing or revamping the series for newer version of SQL Server 2016. Thanks.