Reusable Components – Script to Identify Blocking and Missing Indexes in SQL Server.

Max Blocks
–NOTE: From the results in the below script, pick out the Stored Proc from the parent query and
–underlying tables from the individual query columns..

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
–WHERE qt.dbid IN(35,39) —Chg db_id as reqd.or completely comment the where filter

ORDER BY [Average Time Blocked] DESC;

–Missing Index count

SELECT
@@servername as ServerName
,DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*)
,[Date Captured] = getdate()

FROM sys.dm_db_missing_index_details
–WHERE database_id IN(35,39)
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

  • January 3, 2018 | 14 views
  • Comments