Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DMV Emergency Queries From SQL Saturday #51 in Nashville

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;

 

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.