I wanted to post the first T-SQL script from my DMV Emergency Room presentation on August 21 in Nashville. This is designed to help you gather initial triage information about what is happening on your database instance, and then start to gather more detailed information about a particular aspect (such as CPU or I/O) that might be the primary issue (based on the initial triage queries).
-- SQL Server 2008 and 2008 R2 DMV Emergency Queries -- Glenn Berry -- August 2010 -- http://glennberrysqlperformance.spaces.live.com/ -- Twitter: GlennAlanBerry -- Step 1 - Check Task Manager. -- Are all CPUs above 90-95% for an extended period of time? -- Is the CPU pressure caused by SQL Server? -- If yes, run CPU Related Queries below: -- Step 2 -- Initial Triage Queries -- Run appropriate queries to further investigate based on results -- Step 3 - Check Performance Monitor -- SQL Server Buffer Manager: Page Life Expectancy -- SQL Server Memory Manager: Memory Grants Pending -- Physical Disk: Avg disk sec/Read and Avg disk sec/Write -- ******* Start of Initial Triage Queries *************************************************** -- Isolate top waits for server instance since last restart or statistics clear WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK' ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE' ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT' ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold -- Clear Wait Stats (consider clearing wait stats and running wait stats query again after a few minutes) -- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); -- *** Some CPU Related Waits *** -- SOS_SCHEDULER_YIELD -- THREADPOOL -- CX_PACKET -- *** Some I/O Related Waits *** -- WRITELOG -- IO_COMPLETION -- ASYNC_IO_COMPLETION -- PAGEIOLATCH_SH -- PAGEIOLATCH_EX -- *** Some Locking Waits *** -- LCK_M_IX -- LCK_M_IU -- LCK_M_S -- Avg Current Tasks Count, Avg Runnable Tasks Count, Avg Pending Disk IO Count -- Run multiple times SELECT AVG(current_tasks_count) AS [Avg Task Count], AVG(runnable_tasks_count) AS [Avg Runnable Task Count], AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount] FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255; -- Sustained values above 10 bear further investigation -- High current tasks are often a symptom of locking/blocking -- High runnable tasks are often a symptom of CPU pressure -- High pending disk IO counts relate to I/O pressure -- Actual counts by scheduler (is it all schedulers, or just one?) -- Run multiple times SELECT scheduler_id, current_tasks_count , runnable_tasks_count, pending_disk_io_count FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255; -- Sustained values above 10 bear further investigation -- Get a snapshot of current activity -- Run multiple times SELECT LTRIM (st.[text]) AS [Command Text],[host_name], der.session_id AS [SPID], der.[status], db_name(database_id) AS [Database Name], ISNULL(der.wait_type, 'None') AS [Wait Type], der.logical_reads, der.cpu_time, der.total_elapsed_time FROM sys.dm_exec_requests AS der INNER JOIN sys.dm_exec_connections AS dexc ON der.session_id = dexc.session_id INNER JOIN sys.dm_exec_sessions AS dexs ON dexs.session_id = der.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st WHERE der.session_id >= 51 AND der.session_id <> @@spid -- eliminate this connection ORDER BY der.[status] -- Snapshot of current load SELECT node_id, node_state_desc, online_scheduler_count, idle_scheduler_count, active_worker_count, avg_load_balance FROM sys.dm_os_nodes WHERE node_state_desc <> N'ONLINE DAC'; -- Get connection counts (are they higher than normal?) SELECT login_name, COUNT(session_id) AS [session_count] FROM sys.dm_exec_sessions WITH (NOLOCK) GROUP BY login_name ORDER BY COUNT(session_id) DESC; -- Missing Indexes on current instance by Index Advantage SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], migs.last_user_seek, mid.[statement] AS [Database.Schema.Table], mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle ORDER BY index_advantage DESC; -- ******* End of Initial Triage Queries ***************************************************** -- Run these queries if you see evidence of CPU pressure -- ******** Start of CPU Related Queries ****************************************************** -- Recent CPU Utilization History (SQL 2008/2008 R2 Only) DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); SELECT TOP(144) SQLProcessUtilization AS [SQLServerProcessCPUUtilization], SystemIdle AS [SystemIdleProcess], 100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS x ) AS y ORDER BY record_id DESC; -- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats; -- Get the most CPU intensive queries SET NOCOUNT ON; DECLARE @SpID smallint; DECLARE @lastbatch datetime; DECLARE spID_Cursor CURSOR FAST_FORWARD FOR SELECT TOP(25) spid, last_batch FROM master..sysprocesses WHERE [status] = 'runnable' AND spid > 50 -- Eliminate system SPIDs AND spid <> @@SPID -- Eliminate this connection ORDER BY CPU DESC; OPEN spID_Cursor; FETCH NEXT FROM spID_Cursor INTO @spID, @lastbatch; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Spid #: ' + LTRIM(STR(@spID)) + ' - Last Batch ' + CONVERT(varchar(20),@lastbatch); EXEC ('DBCC INPUTBUFFER (' + @spID + ')'); FETCH NEXT FROM spID_Cursor INTO @spID, @lastbatch; END CLOSE spID_Cursor; DEALLOCATE spID_Cursor; -- End of get the most CPU intensive queries -- Switch to a particular database USE YourDatabaseName; GO -- Top Cached SPs By Total Worker time. Worker time relates to CPU cost SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_worker_time DESC; -- Top Cached SPs By Execution Count SELECT p.name AS [SP Name], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.execution_count DESC; -- Get most CPU expensive SPs and queries SELECT q.[text], hcpu.total_worker_time, hcpu.plan_handle, q.[dbid], q.objectid, q.number FROM (SELECT TOP (50) qs.plan_handle, qs.total_worker_time FROM sys.dm_exec_query_stats AS qs ORDER BY qs.total_worker_time DESC) AS hcpu CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q ORDER BY hcpu.total_worker_time DESC; -- Look for the most expensive Hash Matches DECLARE @op VARCHAR(30) = 'Hash Match'; SELECT st.[text], qs.Execution_Count, qs.last_execution_time, qs.total_worker_time, qs.total_elapsed_time, qs.total_logical_reads, qs.total_logical_writes, p.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') = 1 AND st.[dbid] = DB_ID() ORDER BY qs.total_worker_time DESC; -- ******** End of CPU Related Queries *********************************************************** -- Run these queries if you see evidence of memory pressure -- ******** Start of Memory Related Queries ****************************************************** -- Get Page Life Expectancy SELECT cntr_value AS [Page Life Expectancy] FROM sys.dm_os_performance_counters WHERE object_name = N'SQLServer:Buffer Manager' -- This is for the default instance AND counter_name = N'Page life expectancy'; -- Operating System memory amounts and state SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, available_page_file_kb, system_memory_state_desc FROM sys.dm_os_sys_memory; -- SQL Server Process Address space info --(shows whether locked pages is enabled, among other things) SELECT physical_memory_in_use_kb,locked_page_allocations_kb, page_fault_count, memory_utilization_percentage, available_commit_limit_kb, process_physical_memory_low, process_virtual_memory_low FROM sys.dm_os_process_memory; -- Memory Clerk Usage for instance SELECT TOP(20) [type], [name], SUM(single_pages_kb) AS [SPA Mem, Kb] FROM sys.dm_os_memory_clerks GROUP BY [type], [name] ORDER BY SUM(single_pages_kb) DESC; -- Find single-use, ad-hoc queries that are bloating the plan cache SELECT TOP(100) [text], cp.size_in_bytes FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cp.cacheobjtype = N'Compiled Plan' AND cp.objtype = N'Adhoc' AND cp.usecounts = 1 ORDER BY cp.size_in_bytes DESC; SELECT usecounts, SUM(size_in_bytes) AS [size_of_N_use_adhoc_plans_in_bytes] FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cacheobjtype = N'Compiled Plan' AND objtype = N'Adhoc' AND usecounts BETWEEN 1 AND 10 GROUP BY usecounts; -- Breaks down buffers used by current database by object (table, index) in the buffer cache SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [BufferCount], p.data_compression_desc AS [CompressionType] FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id WHERE b.database_id = DB_ID() AND p.[object_id] > 100 GROUP BY p.[object_id], p.index_id, p.data_compression_desc ORDER BY [BufferCount] DESC; -- Switch to a particular database USE YourDatabaseName; GO -- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_reads DESC; -- Look for the most expensive Clustered Index Scans DECLARE @op VARCHAR(30) = 'Clustered Index Scan'; SELECT st.[text], qs.Execution_Count, qs.last_execution_time, qs.total_worker_time, qs.total_elapsed_time, qs.total_logical_reads, qs.total_logical_writes, p.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') = 1 AND st.[dbid] = DB_ID() ORDER BY qs.total_logical_reads DESC; GO -- Look for the most expensive Table Scans DECLARE @op VARCHAR(30) = 'Table Scan'; SELECT st.[text], qs.Execution_Count, qs.last_execution_time, qs.total_worker_time, qs.total_elapsed_time, qs.total_logical_reads, qs.total_logical_writes, p.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') = 1 AND st.[dbid] = DB_ID() ORDER BY qs.total_logical_reads DESC; GO -- Look for the most expensive Index Scans DECLARE @op VARCHAR(30) = 'Index Scan'; SELECT st.[text], qs.Execution_Count, qs.last_execution_time, qs.total_worker_time, qs.total_elapsed_time, qs.total_logical_reads, qs.total_logical_writes, p.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') = 1 AND st.[dbid] = DB_ID() ORDER BY qs.total_logical_reads DESC; GO -- ******** End of Memory Related Queries ******************************************************** -- Run these queries if you see evidence of I/O pressure -- ******** Start of I/O Related Queries ********************************************************* -- Calculates average stalls per read, per write, and per total input/output for each database file. SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC; -- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database], mf.physical_name, r.io_pending, r.io_pending_ms_ticks, r.io_type, fs.num_of_reads, fs.num_of_writes FROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id ORDER BY r.io_pending, r.io_pending_ms_ticks DESC; -- Find queries that generate the most I/O SELECT TOP(10)(total_logical_reads/execution_count) AS [avg_logical_reads], (total_logical_writes/execution_count) AS [avg_logical_writes], (total_physical_reads/execution_count) AS [avg_phys_reads], execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, (SELECT query_plan FROM sys.dm_exec_query_plan(plan_handle)) AS query_plan FROM sys.dm_exec_query_stats ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC; SELECT io_type, io_pending, io_pending_ms_ticks FROM sys.dm_io_pending_io_requests -- Switch to a particular database USE YourDatabaseName; GO -- Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_physical_reads, qs.total_logical_reads DESC; -- Top Cached SPs By Total Logical Writes (SQL 2008). -- Logical writes relate to both memory and disk I/O pressure SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_writes DESC; -- Lists the top statements by average input/output usage for the current database SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name], (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], SUBSTRING(qt.[text],qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [Query Text] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.[dbid] = DB_ID() ORDER BY [Avg IO] DESC; -- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads], user_updates - (user_seeks + user_scans + user_lookups) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > (user_seeks + user_scans + user_lookups) AND i.index_id > 1 ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC; -- ******** End of I/O Related Queries *********************************************************** -- Detect blocking (more complete) SELECT t1.resource_type AS [lock type],db_name(resource_database_id) AS [database], t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], --- lock requested t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], -- spid of waiter (SELECT [text] FROM sys.dm_exec_requests AS r -- get sql for waiter CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = t1.request_session_id) AS [waiter_batch], (SELECT 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) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id = t1.request_session_id) AS [waiter_stmt], -- statement blocked t2.blocking_session_id AS [blocker sid], -- spid of blocker (SELECT [text] FROM sys.sysprocesses AS p -- get sql for blocker CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt] FROM sys.dm_tran_locks AS t1 INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address;
