关于SQLserver的索引的一些脚本

--判断无用的索引：
SELECT TOP 30  
        DB_NAME() AS DatabaseName ,  
        ‘[‘ + SCHEMA_NAME(o.Schema_ID) + ‘]‘ + ‘.‘ + ‘[‘  
       + OBJECT_NAME(s.[object_id]) + ‘]‘ AS TableName ,  
        i.name AS IndexName ,  
        i.type AS IndexType ,  
       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 30  
       ROUND(s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks  
                                                           + s.user_scans ),  
             0) AS [Total Cost] ,  
        s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks  
                                                              + s.user_scans ) AS Improvement_Measure ,  
        DB_NAME() AS DatabaseName ,          d.[statement] AS [Table Name] ,  
        equality_columns ,  
        inequality_columns ,  
        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  
WHERE   s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks  
                                                              + s.user_scans ) > 10  
ORDER BY [Total Cost] DESC ,  
        s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks  
                                                      + s.user_scans ) DESC  


---看看那些索引维护成本很高 通俗的说就是更新次数大于使用这个索引的次数
SELECT TOP 20  
        DB_NAME() AS DatabaseName ,  
        ‘[‘ + SCHEMA_NAME(o.Schema_ID) + ‘]‘ + ‘.‘ + ‘[‘  
        + OBJECT_NAME(s.[object_id]) + ‘]‘ AS TableName ,  
        i.name AS IndexName ,  
        i.type AS IndexType ,  
        ( 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(‘{0}‘)  
        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  
DB_NAME() AS DatabaseName  
, ‘[‘+SCHEMA_NAME(o.Schema_ID)+‘]‘+‘.‘+‘[‘+OBJECT_NAME(s.[object_id]) +‘]‘AS TableName  
, i.name AS IndexName  
, i.type as IndexType  
, (s.user_seeks + s.user_scans + s.user_lookups) AS Usage  
, s.user_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  
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   

关于SQLserver的索引的一些脚本

标签：main   date   missing   obj   ctp   scan   object_id   pac   property