Sql Server 索引使用情况

--查询数据库索引碎片状况

use dbname;

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 

ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 

indexstats.avg_fragmentation_in_percent 

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 

INNER JOIN sys.indexes ind  

ON ind.object_id = indexstats.object_id 

AND ind.index_id = indexstats.index_id 

WHERE indexstats.avg_fragmentation_in_percent > 50 

ORDER BY indexstats.avg_fragmentation_in_percent DESC ;

--查询数据库索引使用状况

select db_name(database_id) as N'数据库名称',  

        object_name(a.object_id) as N'表名称',  

        b.name N'索引名称',

        user_seeks N'索引查找次数',

        user_scans N'索引扫描次数',

        last_user_seek N'索引最后查找时间',

       last_user_scan N'索引最后扫描时间',

        rows as N'索引所属表行数'

from sys.dm_db_index_usage_stats a join

      sys.indexes b

      on a.index_id = b.index_id

     and a.object_id = b.object_id

     join sysindexes c

      on c.id = b.object_id

--where database_id=db_id()   ---当前数据库

-- and object_name(a.object_id)   like 'test%' ---待检索表名

 order by user_seeks,user_scans,object_name(a.object_id)

--查看那些被大量更新却很少被使用的索引，这类索引应该被移除

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

--最高维护代价的索引，Maintenance cost高的应该被移除

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

--使用频繁的索引，这类索引不应被移除

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

--碎片最多的索引，这类索引应该rebuild，否则会严重拖累数据库的性能

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

--自上次SQL Server重启后，找出完全没有使用的索引，这类索引应该小心对待，不能一概而论，要看是什么原因导致这种问题

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

 

--查看索引统计的相关信息

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

/*

因为查询计划是根据统计信息来的，索引的选择同样取决于统计信息，

所以根据统计信息更新的多寡可以看出数据库的大体状况，20%的自动更新对于大表来说非常慢。

*/

 --查询数据库缺失索引状况

 SELECT  avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,  

        last_user_seek ,  

        last_user_scan ,  

        [statement] AS [Object] ,  

        'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'  

        + CONVERT(VARCHAR(32), D.index_handle) + '_'  

        + REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')  

        + ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')  

        + CASE WHEN equality_columns IS NOT NULL  

                    AND inequality_columns IS NOT NULL THEN ','  

               ELSE ''  

          END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('  

                                                              + included_columns  

                                                              + ')', '') AS Create_Index_Syntax  

FROM    sys.dm_db_missing_index_groups AS G  

        INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle  

        INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle  

ORDER BY statement,PossibleImprovement desc

--索引碎片整理

1）删除索引并重建

2）使用 DROP_EXISTING 语句重建索引

3）使用 ALTER INDEX REBUILD 语句重建索引(avg_fragmentation_in_percent>30%)

4）使用 ALTER INDEX REORGANIZE 重新组织索引（5%<avg_fragmentation_in_percent<=30%)