SQL Server performance analysis

These SQL Server performance analysis queries can be used to determine query performance and suggest indexes to help with this.


--Improvement Measure recommendations:

SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


--Top 50 statements with highest AVG Cpu Time:

SELECT TOP 50 qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2) as query_text, qt.dbid, dbname=db_name(qt.dbid), qt.objectid FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg CPU Time] DESC


--Top 50 queries based on CPU utilization (total worker time) including the full text of the executed statement.

SELECT TOP 50 total_worker_time, total_worker_time/execution_count AS [Avg worker Time], last_worker_time, execution_count, last_execution_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text
FROM master.sys.dm_exec_query_stats as qs
CROSS APPLY master.sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_worker_time desc;


--Physical index fragmentation report (inequivalent to "Index physical statistics" report in SSMS)


SET NOCOUNT ON;

DECLARE @ThreshHold AS FLOAT;
DECLARE @FragmentationRange AS INT;
DECLARE @Command AS NVARCHAR(4000); 

DECLARE @ObjectId AS INT;
DECLARE @IndexId AS INT;
DECLARE @PartitionNumber AS BIGINT;
DECLARE @FragmentCount AS FLOAT;
DECLARE @Percent AS FLOAT;
DECLARE @ObjectName AS NVARCHAR(130); 
DECLARE @SchemaName AS NVARCHAR(130); 
DECLARE @IndexName AS NVARCHAR(130); 
DECLARE @PartitionCount AS BIGINT;

SET @ThreshHold = 5
DECLARE @IndexStatistics TABLE(ObjectId INT, IndexId INT, PartitionNumber BIGINT, FragmentCount FLOAT, [Percent] FLOAT, [ObjectName] NVARCHAR(130), [SchemaName] NVARCHAR(130), [IndexName] NVARCHAR(130), [PartitionCount] BIGINT)

INSERT INTO @IndexStatistics
SELECT
       [index].object_id AS ObjectId,
       [index].index_id AS IndexId,
       [index].partition_number AS PartitionNumber,
       [index].fragment_count FragmentCount,
       [index].avg_fragmentation_in_percent AS [Percent],
       QUOTENAME(o.name) [ObjectName],
       QUOTENAME(s.name) [SchemaName],
       QUOTENAME(i.name) [IndexName],
       ISNULL(pt.[Count], 0) [PartitionCount]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') [index]
INNER JOIN sys.objects AS o ON o.object_id = [index].object_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id  
INNER JOIN sys.indexes AS i ON i.object_id = [index].object_id AND i.index_id = [index].index_id
LEFT OUTER JOIN
(
  SELECT [object_id],index_id, count (*) [Count]
  FROM sys.partitions 
  GROUP BY [object_id],index_id
) pt ON pt.object_id = [index].object_id AND pt.index_id = [index].index_id
WHERE [index].fragment_count > 100 AND [index].avg_fragmentation_in_percent > CASE WHEN @ThreshHold>= 2 THEN (@ThreshHold - 1) ELSE @ThreshHold END --AND [index].index_id > 0
ORDER BY 
       --[index].fragment_count DESC, 
       [index].avg_fragmentation_in_percent DESC;
       
SELECT * from @IndexStatistics WHERE IndexName IS NOT NULL
order by [Percent] desc



Fragmented indexes can be either reorganised or rebuilt via SSMS.  When rebuilding be careful with doing it online vs. offline as it can make the table inaccessible and lock the database while processing.  This can be dealt with by rebuilding in a temporary table:

ALTER INDEX IndexName ON tablename REBUILD WITH (SORT_IN_TEMPDB=ON)



UPDATE 10 May 2013

The fillfactor option will cause the index to be rebuilt with spare space in each index page. This allows the index to grow into this space before it has to write to the end of the file and cause index fragmentation. The number is the percentage of the page that is pre-filled during the index - i.e. a fill factor of 80 means that there is 20% unused space available.

ALTER INDEX IndexName ON tablename REBUILD WITH (SORT_IN_TEMPDB=ON, FILLFACTOR = 80)