Here are the SQL Server scripts :
Find missing indexes :: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 ROUND(s.avg_total_user_cos 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_gr INNER JOIN sys.dm_db_missing_index_gr ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_de ON d.index_handle = g.index_handle ORDER BY [Total Cost] DESC Find 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]) , 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_stat 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]) , 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_stat 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_i 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 Find 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]) , 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_stat 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]) , 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_stat 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_i 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 Find 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]) , 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_stat 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]) , 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_stat 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_i ORDER BY [Usage] DESC' SELECT TOP 20 * FROM #TempUsage ORDER BY [Usage] DESC DROP TABLE #TempUsage Find 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]) , i.name AS IndexName , ROUND(s.avg_fragmentation_ INTO #TempFragmentation FROM sys.dm_db_index_physical_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]) , i.name AS IndexName , ROUND(s.avg_fragmentation_ FROM sys.dm_db_index_physical_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_i ORDER BY [Fragmentation %] DESC' SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC DROP TABLE #TempFragmentation Find slowest queries :: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST(qs.total_elapsed_time AS [Total Elapsed Duration (s)] , qs.execution_count , SUBSTRING (qt.text,(qs.statement_sta ((CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ELSE qs.statement_end_offset END - qs.statement_start_offset) , 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.sq CROSS APPLY sys.dm_exec_query_plan(qs. ORDER BY total_elapsed_time DESC |
|
来自: goldbomb > 《SqlServer》