索引维护（1）如何获取索引使用情况

-1)服务器名
--检查是否一致 
use master  
go  
select @@servername  
select serverproperty('servername')  
   
--如果不一致，执行下面的语句 
if  serverproperty('servername')  <>   @@servername   
    begin   
   declare   @server   sysname   
   set   @server   =  @@servername    
   exec   sp_dropserver   @server  =   @server   
   set   @server   =  cast(serverproperty('servername')  as   sysname)   
   exec   sp_addserver   @server  =   @server   ,  @local   =   'LOCAL'   
end  
   
/***************************************  
说明：其实就是删除旧的服务器名servername，再添加新的服务器名 
sp_dropserver '旧的服务器名'  
sp_addserver '新的服务器名' , 'LOCAL'  
3、重启SQL SERVER  
4、再运行以下脚本验证一下。 
***************************************/  
   
use master  
go  
select @@servername  
select serverproperty('servername')  

--2)查找索引定义
DECLARE @tbl nvarchar(265)  
SELECT @tbl = 'u_store_c'  --替换表名
   
SELECT o.name,i.index_id, i.name, i.type_desc,  
       substring(ikey.cols, 3, len(ikey.cols))AS key_cols,  
       substring(inc.cols, 3, len(inc.cols)) ASincluded_cols,  
       stats_date(o.object_id, i.index_id) ASstats_date,  
       i.filter_definition  
FROM   sys.objects o  
JOIN   sys.indexes i ON i.object_id = o.object_id  
CROSS  APPLY (SELECT ', ' + c.name +  
                     CASE ic.is_descending_key  
                          WHEN 1 THEN ' DESC'  
                          ELSE ''  
                     END  
              FROM   sys.index_columns ic  
              JOIN   sys.columns c ON ic.object_id = c.object_id  
                                  AND ic.column_id = c.column_id  
              WHERE  ic.object_id = i.object_id  
                AND  ic.index_id = i.index_id  
                AND  ic.is_included_column = 0  
              ORDER BY ic.key_ordinal  
              FOR XML PATH('')) AS ikey(cols)  
OUTER  APPLY (SELECT ', ' + c.name  
              FROM   sys.index_columns ic  
              JOIN   sys.columns c ON ic.object_id = c.object_id  
                                  AND ic.column_id = c.column_id  
              WHERE  ic.object_id = i.object_id  
                AND  ic.index_id = i.index_id  
                AND  ic.is_included_column = 1  
              ORDER  BY ic.index_column_id  
              FOR XML PATH('')) AS inc(cols)  
WHERE  o.name = @tbl  
  AND i.type IN (1, 2)  
ORDER  BY o.name, i.index_id  

--3)每个表上索引的使用情况
SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,  
        i.name AS [Index Name] ,  
        i.index_id ,  
        user_updates AS [Total Writes],  
        user_seeks+ user_scans + user_lookups AS [Total Reads] ,  
        user_updates-( user_seeks + user_scans + user_lookups ) AS [Difference]  
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )  
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]  
                                                      AND i.index_id = ddius.index_id  
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1  
        AND ddius.database_id = DB_ID()  
        AND OBJECT_NAME(ddius.[object_id])='u_store_c'  --替换表名
        AND i.index_id > 1  --非聚集索引 
ORDER BY [Difference] DESC ,  
        [Total Writes]DESC ,  
        [Total Reads]ASC;  
        
        
--4)获取某个索引被使用的情况 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  
SELECT COALESCE(DB_NAME(p.dbid)  
    ,p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)'))  
     AS database_name  
  ,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid)  
    +'.' + OBJECT_NAME(p.objectid, p.dbid) AS OBJECT_NAME,  
    cast ('索引名' as varchar(64)) AS IndexName  
  ,cp.objtype  
  ,p.query_plan  
  ,cp.UseCounts AS use_counts  
  ,cp.plan_handle  
  ,CAST('<?query --' + CHAR(13) + q.text + CHAR(13) + '--?>' AS XML) AS sql_text INTO xxx.xxx.xxx表  
FROM sys.dm_exec_cached_plans cp  
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p  
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q  
WHERE cp.cacheobjtype = 'Compiled Plan'  
AND p.query_plan.exist('//Object[@Index = "[索引名]"]') = 1  
ORDER BY UseCounts DESC,COALESCE(DB_NAME(p.dbid), p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)'))  


--5)丢失索引 
SELECT  user_seeks * avg_total_user_cost *( avg_user_impact *0.01 ) AS [index_advantage] ,  
        dbmigs.last_user_seek ,  
        dbmid.[statement] AS [Database.Schema.Table],  
        dbmid.equality_columns ,  
        dbmid.inequality_columns ,  
        dbmid.included_columns ,  
        dbmigs.unique_compiles ,  
        dbmigs.user_seeks ,  
        dbmigs.avg_total_user_cost ,  
        dbmigs.avg_user_impact  
FROM    sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )  
        INNER JOIN sys.dm_db_missing_index_groupsAS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle  
        INNER JOIN sys.dm_db_missing_index_detailsAS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle  
WHERE   dbmid.[database_id] = DB_ID()  
ORDER BY index_advantage DESC;  

--6)索引上的碎片超过%并且索引体积较大（超过页）的索引。 
SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],  
                                                    DB_ID())+ '].['  
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,  
        i.[name] AS [index_name] ,  
        ddips.[index_type_desc] ,  
        ddips.[partition_number] ,  
        ddips.[alloc_unit_type_desc],  
        ddips.[index_depth] ,  
        ddips.[index_level] ,  
        CAST(ddips.[avg_fragmentation_in_percent]AS SMALLINT) AS [avg_frag_%] ,  
        CAST(ddips.[avg_fragment_size_in_pages]AS SMALLINT) AS [avg_frag_size_in_pages] ,  
        ddips.[fragment_count] ,  
        ddips.[page_count]  
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips  
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]  
                                      AND ddips.[index_id] = i.[index_id]  
WHERE   ddips.[avg_fragmentation_in_percent] > 15  
        AND ddips.[page_count] > 500  
ORDER BY ddips.[avg_fragmentation_in_percent] ,  
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,  
        i.[name]