Skip to content

Instantly share code, notes, and snippets.

@huguogang

huguogang/SQL

Created Mar 22, 2012
Embed
What would you like to do?
SQL Server Performance DMV Sample Queries
-----------------------------------------------------------------------
-- Collection of queries that helps in performance tuning
--grant permission to user
GRANT VIEW SERVER STATE TO [user_name]
------------ based on http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
---------- Uncover hidden data to optimize application performance -----------------------
-- * Number of missing indexes
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;
-- * top 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
, s.unique_compiles
,s.last_user_seek
,s.user_seeks
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;
-- * top costly unused indexes (does not seem to work)
-- 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
-- * most costly used 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
-- * most 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
-- * Find top server waits (CLR_AUTO_EVENT, CHECKPOINT_QUEUE, PAGEIOLATCH_SH, ...)
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;
-- * Find top queries by average time blocked
SELECT TOP 100
[Average Time Blocked (s)] = (total_elapsed_time - total_worker_time) / (qs.execution_count * 1000000)
,[Total Time Blocked (s)] = total_elapsed_time - total_worker_time / 1000000
,[Execution count] = qs.execution_count
-- quote text into CSV friendly format
,[Individual Query] = '"' + 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)
+ '"'
-- quote text into CSV friendly format
,[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 (s)] DESC;
-- * Find most executed queries by number of executions (repetitions)
SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = 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)
,[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 [Execution count] DESC;
-- * Find top queries by average CPU
SELECT TOP 100
[Average CPU (s)] = total_worker_time / (qs.execution_count * 1000000) --Unit: sec
,[Total CPU (s)] = total_worker_time / 1000000
,[Execution count] = qs.execution_count
,qs.last_execution_time
-- quote text into CSV friendly format
,[Individual Query] = '"' + 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) + '"'
-- quote text into CSV friendly format
,[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 last_execution_time > '2012-03-10 7:00AM'
ORDER BY [Average CPU (s)] DESC;
-- * Find top queries by total CPU
SELECT TOP 100
[Average CPU (s)] = total_worker_time / (qs.execution_count * 1000000)
,[Total CPU (s)] = total_worker_time / 1000000
,[Execution count] = qs.execution_count
-- quote text into CSV friendly format
,[Individual Query] = '"' + 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) + '"'
-- quote text into CSV friendly format
,[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 [Total CPU (s)] DESC;
-- * list of all current traces
select * from sys.traces
-- * list all categories, classes and columns available to SQL trace
/*
Script: CH10 Trace View.sql
Author: James Rowland-Jones
Title: Professional SQL Server 2008: Internals and Performance Tuning
*/
SELECT cat.name AS category_name
,ev.name AS event_name
,col.name AS column_name
,col.is_filterable AS column_is_filterable
,col.max_size AS column_max_size
,col.[type_name] AS column_type_name
,sub.subclass_name AS subclass_name
,sub.subclass_value AS subclass_value
FROM sys.trace_categories cat
JOIN sys.trace_events ev
ON cat.category_id = ev.category_id
JOIN sys.trace_event_bindings bi
ON bi.trace_event_id = ev.trace_event_id
JOIN sys.trace_columns col
ON bi.trace_column_id = col.trace_column_id
LEFT JOIN sys.trace_subclass_values sub
ON sub.trace_column_id = bi.trace_column_id
AND sub.trace_event_id = bi.trace_event_id
ORDER BY cat.name
,ev.name
,col.name
,sub.subclass_value
-- * List definition of defatul SQL trace
/*
Script: CH10 Trace Definition.sql
Author: James Rowland-Jones
Title: Professional SQL Server 2008: Internals and Performance Tuning
*/
DECLARE @vTraceID INT;
SET @vTraceID = 1;
WITH filter AS
( SELECT columnid
,CASE logical_operator
WHEN 0 THEN 'AND'
WHEN 1 THEN 'OR'
ELSE 'err'
END AS logical_operator
,CASE comparison_operator
WHEN 0 THEN ' = '
WHEN 1 THEN ' <> '
WHEN 2 THEN ' > '
WHEN 3 THEN ' < '
WHEN 4 THEN ' >= '
WHEN 5 THEN ' <= '
WHEN 6 THEN ' LIKE '
WHEN 7 THEN ' NOT LIKE '
END AS comparison_operator
,value
FROM ::fn_trace_getfilterinfo(@vTraceID)
)
SELECT cat.name AS CategoryName
,evt.name AS EventName
,col.name AS ColumnName
,STUFF ( ( SELECT ' | ' + child_fil.logical_operator
+ child_fil.comparison_operator
+ CAST(child_fil.value AS VARCHAR(MAX)
)
FROM filter child_fil
WHERE parent_fil.columnid = child_fil.columnid
FOR XML PATH ('')
),1,1,''
) AS ColFilters
FROM fn_trace_geteventinfo(@vTraceID) evi
JOIN sys.trace_events evt
ON evi.eventid = evt.trace_event_id
JOIN sys.trace_categories cat
ON evt.category_id = cat.category_id
JOIN sys.trace_columns col
ON evi.columnid = col.trace_column_id
LEFT JOIN filter parent_fil
ON col.trace_column_id = parent_fil.columnid
GROUP BY cat.name
,evt.name
,col.name
,parent_fil.columnid
ORDER BY cat.name
,evt.name
,col.name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment