Find lead blocker on a SQL Server Instance

We can use sp_who2 active, sys.dm_exec_requests , sysprocesses or sp_lock to get information about the locks. When there are large number of blocks on a server and one needs to figure out what is the root SPID that is causing all this – We are looking for a lead blocker.

select a.spid, a.blocked, b.spid, b.blocked from sys.sysprocesses a inner join sys.sysprocesses b on b.spid= a.blocked and b.blocked = 0 and a.spid<> a.blocked

Once you have the lead blocker SPID , you can see the statement using DBCC INPUTBUFFER (SPID) or use the below script to get the SQL Text of lead blocker in a single go.

SELECT

  

    spid 

  

    ,sp.status 

  

    ,loginame   = SUBSTRING(loginame, 1, 12) 

  

    ,hostname   = SUBSTRING(hostname, 1, 12) 

  

    ,blk        = CONVERT(char(3), blocked) 

  

    ,open_tran 

  

    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10) 

  

    ,cmd 

  

    ,waittype 

  

    ,waittime 

  

    ,last_batch 

  

    ,SQLStatement       = 

  

        SUBSTRING

  

        ( 

  

            qt.text, 

  

            er.statement_start_offset/2, 

  

            (CASE WHEN er.statement_end_offset = -1 

  

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 

  

                ELSE er.statement_end_offset 

  

                END - er.statement_start_offset)/2 

  

        ) 

  

FROM master.dbo.sysprocesses sp 

  

LEFT JOIN sys.dm_exec_requests er 

  

    ON er.session_id = sp.spid 

  

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt 

  

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) 

  

AND blocked = 0

Identifying Queries with Lowest Plan Reuse

Lowest Plan Reuse

One of the advantages of using stored procedures is that the query plan is cached and can be reused without compiling the query. This saves time, resources, and improves performance. You can identify the query plans that have the lowest reuse to further investigate why the plans are not being reused. You may find that some can be rewritten to optimize reuse.

SELECT TOP 10


[Plan usage] = cp.usecounts

,[Individual Query] = 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)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,cp.cacheobjtype

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

WHERE cp.plan_handle=qs.plan_handle

ORDER BY [Plan usage] ASC;

Identifying Queries Most Often Blocked

SELECT TOP 10


[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count

,[Total Time Blocked] = total_elapsed_time - total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = 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)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average Time Blocked] DESC;

Identifying Most Costly CLR Queries

SELECT TOP 10


[Average CLR Time] = total_clr_time / execution_count

,[Total CLR Time] = total_clr_time

,[Execution count] = qs.execution_count

,[Individual Query] = 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)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

WHERE total_clr_time <> 0

ORDER BY [Average CLR Time] DESC;

Identifying Most Costly Queries by CPU

SELECT TOP 10


[Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = 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)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC;

Identifying Most Costly Queries by I/O

SELECT TOP 10


[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

,[Total IO] = (total_logical_reads + total_logical_writes)

,[Execution count] = qs.execution_count

,[Individual Query] = 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)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average IO] DESC;

Identifying Logically Fragmented Indexes

-- Create required table structure only.


-- Note: this SQL must be the same as in the Database loop given in the -- following step.

SELECT TOP 1

DatbaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

INTO #TempFragmentation

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.[object_id] = -999 -- Dummy value just to get table structure.

;



-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempFragmentation

SELECT TOP 10

DatbaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

ORDER BY [Fragmentation %] DESC

;

'



-- Select records.

SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC

-- Tidy up.

DROP TABLE #TempFragmentation

Identifying the Most Costly Indexes

-- Create required table structure only.


-- Note: this SQL must be the same as in the Database loop given in the following step.

SELECT TOP 1

[Maintenance cost] = (user_updates + system_updates)

,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

INTO #TempMaintenanceCost

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND (user_updates + system_updates) > 0 -- Only report on active rows.

AND s.[object_id] = -999 -- Dummy value to get table structure.

;



-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempMaintenanceCost

SELECT TOP 10

[Maintenance cost] = (user_updates + system_updates)

,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND (user_updates + system_updates) > 0 -- Only report on active rows.

ORDER BY [Maintenance cost] DESC

;

'



-- Select records.

SELECT TOP 10 * FROM #TempMaintenanceCost

ORDER BY [Maintenance cost] DESC

-- Tidy up.

DROP TABLE #TempMaintenanceCost

Find out the Unused Indexes

-- Create required table structure only.


-- Note: this SQL must be the same as in the Database loop given in the following step.

SELECT TOP 1

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

-- Useful fields below:

--, *

INTO #TempUnusedIndexes

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND s.[object_id] = -999 -- Dummy value to get table structure.

;



-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempUnusedIndexes

SELECT TOP 10

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND i.name IS NOT NULL -- Ignore HEAP indexes.

ORDER BY user_updates DESC

;

'



-- Select records.

SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC

-- Tidy up.

DROP TABLE #TempUnusedIndexes

Find out Costly Missing Indexes

SELECT TOP 10


[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

, avg_user_impact

, TableName = statement

, [EqualityUsage] = equality_columns

, [InequalityUsage] = inequality_columns

, [Include Cloumns] = included_columns

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s

ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d

ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC;

Missing Indexes by Database

SELECT


DatabaseName = DB_NAME(database_id)

,[Number Indexes Missing] = count(*)

FROM sys.dm_db_missing_index_details

GROUP BY DB_NAME(database_id)

ORDER BY 2 DESC;

Identifying the Most Reads and Writes

SELECT TOP 10


[Total Reads] = SUM(total_logical_reads)

,[Execution count] = SUM(qs.execution_count)

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Reads] DESC;



SELECT TOP 10

[Total Writes] = SUM(total_logical_writes)

,[Execution count] = SUM(qs.execution_count)

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Writes] DESC;

SQL Query Records Causes of Wait Times

SELECT TOP 10


[Wait type] = wait_type,

[Wait time (s)] = wait_time_ms / 1000,

[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0

/ SUM(wait_time_ms) OVER())

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

ORDER BY wait_time_ms DESC;