Performance Tuning - No.3 How to tuning Index

http://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx#S2

1.Identifying Logically Fragmented Indexes

 

-- Create required table structure only. -- Note: this SQL must be the same as in the Database loop given in the -- following step. SELECT TOP 1 DatbaseName = DB_NAME() ,TableName = OBJECT_NAME(s.[object_id]) ,IndexName = i.name ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2) INTO #TempFragmentation FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = -999 -- Dummy value just to get table structure. ; /* -- Loop around all the databases on the server. EXEC sp_MSForEachDB 'USE [?]; -- Table already exists. INSERT INTO #TempFragmentation SELECT TOP 10 DatbaseName = DB_NAME() ,TableName = OBJECT_NAME(s.[object_id]) ,IndexName = i.name ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2) FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL -- Ignore HEAP indexes. AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Fragmentation %] DESC ; ' */ INSERT INTO #TempFragmentation SELECT 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 * FROM #TempFragmentation WHERE [Fragmentation %]>=10 ORDER BY [Fragmentation %] DESC -- Tidy up. DROP TABLE #TempFragmentation

2.Cost of Missing Indexes

 

 

SELECT TOP 10 [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) , avg_user_impact , TableName = statement , [EqualityUsage] = equality_columns , [InequalityUsage] = inequality_columns , [Include Cloumns] = included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY [Total Cost] DESC;

 

3.  Identifying Most Costly Unused Indexes

-- Create required table structure only. -- Note: this SQL must be the same as in the Database loop given in the following step. SELECT TOP 1 DatabaseName = DB_NAME() ,TableName = OBJECT_NAME(s.[object_id]) ,IndexName = i.name ,user_updates ,system_updates ,user_seeks ,user_scans ,user_lookups -- 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 ; ' */ INSERT INTO #TempUnusedIndexes SELECT TOP 10 DatabaseName = DB_NAME() ,TableName = OBJECT_NAME(s.[object_id]) ,IndexName = i.name ,user_updates ,system_updates ,user_seeks ,user_scans ,user_lookups 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 OR 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

 

 

Examining this output it is possible to see how each index has been used by looking at the “user_…” columns.  The “user_seeks” column identifies the number of times an index seek operation has been used to traverse this index to resolve a T-SQL statement. 

The “user_scan” column identifies the number time an index scan operation has been used. 

The “user_lookups” column identifies the number of times the index has been used in an index lookup operation .  The “user_update” column to identifies how many times this index has been updated due to the table in which the index is associated has been updated by an UPDATE statement.  

 

4. Identifying Most Costly Queries by I/O

 

SELECT TOP 10 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count ,[Total IO] = (total_logical_reads + total_logical_writes) ,[Execution count] = qs.execution_count ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.text ,DatabaseName = DB_NAME(qt.dbid) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Average IO] DESC;

 

5. Identifying Most Costly Queries by CPU

 

SELECT TOP 10 [Average CPU used] = total_worker_time / qs.execution_count ,[Total CPU used] = total_worker_time ,[Execution count] = qs.execution_count ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.text ,DatabaseName = DB_NAME(qt.dbid) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Average CPU used] DESC;

 

6. Identifying Queries Most Often Blocked

SELECT TOP 10 [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count ,[Total Time Blocked] = total_elapsed_time - total_worker_time ,[Execution count] = qs.execution_count ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.text ,DatabaseName = DB_NAME(qt.dbid) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Average Time Blocked] DESC;

 

 

7. Find Longest Running Query

http://blog.sqlauthority.com/2009/01/23/sql-server-2008-2005-find-longest-running-query-tsql-part-2/

SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC GO