INDEX--索引相关信息查看

--==============================================--查看可能缺失的索引SELECT mig.*,migs.*,mid.* FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_details AS mid ON (mig.index_handle = mid.index_handle)

--查看索引碎片--'DETAILED'选项会导致扫描全表，慎用SELECT OBJECT_NAME (ips.[object_id]) AS 'Object Name',si.name AS 'Index Name',   ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',   ips.page_count AS 'Pages',   ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'FROM sys.dm_db_index_physical_stats (DB_ID ('SQLskillsDB'), NULL, NULL, NULL, 'DETAILED')ips  CROSS APPLY sys.indexes si WHERE   si.object_id = ips.object_id   AND si.index_id = ips.index_id   AND ips.index_level = 0 -- only the leaf level   AND ips.avg_fragmentation_in_percent > 10; -- filter on fragmentation GO

--===============================================--查看索引结构;WITH T1AS (    SELECT IC.* ,    C.name AS ColumnName    FROM sys.index_columns IC    INNER JOIN sys.columns C     ON IC.object_id = C.object_id    AND IC.column_id = C.column_id)SELECT --DB_ID() AS DatabaseID,DB_NAME() AS DatabaseName ,SCHEMA_NAME(TB.schema_id) AS SchemaName ,TB.name AS TableName ,--IX.index_id AS IndexId,ISNULL(IX.name, '') AS IndexName ,IX.type_desc AS IndexType ,ISNULL(IXK.ix_index_column_name, '') AS IndexKey ,ISNULL(IXK.ix_index_include_column_name, '') AS IndexIncludeColumn ,ISNULL(IX.filter_definition, '') AS FilerDefinition ,IX.is_PRimary_key AS IsPrimaryKey ,IX.is_unique AS IsUnique ,IX.is_disabled AS IsDisabled ,IX.fill_factor AS FileFactor ,IX.has_filter AS HasFiler ,IX.ignore_dup_key AS IgnoreDuplicateKey ,DS.name AS Dataspace ,ISNULL(PS.name, '') AS PartitionScheme ,ISNULL(IXC.ColumnName, '') AS PartitionKey ,IX.allow_page_locks AS AllowPageLocks ,IX.allow_row_locks AS AllowRowLocks ,IX.is_padded AS IsPaddedFROM sys.tables TBINNER JOIN sys.indexes IX ON TB.object_id = IX.object_idINNER JOIN sys.data_spaces DS ON DS.data_space_id = IX.data_space_idLEFT JOIN sys.partition_schemes PS ON IX.data_space_id = PS.data_space_idLEFT JOIN T1 AS IXC ON IX.object_id = IXC.object_idAND IX.index_id = IXC.index_idAND IXC.partition_ordinal = 1OUTER APPLY ( SELECT ix_index_column_name = STUFF(REPLACE(REPLACE(( SELECT CASE WHEN T1.is_descending_key = 1     THEN T1.ColumnName+ ' desc'    ELSE T1.ColumnName    END AS column_nameFROM T1WHERE IX.object_id = T1.object_idAND IX.index_id = T1.index_idAND T1.is_included_column = 0ORDER BY index_column_idFOR xml AUTO),'<T1 column_name="',','), '"/>', ''),1, 1, '') ,ix_index_include_column_name = STUFF(REPLACE(REPLACE(( SELECTT1.ColumnName AS column_nameFROM T1WHERE IX.object_id = T1.object_idAND IX.index_id = T1.index_idAND T1.is_included_column = 1ORDER BY index_column_idFOR XML AUTO),'<T1 column_name="',','), '"/>', ''),1, 1, '')) AS IXK--WHERE TB.name='t_coupon'ORDER BY TableName,IndexKey,IndexIncludeColumn

--==========================================--查看索引的使用情况--索引在重建或删除新建时sys.dm_db_index_usage_stats中相关的数据会被清除--索引在重整是不会清除sys.dm_db_index_usage_stats的数据SELECT  DB_NAME(ixu.database_id) DataBase_Name,OBJECT_NAME(ixu.object_id) Table_Name,ix.name Index_Name ,(     SELECT MAX(s.rows)    FROM   sysindexes s    WHERE  s.id = ixu.object_id) AS Table_Rows ,STATS_DATE(ixu.object_id, ixu.index_id) AS statistic_time,ixu.user_updates,ixu.last_user_seek,ixu.user_seeks,ixu.last_user_scan,ixu.user_scans,ixu.last_user_lookup,ixu.user_lookups,ixu.user_updates/(ISNULL(ixu.user_seeks,0)+ISNULL(ixu.user_scans,0)+1) AS UseRateFROM sys.dm_db_index_usage_stats ixuINNER JOIN sys.indexes ix ON ixu.object_id = ix.object_idAND ixu.index_id = ix.index_idINNER JOIN sys.objects ob ON ixu.object_id = ob.object_idWHERE   ob.type = 'U'AND ob.is_ms_shipped = 0AND ixu.database_id=DB_ID()--AND ix.object_id=OBJECT_ID('TableName')