Live Monitoring Queries to troubleshoot issues in SQL Server

Here, I would like to share one of my favorite Live monitoring DMV Scripts.

It contains two sessions as below. This has been very helpful to me while monitoring the SQL Server Production issues on the floor.

1. Identify the requests in the SQL Server those are currently running

SELECT  r.session_id ,Getdate() 'CurrentDateTime',r.blocking_session_id 'Blocking Session ID',
	(Select Top 1 CON.client_net_address From sys.dm_exec_connections AS CON 
	where CON.session_id = r.session_id) Client_Address,
		Coalesce(Quotename(Db_name(qt.dbid)) + N'.' + Quotename(Object_schema_name(qt.objectid, qt.dbid)) + N'.' +      
				Quotename(Object_name(qt.objectid, qt.dbid)), '') AS command_text,
        r.[status] ,
        r.wait_type ,wait_resource,r.wait_time / (1000.0) 'Wait Time (in Sec)',
        r.scheduler_id ,
        SUBSTRING(qt.[text], r.statement_start_offset / 2,
            ( CASE WHEN r.statement_end_offset = -1
                   THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                   ELSE r.statement_end_offset
              END - r.statement_start_offset ) / 2) AS [statement_executing] ,
        DB_NAME(qt.[dbid]) AS [DatabaseName] ,
        r.cpu_time ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)',
        r.reads ,r.writes ,r.logical_reads ,
        ,s.login_name ,s.host_name ,s.program_name,s.host_process_id ,s.last_request_end_time,s.login_time
FROM    sys.dm_exec_sessions AS s
		INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE   r.session_id > 50

2. Identify relevant information like blocking/wait type/queryplan etc.

    CASE [DOWT].[wait_type]
            RIGHT ([DOWT].[resource_description],
            CHARINDEX (N'=', REVERSE ([DOWT].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],[DES].[program_name],[DEST].text,
FROM sys.dm_os_waiting_tasks [DOWT]
INNER JOIN sys.dm_exec_sessions [DES] ON
    [DOWT].[session_id] = [DES].[session_id]
INNER JOIN sys.dm_exec_requests [DER] ON
    [DES].[session_id] = [DER].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([DER].[sql_handle]) [DEST]
OUTER APPLY sys.dm_exec_query_plan ([DER].[plan_handle]) [DEQP]
WHERE [DES].[is_user_process] = 1
ORDER BY [DOWT].[session_id], [DOWT].[exec_context_id];

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

5 thoughts on “Live Monitoring Queries to troubleshoot issues in SQL Server”

  1. This awesome query really saved a lot of time for me, when analyzing a performance issue in my Data warehouse environment.
    Really hats off. Thank you very much.


Leave a Reply

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

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

Facebook photo

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

Connecting to %s