Live Monitoring Queries to troubleshoot issues in SQL Server

Here, I would like to share one of my favourite 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 ,
        r.plan_handle,r.sql_handle
        ,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

SELECT
    [DOWT].[session_id],[DOWT].[exec_context_id],
    [DOWT].[wait_duration_ms],[DOWT].[wait_type],
    [DOWT].[blocking_session_id],[DOWT].[resource_description],
    CASE [DOWT].[wait_type]
        WHEN N'CXPACKET' THEN
            RIGHT ([DOWT].[resource_description],
            CHARINDEX (N'=', REVERSE ([DOWT].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],[DES].[program_name],[DEST].text,
    [DER].[database_id],[DEQP].[query_plan],[DER].[cpu_time]
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];

Hope, you will also find the above queries helpful!!!

Advertisements

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

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