A Microsoft SQL Server DMV/DMF Cheat-Sheet (Part 2)

  • Article

Written by Mohit Gupta, Microsoft Premier Field Engineer.

In my last post I talked about how to get the list connections (e.g. who is connected and requests are they running on SQL Server).  I talked about how to use dm_exec_connections, dm_exec_sessions and dm_exec_requests, we saw how powerful these DMVs where.  Now to expand on where I left off.

STATEMENT #4: GET A LISTING O ALL USER CONNECTIONS WITH THEIR REQUEST DETAILS

(Copied from Part 1):

 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

SQL’s Statement #4 gave us who was connected, but not what they executed.  But we had a key field called sql_handle.  How do we use that?

Remember in old days we would use the SPID and DBCC INPUTBUFFER() to see what the SPID executed last?  The new and improved way is to use sys.dm_exec_sql_text; why do I care about this?

  • DBCC INPUTBUFFER() is easier to type however goal of these posts is to build reliance and familiarity with DMV/DMFs.
  • By creating a join to sys.dm_exec_sql_text I can get the last SQL text executed for every session vs. the DBCC INPUTBUFFER only a single session.

Statement #5: GET LIST OF ALL SQL STATEMENTS WITH CONNECTION INFORMATION

 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 
 , st.text AS LastSQLStatement 
 , r.cpu_time 
 , r.reads 
 , r.writes 
 , r.logical_reads 
 , r.total_elapsed_time 
 FROM sys.dm_exec_connections c 
 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st 
 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

A little easier to read, replace the sql_handle with LastSQLStatement.  So the output looks like below:

SQL Output

Next column I am going to expand on is node_affinity.  This column lets us know type of scheduler a session is using, which can be very useful if someone is using our Dedicated Administrator Connection (DAC).  Please note there only a SINGLE DAC connection available, if someone is using it by default for their day-to-day task, without the node_affinity you can’t figure out who is using that.  By diving into sys.dm_os_nodes; we can get additional details.

Statement #6: GET TYPE OF CONNECTION BEING USED, NORMAL VERUS DAC

 SELECT c.session_id 
 , c.auth_scheme 
 , CASE WHEN n.node_state_desc = 'ONLINE DAC' THEN 
 '*** DAC ***' 
 ELSE 
 'NORMAL' 
 END AS ConnectionType 
 , 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 
 , st.text AS LastSQLStatement 
 , r.cpu_time 
 , r.reads 
 , r.writes 
 , r.logical_reads 
 , r.total_elapsed_time 
 FROM sys.dm_exec_connections c 
 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st 
 INNER JOIN sys.dm_exec_sessions s 
 ON c.session_id = s.session_id 
 INNER JOIN sys.dm_os_nodes n 
 ON n.node_id = c.node_affinity 
 LEFT JOIN sys.dm_exec_requests r 
 ON c.session_id = r.session_id

Now if I open up a DAC connection, we get a output similar to below:

SQL Output

Now it would be painful to type Statement #6 every time manually; so we can create a view on top of this and save it in master database to allow us to access it from anywhere. I prefer view over stored procedure (sp_who) for this because I would like to be able to filter and join on fly.  Make sure the view name starts with “sp_”.  Yes it sounds weird, however objects that start with sp_ are special, in that you can access these from anywhere (as long as they are in master” [Reference].

This was a short post finishing off last post; we learned how to get SQL Text based on SQL_HANDLE and find out who is using our very important DAC connection.  Next post I’ll dig deeper into DMV.  Stay tuned.