SQL Queries for DMVs
INDEX DMVs
1. Find fill factor of the indexes on the tables in the current databas
SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , i.fill_factor 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 ORDER BY fill_factor DESC
2. Locate fragmentation percentage of the indexes on a table within a database named warehousedw
SELECT i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] FROM sys.dm_db_index_physical_stats(DB_ID(‘warehousedw’), OBJECT_ID('table'), NULL, NULL, NULL) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] ANDs.index_id = i.index_id
3. Identifying the most important missing indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Cost] , d.[statement] AS [Table Name] , equality_columns , inequality_columns , 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
4. The most-costly unused indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC' SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes
5. Finding the top high-maintenance indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , (s.user_updates ) AS [update usage] , (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage] , (s.user_updates) - (s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance cost] , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] , s.last_user_seek , s.last_user_scan , s.last_user_lookup 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempMaintenanceCost SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , (s.user_updates ) AS [update usage] , (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage] , (s.user_updates) - (s.user_seeks + user_scans + s.user_lookups) AS [Maintenance cost] , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] , s.last_user_seek , s.last_user_scan , s.last_user_lookup 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND (s.user_seeks + s.user_scans + s.user_lookups) > 0 ORDER BY [Maintenance cost] DESC' SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC DROP TABLE #TempMaintenanceCost
6. Finding the most-used indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage] , s.user_updates , i.fill_factor INTO #TempUsage 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUsage SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage] , s.user_updates , i.fill_factor 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Usage] DESC' SELECT TOP 20 * FROM #TempUsage ORDER BY [Usage] DESC DROP TABLE #TempUsage
7. Finding the most-fragmented indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempFragmentation SELECT TOP 20 DB_NAME() AS DatbaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 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 INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Fragmentation %] DESC' SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC DROP TABLE #TempFragmentation
8. The databases with the most missing indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(database_id) AS DatabaseName , COUNT(*) AS [Missing Index Count] FROM sys.dm_db_missing_index_details GROUP BY DB_NAME(database_id) ORDER BY [Missing Index Count] DESC
9. Indexes that aren’t used at all
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.name AS IndexName INTO #TempNeverUsedIndexes FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.NAME AS IndexName FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id AND I.index_id = S.index_id AND DATABASE_ID = DB_ID() WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 AND I.name IS NOT NULL AND S.object_id IS NULL' SELECT * FROM #TempNeverUsedIndexes ORDER BY DatbaseName, SchemaName, TableName, IndexName DROP TABLE #TempNeverUsedIndexes
10. Finding the state of your statistics
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT ss.name AS SchemaName , st.name AS TableName , s.name AS IndexName , STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated' , s.rowcnt AS 'Row Count' , s.rowmodctr AS 'Number Of Changes' , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS '% Rows Changed' FROM sys.sysindexes s INNER JOIN sys.tables st ON st.[object_id] = s.[id] INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] WHERE s.id > 100 AND s.indid > 0 AND s.rowcnt >= 500 ORDER BY SchemaName, TableName, IndexName
QUERY DMVs
11. How to find a cached plan
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 st.text AS [SQL] , cp.cacheobjtype , cp.objtype , COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT))+'*', 'Resource') AS [DatabaseName] , cp.usecounts AS [Plan usage] , qp.query_plan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa WHERE pa.attribute = 'dbid' AND st.text LIKE '%PartyType%'
12. Finding where a query is used
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) LIKE '%insert into dbo.deal%'
13. The queries that take the longest time to run
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)] , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU] , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] , qs.execution_count , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)] , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0 ORDER BY qs.total_elapsed_time DESC
14. The queries spend the longest time being blocked
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)] , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU] , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] , qs.execution_count , CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)] , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0 ORDER BY [Total time blocked (s)] DESC
15. The queries that use the most CPU
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)] , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU] , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] , qs.execution_count , CAST((qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)] , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0 ORDER BY [Total CPU time (s)] DESC
16. The queries that use the most I/O
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 [Total IO] = (qs.total_logical_reads + qs.total_logical_writes) , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count , qs.execution_count , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY [Total IO] DESC
17. The queries that have been executed the most often
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 qs.execution_count , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.execution_count DESC;
18. Finding when a query was last run
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DISTINCT TOP 20 qs.last_execution_time , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt WHERE qt.text LIKE '%CREATE PROCEDURE%List%PickList%' ORDER BY qs.last_execution_time DESC
19. Finding when a table was last inserted
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 qs.last_execution_time , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) LIKE '%INSERT INTO dbo.Underlying%' ORDER BY qs.last_execution_time DESC The listing is very similar to the other scripts
20. Finding queries with missing statistics
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 st.text AS [Parent Query] , DB_NAME(st.dbid)AS [DatabaseName] , cp.usecounts AS [Usage Count] , qp.query_plan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%<ColumnsWithNoStatistics>%' ORDER BY cp.usecounts DESC
21. Finding your default statistics options
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT name AS DatabaseName , is_auto_create_stats_on AS AutoCreateStatistics , is_auto_update_stats_on AS AutoUpdateStatistics , is_auto_update_stats_async_on AS AutoUpdateStatisticsAsync FROM sys.databases ORDER BY DatabaseName
22. Finding disparate columns with different data types
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT COLUMN_NAME ,[%] = CONVERT(DECIMAL(12,2),COUNT(COLUMN_NAME)* 100.0 / COUNT(*)OVER()) INTO #Prevalence FROM INFORMATION_SCHEMA.COLUMNS GROUP BY COLUMN_NAME SELECT DISTINCT C1.COLUMN_NAME , C1.TABLE_SCHEMA , C1.TABLE_NAME , C1.DATA_TYPE , C1.CHARACTER_MAXIMUM_LENGTH , C1.NUMERIC_PRECISION , C1.NUMERIC_SCALE , [%] FROM INFORMATION_SCHEMA.COLUMNS C1 INNER JOIN INFORMATION_SCHEMA.COLUMNS C2 ON C1.COLUMN_NAME = C2.COLUMN_NAME INNER JOIN #Prevalence p ON p.COLUMN_NAME = C1.COLUMN_NAME WHERE ((C1.DATA_TYPE != C2.DATA_TYPE) OR (C1.CHARACTER_MAXIMUM_LENGTH != C2.CHARACTER_MAXIMUM_LENGTH) OR (C1.NUMERIC_PRECISION != C2.NUMERIC_PRECISION) OR (C1.NUMERIC_SCALE != C2.NUMERIC_SCALE)) ORDER BY [%] DESC, C1.COLUMN_NAME, C1.TABLE_SCHEMA, C1.TABLE_NAME DROP TABLE #Prevalence
23. Finding queries that are running slower than normal
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 100 qs.execution_count AS [Runs] , (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1) AS [Avg time] , qs.last_worker_time AS [Last time] , (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) AS [Time Deviation] , CASE WHEN qs.last_worker_time = 0 THEN 100 ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100 END / (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1.0))) AS [% Time Deviation] ,qs.last_logical_reads + qs.last_logical_writes + qs.last_physical_reads AS [Last IO] , ((qs.total_logical_reads + qs.total_logical_writes + qs.total_physical_reads) - (qs.last_logical_reads + last_logical_writes + qs.last_physical_reads)) / (qs.execution_count - 1) AS [Avg IO] , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS [DatabaseName] INTO #SlowQueries FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt WHERE qs.execution_count > 1 AND qs.total_worker_time != qs.last_worker_time ORDER BY [% Time Deviation] DESC SELECT TOP 100 [Runs] , [Avg time] , [Last time] , [Time Deviation] , [% Time Deviation] , [Last IO] , [Avg IO] , [Last IO] - [Avg IO] AS [IO Deviation] , CASE WHEN [Avg IO] = 0 THEN 0 ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO] END AS [% IO Deviation] , [Individual Query] , [Parent Query] , [DatabaseName] INTO #SlowQueriesByIO FROM #SlowQueries ORDER BY [% Time Deviation] DESC SELECT TOP 100 [Runs] , [Avg time] , [Last time] , [Time Deviation] , [% Time Deviation] , [Last IO] , [Avg IO] , [IO Deviation] , [% IO Deviation] , [Impedance] = [% Time Deviation] - [% IO Deviation] , [Individual Query] , [Parent Query] , [DatabaseName] FROM #SlowQueriesByIO WHERE [% Time Deviation] - [% IO Deviation] > 20 ORDER BY [Impedance] DESC DROP TABLE #SlowQueries DROP TABLE #SlowQueriesByIO
24. Finding unused stored procedures
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT s.name, s.type_desc FROM sys.procedures s LEFT OUTER JOIN sys.dm_exec_procedure_stats d ON s.object_id = d.object_id WHERE d.object_id IS NULL ORDER BY s.name
25. Which queries run over a given time period
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT sql_handle, plan_handle, total_elapsed_time , total_worker_time, total_logical_reads, total_logical_writes , total_clr_time, execution_count, statement_start_offset , statement_end_offset INTO #PreWorkSnapShot FROM sys.dm_exec_query_stats WAITFOR DELAY '00:05:00' SELECT sql_handle, plan_handle, total_elapsed_time , total_worker_time, total_logical_reads, total_logical_writes , total_clr_time, execution_count, statement_start_offset , statement_end_offset INTO #PostWorkSnapShot FROM sys.dm_exec_query_stats SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] , p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] , (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) - (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked] , p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] , p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] , p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] , p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] , SUBSTRING (qt.text,p2.statement_start_offset/2 + 1, ((CASE WHEN p2.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM #PreWorkSnapShot p1 RIGHT OUTER JOIN #PostWorkSnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(p2.plan_handle) qp WHERE p2.execution_count != ISNULL(p1.execution_count, 0) AND qt.text NOT LIKE '--ThisRoutineIdentifier99%' ORDER BY [Duration] DESC DROP TABLE #PreWorkSnapShot DROP TABLE #PostWorkSnapShot
26. Amalgamated DMV snapshots
--ThisRoutineIdentifier SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT index_group_handle, index_handle , avg_total_user_cost, avg_user_impact, user_seeks, user_scans INTO #PreWorkMissingIndexes 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 SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PreWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT wait_type, waiting_tasks_count , wait_time_ms, max_wait_time_ms, signal_wait_time_ms INTO #PreWorkWaitStats FROM sys.dm_os_wait_stats WAITFOR DELAY '00:05:00' SELECT wait_type, waiting_tasks_count, wait_time_ms , max_wait_time_ms, signal_wait_time_ms INTO #PostWorkWaitStats FROM sys.dm_os_wait_stats SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PostWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PostWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT index_group_handle, index_handle , avg_total_user_cost, avg_user_impact, user_seeks, user_scans INTO #PostWorkMissingIndexes 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 SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] , p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] , (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) - (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked] , p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] , p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] , p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] , p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] , SUBSTRING (qt.text,p2.statement_start_offset/2 + 1, ((CASE WHEN p2.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM #PreWorkQuerySnapShot p1 RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt WHERE p2.execution_count != ISNULL(p1.execution_count, 0) AND qt.text NOT LIKE '--ThisRoutineIdentifier%' SELECT p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms , p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms , ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) - (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait , p2.wait_type FROM #PreWorkWaitStats p1 RIGHT OUTER JOIN #PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type) WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0 AND p2.wait_type NOT LIKE '%SLEEP%' AND p2.wait_type != 'WAITFOR' ORDER BY RealWait DESC SELECT ROUND((p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0)) * (p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0)) * ((p2.user_seeks - ISNULL(p1.user_seeks, 0)) + (p2.user_scans - ISNULL(p1.user_scans, 0))),0) AS [Total Cost] , p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) AS avg_total_user_cost , p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) AS avg_user_impact , p2.user_seeks - ISNULL(p1.user_seeks, 0) AS user_seeks , p2.user_scans - ISNULL(p1.user_scans, 0) AS user_scans , d.statement AS TableName , d.equality_columns , d.inequality_columns , d.included_columns FROM #PreWorkMissingIndexes p1 RIGHT OUTER JOIN #PostWorkMissingIndexes p2 ON p2.index_group_handle = ISNULL(p1.index_group_handle, p2.index_group_handle) AND p2.index_handle = ISNULL(p1.index_handle, p2.index_handle) INNER JOIN sys.dm_db_missing_index_details d ON p2.index_handle = d.index_handle WHERE p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) > 0 OR p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) > 0 OR p2.user_seeks - ISNULL(p1.user_seeks, 0) > 0 OR p2.user_scans - ISNULL(p1.user_scans, 0) > 0 ORDER BY [Total Cost] DESC SELECT p2.object_name, p2.counter_name, p2.instance_name , ISNULL(p1.cntr_value, 0) AS InitialValue , p2.cntr_value AS FinalValue , p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change , (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change] FROM #PreWorkOSSnapShot p1 RIGHT OUTER JOIN #PostWorkOSSnapShot p2 ON p2.object_name = ISNULL(p1.object_name, p2.object_name) AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name) AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name) WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0 AND ISNULL(p1.cntr_value, 0) != 0 ORDER BY [% Change] DESC, Change DESC DROP TABLE #PreWorkQuerySnapShot DROP TABLE #PostWorkQuerySnapShot DROP TABLE #PostWorkWaitStats DROP TABLE #PreWorkWaitStats DROP TABLE #PreWorkOSSnapShot DROP TABLE #PostWorkOSSnapShot DROP TABLE #PreWorkMissingIndexes DROP TABLE #PostWorkMissingIndexes
27. What queries are running now
SELECT es.session_id, es.host_name, es.login_name , er.status, DB_NAME(database_id) AS DatabaseName , SUBSTRING (qt.text,(er.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , es.program_name, er.start_time, qp.query_plan , er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads , er.blocking_session_id, er.open_transaction_count, er.last_wait_type , er.percent_complete FROM sys.dm_exec_requests AS er INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp WHERE es.is_user_process=1 AND es.session_Id NOT IN (@@SPID) ORDER BY es.session_id
28. Determining your most-recompiled queries
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 qs.plan_generation_num , qs.total_elapsed_time , qs.execution_count , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((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) + 1) AS [Individual Query] , qt.text AS [Parent Query], DB_NAME(qt.dbid) AS DatabaseName , qs.creation_time , qs.last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY plan_generation_num DESC
Operating system DMVs
29. SQL script shown here will identify the top 20 causes of waiting on your server instance
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 wait_type, wait_time_ms, signal_wait_time_ms , wait_time_ms - signal_wait_time_ms AS RealWait , CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER()) AS [% Waiting] , CONVERT(DECIMAL(12,2), (wait_time_ms - signal_wait_time_ms) * 100.0 / SUM(wait_time_ms) OVER()) AS [% RealWait] FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE '%SLEEP%' AND wait_type != 'WAITFOR' ORDER BYwait_time_ms DESC
30. Script will identify the waits that occur over a given 10-minute period, ordered by RealWait
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT wait_type, waiting_tasks_count , wait_time_ms, max_wait_time_ms, signal_wait_time_ms INTO #PreWorkWaitStats FROM sys.dm_os_wait_stats WAITFOR DELAY '00:10:00' SELECT wait_type, waiting_tasks_count , wait_time_ms, max_wait_time_ms, signal_wait_time_ms INTO #PostWorkWaitStats FROM sys.dm_os_wait_stats SELECT p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms , p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms , ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) - (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait , p2.wait_type FROM #PreWorkWaitStats p1 RIGHT OUTER JOIN #PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type) WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0 AND p2.wait_type NOT LIKE '%SLEEP%' AND p2.wait_type != 'WAITFOR' ORDER BY RealWait DESC DROP TABLE #PostWorkWaitStats DROP TABLE #PreWorkWaitStats
31. Why your queries are waiting
--ThisRoutineIdentifier SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT wait_type, waiting_tasks_count , wait_time_ms, max_wait_time_ms, signal_wait_time_ms INTO #PreWorkWaitStats FROM sys.dm_os_wait_stats WAITFOR DELAY '00:05:00' SELECT wait_type, waiting_tasks_count, wait_time_ms , max_wait_time_ms, signal_wait_time_ms INTO #PostWorkWaitStats FROM sys.dm_os_wait_stats SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PostWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms , p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms , ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) – (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait , p2.wait_type FROM #PreWorkWaitStats p1 RIGHT OUTER JOIN #PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type) WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0 AND p2.wait_type NOT LIKE '%SLEEP%' AND p2.wait_type != 'WAITFOR' ORDER BY RealWait DESC SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] , p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] , (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) - (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked] , p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] , p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] , p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] , p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] , SUBSTRING (qt.text,p2.statement_start_offset/2 + 1, ((CASE WHEN p2.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM #PreWorkQuerySnapShot p1 RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt WHERE p2.execution_count != ISNULL(p1.execution_count, 0) AND qt.text NOT LIKE '--ThisRoutineIdentifier%' ORDER BY [Time blocked] DESC DROP TABLE #PostWorkWaitStats DROP TABLE #PreWorkWaitStats DROP TABLE #PostWorkQuerySnapShot DROP TABLE #PreWorkQuerySnapShot
32. What’s blocking my SQL query?
SELECT Blocking.session_id as BlockingSessionId , Sess.login_name AS BlockingUser , BlockingSQL.text AS BlockingSQL , Waits.wait_type WhyBlocked , Blocked.session_id AS BlockedSessionId , USER_NAME(Blocked.user_id) AS BlockedUser , BlockedSQL.text AS BlockedSQL , DB_NAME(Blocked.database_id) AS DatabaseName FROM sys.dm_exec_connections AS Blocking INNER JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id RIGHT OUTER JOIN sys.dm_exec_sessions Sess ON Blocking.session_id = sess.session_id CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL ORDER BY BlockingSessionId, BlockedSessionId
33. Script here will show how the performance counters change over the given time interval.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PreWorkOSSnapShot FROM sys.dm_os_performance_counters WAITFOR DELAY '00:05:00' SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PostWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT p2.object_name, p2.counter_name, p2.instance_name , ISNULL(p1.cntr_value, 0) AS InitialValue , p2.cntr_value AS FinalValue , p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change , (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change] FROM #PreWorkOSSnapShot p1 RIGHT OUTER JOIN #PostWorkOSSnapShot p2 ON p2.object_name = ISNULL(p1.object_name, p2.object_name) AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name) AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name) WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0 AND ISNULL(p1.cntr_value, 0) != 0 ORDER BY [% Change] DESC, Change DESC DROP TABLE #PreWorkOSSnapShot DROP TABLE #PostWorkOSSnapShot
34. Changes in performance counters and wait states
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PreWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT wait_type, waiting_tasks_count , wait_time_ms, max_wait_time_ms, signal_wait_time_ms INTO #PreWorkWaitStats FROM sys.dm_os_wait_stats WAITFOR DELAY '00:05:00' SELECT wait_type, waiting_tasks_count, wait_time_ms , max_wait_time_ms, signal_wait_time_ms INTO #PostWorkWaitStats FROM sys.dm_os_wait_stats SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PostWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms , p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms , ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) – (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait , p2.wait_type FROM #PreWorkWaitStats p1 RIGHT OUTER JOIN #PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type) WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0 AND p2.wait_type NOT LIKE '%SLEEP%' AND p2.wait_type != 'WAITFOR' ORDER BY RealWait DESC SELECT p2.object_name, p2.counter_name, p2.instance_name , ISNULL(p1.cntr_value, 0) AS InitialValue , p2.cntr_value AS FinalValue , p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change , (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change] FROM #PreWorkOSSnapShot p1 RIGHT OUTER JOIN #PostWorkOSSnapShot p2 ON p2.object_name =ISNULL(p1.object_name, p2.object_name) AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name) AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name) WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0 AND ISNULL(p1.cntr_value, 0) != 0 ORDER BY [% Change] DESC, Change DESC DROP TABLE #PostWorkWaitStats DROP TABLE #PreWorkWaitStats DROP TABLE #PreWorkOSSnapShot DROP TABLE #PostWorkOSSnapShot
35. Changes in performance counters and wait states
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT sql_handle ,plan_handle ,total_elapsed_time ,total_worker_time ,total_logical_reads ,total_logical_writes ,total_clr_time ,execution_count ,statement_start_offset ,statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT [object_name] ,[counter_name] ,[instance_name] ,[cntr_value] ,[cntr_type] INTO #PreWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT wait_type ,waiting_tasks_count ,wait_time_ms ,max_wait_time_ms ,signal_wait_time_ms INTO #PreWorkWaitStats FROM sys.dm_os_wait_stats WAITFOR DELAY '00:05:00' SELECT wait_type ,waiting_tasks_count ,wait_time_ms ,max_wait_time_ms ,signal_wait_time_ms INTO #PostWorkWaitStats FROM sys.dm_os_wait_stats SELECT [object_name] ,[counter_name] ,[instance_name] ,[cntr_value] ,[cntr_type] INTO #PostWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT sql_handle ,plan_handle ,total_elapsed_time ,total_worker_time ,total_logical_reads ,total_logical_writes ,total_clr_time ,execution_count ,statement_start_offset ,statement_end_offset INTO #PostWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] ,p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] ,(p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) - (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked] ,p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] ,p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] ,p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] ,p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] ,SUBSTRING(qt.TEXT, p2.statement_start_offset / 2 + 1, ( ( CASE WHEN p2.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset ) / 2 ) + 1) AS [Individual Query] ,qt.TEXT AS [Parent Query] ,DB_NAME(qt.dbid) AS DatabaseName FROM #PreWorkQuerySnapShot p1 RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) AS qt WHERE p2.execution_count != ISNULL(p1.execution_count, 0) AND qt.TEXT NOT LIKE '--ThisRoutineIdentifier%' ORDER BY [Duration] DESC SELECT p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms ,p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms ,((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))) AS wait_time_ms ,(p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)) AS RealWait ,p2.wait_type FROM #PreWorkWaitStats p1 RIGHT OUTER JOIN #PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type) WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0 AND p2.wait_type NOT LIKE '%SLEEP%' AND p2.wait_type != 'WAITFOR' ORDER BY RealWait DESC SELECT p2.object_name ,p2.counter_name ,p2.instance_name ,ISNULL(p1.cntr_value, 0) AS InitialValue ,p2.cntr_value AS FinalValue ,p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change ,(p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change] FROM #PreWorkOSSnapShot p1 RIGHT OUTER JOIN #PostWorkOSSnapShot p2 ON p2.object_name = ISNULL(p1.object_name, p2.object_name) AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name) AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name) WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0 AND ISNULL(p1.cntr_value, 0) != 0 ORDER BY [% Change] DESC ,Change DESC DROP TABLE #PreWorkQuerySnapShot DROP TABLE #PostWorkQuerySnapShot DROP TABLE #PostWorkWaitStats DROP TABLE #PreWorkWaitStats DROP TABLE #PreWorkOSSnapShot DROP TABLE #PostWorkOSSnapShot
Resolving transaction issues
36. Observing the current locks
SELECT DB_NAME(resource_database_id) AS DatabaseName, request_session_id , resource_type, request_status, request_mode FROM sys.dm_tran_locks WHERE request_session_id !=@@spid ORDER BY request_session_id
37. Information contained in sessions, connections, and requests
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id LEFT OUTER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id WHERE s.session_id > 50
38. How to discover which locks are currently held
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(resource_database_id) AS DatabaseName , request_session_id , resource_type , CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id) WHEN resource_type IN ('KEY', 'PAGE', 'RID') THEN (SELECT OBJECT_NAME(OBJECT_ID) FROM sys.partitions p WHERE p.hobt_id = l.resource_associated_entity_id) END AS resource_type_name , request_status , request_mode FROM sys.dm_tran_locks l WHERE request_session_id !=@@spid ORDER BY request_session_id
39. How to identify contended resources
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT tl1.resource_type, DB_NAME(tl1.resource_database_id) AS DatabaseName, tl1.resource_associated_entity_id, tl1.request_session_id, tl1.request_mode, tl1.request_status , CASE WHEN tl1.resource_type = 'OBJECT' THEN OBJECT_NAME(tl1.resource_associated_entity_id) WHEN tl1.resource_type IN ('KEY', 'PAGE', 'RID') THEN (SELECT OBJECT_NAME(OBJECT_ID) FROM sys.partitions s WHERE s.hobt_id = tl1.resource_associated_entity_id) END AS resource_type_name FROM sys.dm_tran_locks as tl1 INNER JOIN sys.dm_tran_locks as tl2 ON tl1.resource_associated_entity_id = tl2.resource_associated_entity_id AND tl1.request_status <> tl2.request_status AND (tl1.resource_description = tl2.resource_description OR (tl1.resource_description IS NULL AND tl2.resource_description IS NULL)) ORDER BY tl1.resource_associated_entity_id, tl1.request_status
40. How to identify contended resources, including SQL query details
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT tl1.resource_type , DB_NAME(tl1.resource_database_id) AS DatabaseName , tl1.resource_associated_entity_id , tl1.request_session_id , tl1.request_mode , tl1.request_status , CASE WHEN tl1.resource_type = 'OBJECT' THEN OBJECT_NAME(tl1.resource_associated_entity_id) WHEN tl1.resource_type IN ('KEY', 'PAGE', 'RID') THEN (SELECT OBJECT_NAME(OBJECT_ID) FROM sys.partitions s WHERE s.hobt_id = tl1.resource_associated_entity_id) END AS resource_type_name , t.text AS [Parent Query] , SUBSTRING (t.text,(r.statement_start_offset/2) + 1, ((CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS [Individual Query] FROM sys.dm_tran_locks as tl1 INNER JOIN sys.dm_tran_locks as tl2 ON tl1.resource_associated_entity_id = tl2.resource_associated_entity_id AND tl1.request_status <> tl2.request_status AND (tl1.resource_description = tl2.resource_description OR (tl1.resource_description IS NULL AND tl2.resource_description IS NULL)) INNER JOIN sys.dm_exec_connections c ON tl1.request_session_id = c.most_recent_session_id CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t LEFT OUTER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id ORDER BY tl1.resource_associated_entity_id, tl1.request_status
41. How to find an idle session with an open transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id, es.login_name, es.host_name, est.text , cn.last_read, cn.last_write, es.program_name FROM sys.dm_exec_sessions es INNER JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id INNER JOIN sys.dm_exec_connections cn ON es.session_id = cn.session_id CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est LEFT OUTER JOIN sys.dm_exec_requests er ON st.session_id = er.session_id AND er.session_id IS NULL
42. Amount of space (total, used, and free) in tempdb
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT SUM(user_object_reserved_page_count + internal_object_reserved_page_count + version_store_reserved_page_count + mixed_extent_page_count + unallocated_extent_page_count) * (8.0/1024.0) AS [TotalSizeOfTempDB(MB)] , SUM(user_object_reserved_page_count + internal_object_reserved_page_count + version_store_reserved_page_count + mixed_extent_page_count) * (8.0/1024.0) AS [UsedSpace (MB)] , SUM(unallocated_extent_page_count * (8.0/1024.0)) AS [FreeSpace (MB)] FROM sys.dm_db_file_space_usage
43. Total amount of space (data, log, and log used) by database
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT instance_name , counter_name , cntr_value / 1024.0 AS [Size(MB)] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Databases' AND counter_name IN ( 'Data File(s) Size (KB)' , 'Log File(s) Size (KB)' , 'Log File(s) Used Size (KB)') ORDER BY instance_name, counter_name
44. Tempdb total space usage by object type
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT SUM (user_object_reserved_page_count) * (8.0/1024.0) AS [User Objects (MB)], SUM (internal_object_reserved_page_count) * (8.0/1024.0) AS [Internal Objects (MB)], SUM (version_store_reserved_page_count) * (8.0/1024.0) AS [Version Store (MB)], SUM (mixed_extent_page_count)* (8.0/1024.0) AS [Mixed Extent (MB)], SUM (unallocated_extent_page_count)* (8.0/1024.0) AS [Unallocated (MB)] FROM sys.dm_db_file_space_usage
45. tempdb Space usage by session
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id , ec.connection_id , es.login_name , es.host_name , st.text , su.user_objects_alloc_page_count , su.user_objects_dealloc_page_count , su.internal_objects_alloc_page_count , su.internal_objects_dealloc_page_count , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st WHERE su.session_id > 50
46. Space used and reclaimed in tempdb for completed batches
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT CAST(SUM(su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) * (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceUsed(MB)] , CAST(SUM(su.user_objects_alloc_page_count – su.user_objects_dealloc_page_count + su.internal_objects_alloc_page_count – su.internal_objects_dealloc_page_count) * (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceStillUsed(MB)] , su.session_id , ec.connection_id , es.host_name , st.text AS [LastQuery] , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st WHERE su.session_id > 50 GROUP BY su.session_id, ec.connection_id, es.login_name, es.host_name , st.text, ec.last_read, ec.last_write, es.program_name ORDER BY [SpaceStillUsed(MB)] DESC
47. Space used by running SQL queries
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id , ec.connection_id , es.login_name , es.host_name , st.text , tu.user_objects_alloc_page_count , tu.user_objects_dealloc_page_count , tu.internal_objects_alloc_page_count , tu.internal_objects_dealloc_page_count , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_task_space_usage tu INNER JOIN sys.dm_exec_sessions es ON tu.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON tu.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st WHERE tu.session_id > 50
48. Space used and not reclaimed by active SQL queries
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT SUM(ts.user_objects_alloc_page_count + ts.internal_objects_alloc_page_count) * (8.0/1024.0) AS [SpaceUsed(MB)] , SUM(ts.user_objects_alloc_page_count – ts.user_objects_dealloc_page_count + ts.internal_objects_alloc_page_count – ts.internal_objects_dealloc_page_count) * (8.0/1024.0) AS [SpaceStillUsed(MB)] , ts.session_id , ec.connection_id , es.login_name , es.host_name , st.text AS [Parent Query] , SUBSTRING (st.text,(er.statement_start_offset/2) + 1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS [Current Query] , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_task_space_usage ts INNER JOIN sys.dm_exec_sessions es ON ts.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON ts.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st LEFT OUTER JOIN sys.dm_exec_requests er ON ts.session_id = er.session_id WHERE ts.session_id > 50 GROUP BY ts.session_id, ec.connection_id, es.login_name, es.host_name , st.text, ec.last_read, ec.last_write, es.program_name , SUBSTRING (st.text,(er.statement_start_offset/2) + 1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) ORDER BY [SpaceStillUsed(MB)] DESC
49. Indexes under row-locking pressure
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 x.name AS SchemaName , OBJECT_NAME(s.object_id) AS TableName , i.name AS IndexName , s.row_lock_wait_in_ms , s.row_lock_wait_count FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE s.row_lock_wait_in_ms > 0 AND o.is_ms_shipped = 0 ORDER BY s.row_lock_wait_in_ms DESC
50. Indexes with the most lock escalations
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 x.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.index_lock_promotion_count FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE s.index_lock_promotion_count > 0 AND o.is_ms_shipped = 0 ORDER BY s.index_lock_promotion_count DESC
51. Indexes with the most unsuccessful lock escalations
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 x.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.index_lock_promotion_attempt_count – s.index_lock_promotion_count AS UnsuccessfulIndexLockPromotions FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE (s.index_lock_promotion_attempt_count - index_lock_promotion_count)>0 AND o.is_ms_shipped = 0 ORDER BY UnsuccessfulIndexLockPromotions DESC
52. Indexes with the most page splits
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 x.name AS SchemaName , object_name(s.object_id) AS TableName , i.name AS IndexName , s.leaf_allocation_count , s.nonleaf_allocation_count FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE s.leaf_allocation_count > 0 AND o.is_ms_shipped = 0 ORDER BY s.leaf_allocation_count DESC
53. Indexes with the most latch contention
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 x.name AS SchemaName , OBJECT_NAME(s.object_id) AS TableName , i.name AS IndexName , s.page_latch_wait_in_ms , s.page_latch_wait_count FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE s.page_latch_wait_in_ms > 0 AND o.is_ms_shipped = 0 ORDER BY s.page_latch_wait_in_ms DESC
54. Indexes with the most page I/O-latch contention
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT x.NAME AS SchemaName ,OBJECT_NAME(s.object_id) AS TableName ,i.NAME AS IndexName ,s.row_lock_wait_in_ms INTO #PreWorkIndexCount FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE s.row_lock_wait_in_ms > 0 AND o.is_ms_shipped = 0 SELECT sql_handle ,plan_handle ,total_elapsed_time ,total_worker_time ,total_logical_reads ,total_logical_writes ,total_clr_time ,execution_count ,statement_start_offset ,statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats WAITFOR DELAY '01:00:00' SELECT x.NAME AS SchemaName ,OBJECT_NAME(s.object_id) AS TableName ,i.NAME AS IndexName ,s.row_lock_wait_in_ms INTO #PostWorkIndexCount FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE s.row_lock_wait_in_ms > 0 AND o.is_ms_shipped = 0 SELECT sql_handle ,plan_handle ,total_elapsed_time ,total_worker_time ,total_logical_reads ,total_logical_writes ,total_clr_time ,execution_count ,statement_start_offset ,statement_end_offset INTO #PostWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT p2.SchemaName ,p2.TableName ,p2.IndexName ,p2.row_lock_wait_in_ms - ISNULL(p1.row_lock_wait_in_ms, 0) AS RowLockWaitTimeDelta_ms FROM #PreWorkIndexCount p1 RIGHT OUTER JOIN #PostWorkIndexCount p2 ON p2.SchemaName = ISNULL(p1.SchemaName, p2.SchemaName) AND p2.TableName = ISNULL(p1.TableName, p2.TableName) AND p2.IndexName = ISNULL(p1.IndexName, p2.IndexName) WHERE p2.row_lock_wait_in_ms - ISNULL(p1.row_lock_wait_in_ms, 0) > 0 ORDER BY RowLockWaitTimeDelta_ms DESC SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] ,p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] ,(p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) ,(p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked] ,p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] ,p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] ,p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] ,p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] ,SUBSTRING(qt.TEXT, p2.statement_start_offset / 2 + 1, ( ( CASE WHEN p2.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset ) / 2 ) + 1) AS [Individual Query] ,qt.TEXT AS [Parent Query] ,DB_NAME(qt.dbid) AS DatabaseName FROM #PreWorkQuerySnapShot p1 RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) AS qt WHERE p2.execution_count != ISNULL(p1.execution_count, 0) AND qt.TEXT NOT LIKE '--ThisRoutineIdentifier%' ORDER BY [Duration] DESC DROP TABLE #PreWorkIndexCount DROP TABLE #PostWorkIndexCount DROP TABLE #PreWorkQuerySnapShot DROP TABLE #PostWorkQuerySnapShot
55. Determining how many rows are inserted/deleted/updated/selected
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT x.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.leaf_delete_count , s.leaf_ghost_count , s.leaf_insert_count , s.leaf_update_count , s.range_scan_count , s.singleton_lookup_count INTO #PreWorkIndexCount FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE o.is_ms_shipped = 0 WAITFOR DELAY '01:00:00' SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PostWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT x.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.leaf_delete_count , s.leaf_ghost_count , s.leaf_insert_count , s.leaf_update_count , s.range_scan_count , s.singleton_lookup_count INTO #PostWorkIndexCount FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.indexes i ON s.index_id = i.index_id AND i.object_id = o.object_id INNER JOIN sys.schemas x ON x.schema_id = o.schema_id WHERE o.is_ms_shipped = 0 SELECT p2.SchemaName , p2.TableName , p2.IndexName , p2.leaf_delete_count - ISNULL(p1.leaf_delete_count, 0) AS leaf_delete_countDelta , p2.leaf_ghost_count - ISNULL(p1.leaf_ghost_count, 0) AS leaf_ghost_countDelta , p2.leaf_insert_count - ISNULL(p1.leaf_insert_count, 0) AS leaf_insert_countDelta , p2.leaf_update_count - ISNULL(p1.leaf_update_count, 0) AS leaf_update_countDelta , p2.range_scan_count - ISNULL(p1.range_scan_count, 0) AS range_scan_countDelta , p2.singleton_lookup_count - ISNULL(p1.singleton_lookup_count, 0) AS singleton_lookup_countDelta FROM #PreWorkIndexCount p1 RIGHT OUTER JOIN #PostWorkIndexCount p2 ON p2.SchemaName = ISNULL(p1.SchemaName, p2.SchemaName) AND p2.TableName = ISNULL(p1.TableName, p2.TableName) AND p2.IndexName = ISNULL(p1.IndexName, p2.IndexName) WHERE p2.leaf_delete_count - ISNULL(p1.leaf_delete_count, 0) > 0 OR p2.leaf_ghost_count - ISNULL(p1.leaf_ghost_count, 0) > 0 OR p2.leaf_insert_count - ISNULL(p1.leaf_insert_count, 0) > 0 OR p2.leaf_update_count - ISNULL(p1.leaf_update_count, 0) > 0 OR p2.range_scan_count - ISNULL(p1.range_scan_count, 0) > 0 OR p2.singleton_lookup_count - ISNULL(p1.singleton_lookup_count, 0) > 0 ORDER BY leaf_delete_countDelta DESC SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] , p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] , (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) – (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked] , p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] , p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] , p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] , p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] , SUBSTRING (qt.text,p2.statement_start_offset/2 + 1, ((CASE WHEN p2.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM #PreWorkQuerySnapShot p1 RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt WHERE p2.execution_count != ISNULL(p1.execution_count, 0) ORDER BY [Duration] DESC DROP TABLE #PreWorkIndexCount DROP TABLE #PostWorkIndexCount DROP TABLE #PreWorkQuerySnapShot DROP TABLE #PostWorkQuerySnapShot
56. Rebuilding and reorganizing fragmented indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED CREATE TABLE #FragmentedIndexes( DatabaseName SYSNAME , SchemaName SYSNAME , TableName SYSNAME , IndexName SYSNAME , [Fragmentation%] FLOAT) INSERT INTO #FragmentedIndexes SELECT DB_NAME(DB_ID()) AS DatabaseName , ss.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.avg_fragmentation_in_percent AS [Fragmentation%] FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id] WHERE s.database_id = DB_ID() AND i.index_id != 0 AND s.record_count > 0 AND o.is_ms_shipped = 0 DECLARE @RebuildIndexesSQL NVARCHAR(MAX) SET @RebuildIndexesSQL = '' SELECT @RebuildIndexesSQL = @RebuildIndexesSQL + CASE WHEN [Fragmentation%] > 30 THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' REBUILD;' WHEN [Fragmentation%] > 10 THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' REORGANIZE;' END FROM #FragmentedIndexes WHERE [Fragmentation%] > 10 DECLARE @StartOffset INT DECLARE @Length INT SET @StartOffset = 0 SET @Length = 4000 WHILE (@StartOffset < LEN(@RebuildIndexesSQL)) BEGIN PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length) SET @StartOffset = @StartOffset + @Length END PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length) EXECUTE sp_executesql @RebuildIndexesSQL DROP TABLE #FragmentedIndexes
57. Rebuild/reorganize for all databases on a given server
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED CREATE TABLE #FragmentedIndexes( DatabaseName SYSNAME , SchemaName SYSNAME , TableName SYSNAME , IndexName SYSNAME , [Fragmentation%] FLOAT) EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #FragmentedIndexes SELECT DB_NAME(DB_ID()) AS DatabaseName , ss.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.avg_fragmentation_in_percent AS [Fragmentation%] FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, ''SAMPLED'') s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id] WHERE s.database_id = DB_ID() AND i.index_id != 0 AND s.record_count > 0 AND o.is_ms_shipped = 0 ;' DECLARE @RebuildIndexesSQL NVARCHAR(MAX) SET @RebuildIndexesSQL = '' SELECT @RebuildIndexesSQL = @RebuildIndexesSQL + CASE WHEN [Fragmentation%] > 30 THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' REBUILD;' WHEN [Fragmentation%] > 10 THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' REORGANIZE;' END FROM #FragmentedIndexes WHERE [Fragmentation%] > 10 DECLARE @StartOffset INT DECLARE @Length INT SET @StartOffset = 0 SET @Length = 4000 WHILE (@StartOffset < LEN(@RebuildIndexesSQL)) BEGIN PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length) SET @StartOffset = @StartOffset + @Length END PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length) EXECUTE sp_executesql @RebuildIndexesSQL DROP TABLE #FragmentedIndexes
58. Intelligently update statistics—simple version
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT ss.name AS SchemaName , st.name AS TableName , si.name AS IndexName , si.type_desc AS IndexType , STATS_DATE(si.object_id,si.index_id) AS StatsLastTaken , ssi.rowcnt , ssi.rowmodctr INTO #IndexUsage FROM sys.indexes si INNER JOIN sys.sysindexes ssi ON si.object_id = ssi.id AND si.name = ssi.name INNER JOIN sys.tables st ON st.[object_id] = si.[object_id] INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] WHERE st.is_ms_shipped = 0 AND si.index_id != 0 AND ssi.rowcnt > 100 AND ssi.rowmodctr > 0 DECLARE @UpdateStatisticsSQL NVARCHAR(MAX) SET @UpdateStatisticsSQL = '' SELECT @UpdateStatisticsSQL = @UpdateStatisticsSQL + CHAR(10) + 'UPDATE STATISTICS ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' ' + QUOTENAME(IndexName) + ' WITH SAMPLE ' + CASE WHEN rowcnt < 500000 THEN '100 PERCENT' WHEN rowcnt < 1000000 THEN '50 PERCENT' WHEN rowcnt < 5000000 THEN '25 PERCENT' WHEN rowcnt < 10000000 THEN '10 PERCENT' WHEN rowcnt < 50000000 THEN '2 PERCENT' WHEN rowcnt < 100000000 THEN '1 PERCENT' ELSE '3000000 ROWS ' END + '-- ' + CAST(rowcnt AS VARCHAR(22)) + ' rows' FROM #IndexUsage DECLARE @StartOffset INT DECLARE @Length INT SET @StartOffset = 0 SET @Length = 4000 WHILE (@StartOffset < LEN(@UpdateStatisticsSQL)) BEGIN PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length) SET @StartOffset = @StartOffset + @Length END PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length) EXECUTE sp_executesql @UpdateStatisticsSQL DROP TABLE #IndexUsage
59. Estimating when a job will finish
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT r.percent_complete , DATEDIFF(MINUTE, start_time, GETDATE()) AS Age , DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) / percent_complete * 100, start_time) AS EstimatedEndTime , t.Text AS ParentQuery , SUBSTRING (t.text,(r.statement_start_offset/2) + 1, ((CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS IndividualQuery , start_time , DB_NAME(Database_Id) AS DatabaseName , Status FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) t WHERE session_id > 50 AND percent_complete > 0 ORDER BY percent_complete DESC
60. Who’s doing what and when?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED CREATE TABLE dbo.WhatsGoingOnHistory( [Runtime] [DateTime], [session_id] [smallint] NOT NULL, [login_name] [varchar](128) NOT NULL, [host_name] [varchar](128) NULL, [DBName] [varchar](128) NULL, [Individual Query] [varchar](max) NULL, [Parent Query] [varchar](200) NULL, [status] [varchar](30) NULL, [start_time] [datetime] NULL, [wait_type] [varchar](60) NULL, [program_name] [varchar](128) NULL ) GO CREATE UNIQUE NONCLUSTERED INDEX [NONCLST_WhatsGoingOnHistory] ON [dbo].[WhatsGoingOnHistory] ([Runtime] ASC, [session_id] ASC) GO INSERT INTO dbo.WhatsGoingOnHistory SELECT GETDATE() , s.session_id , s.login_name , s.host_name , DB_NAME(r.database_id) AS DBName , SUBSTRING (t.text,(r.statement_start_offset/2) + 1, ((CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS [Individual Query] , SUBSTRING(text, 1, 200) AS [Parent Query] , r.status , r.start_time , r.wait_type , s.program_name FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id INNER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE s.session_id > 50 AND r.session_id != @@spid WAITFOR DELAY '00:01:00' GO 1440 -- 60 * 24 (one day)
61. Determining where your query spends its time
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats EXEC MO.PNLYearToDate_v01iws @pControlOrgIds = '537' , @pCOBStart = '27 may 2009' , @pCOBEnd = '27 may 2009' SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset, last_execution_time INTO #PostWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration] , p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU] , (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) - (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time waiting] , p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads] , p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes] , p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time] , p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions] , p2.last_execution_time , SUBSTRING (qt.text,p2.statement_start_offset/2 + 1, ((CASE WHEN p2.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE p2.statement_end_offset END - p2.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM #PreWorkQuerySnapShot p1 RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle) AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle) AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset) CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt WHERE p2.execution_count != ISNULL(p1.execution_count, 0) AND qt.text LIKE '%PNLYearToDate_v01iws %' ORDER BY [Parent Query], p2.statement_start_offset DROP TABLE #PreWorkQuerySnapShot DROP TABLE #PostWorkQuerySnapShot
62. Memory used per database
SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2)) AS [Size (MB)] FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY DatabaseName
63. Memory used by objects in the current database
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT OBJECT_NAME(p.[object_id]) AS [TableName] , (COUNT(*) * 8) / 1024 AS [Buffer size(MB)] , ISNULL(i.name, '-- HEAP --') AS ObjectName , COUNT(*) AS NumberOf8KPages 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 INNER JOIN sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id] ON a.container_id = p.hobt_id WHERE b.database_id = DB_ID() AND p.[object_id] > 100 GROUP BY p.[object_id], i.name ORDER BY NumberOf8KPages DESC
64. I/O stalls at the database level
SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(database_id) AS [DatabaseName] , SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)] , SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [IO read (MB)] , SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [IO written (MB)] , SUM(CAST((num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)] FROM sys.dm_io_virtual_file_stats(NULL, NULL) GROUP BY database_id ORDER BY [IO stall (secs)] DESC
65. I/O waits at the file level
SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(database_id) AS [DatabaseName] , file_id , SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)] , SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [IO read (MB)] , SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [IO written (MB)], SUM(CAST((num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)] FROM sys.dm_io_virtual_file_stats(NULL, NULL) GROUP BY database_id, file_id ORDER BY [IO stall (secs)] DESC
66. Average read/write times per file, per database
SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(database_id) AS DatabaseName , file_id , io_stall_read_ms / num_of_reads AS 'Average read time' , io_stall_write_ms / num_of_writes AS 'Average write time' FROM sys.dm_io_virtual_file_stats(NULL, NULL) WHERE num_of_reads > 0 and num_of_writes > 0 ORDER BY DatabaseName
New layer…
Leave a Reply
You must be logged in to post a comment.