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 T1
AS (
    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 IsPadded
FROM sys.tables TB
INNER JOIN sys.indexes IX 
ON TB.object_id = IX.object_id
INNER JOIN sys.data_spaces DS 
ON DS.data_space_id = IX.data_space_id
LEFT JOIN sys.partition_schemes PS 
ON IX.data_space_id = PS.data_space_id
LEFT JOIN T1 AS IXC ON IX.object_id = IXC.object_id
AND IX.index_id = IXC.index_id
AND IXC.partition_ordinal = 1
OUTER 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_name
FROM T1
WHERE IX.object_id = T1.object_id
AND IX.index_id = T1.index_id
AND T1.is_included_column = 0
ORDER BY index_column_id
FOR XML AUTO),'<T1 column_name="',','), '"/>', ''),1, 1, '') ,
ix_index_include_column_name = 
STUFF(REPLACE(REPLACE(( SELECT
T1.ColumnName AS column_name
FROM T1
WHERE IX.object_id = T1.object_id
AND IX.index_id = T1.index_id
AND T1.is_included_column = 1
ORDER BY index_column_id
FOR 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 UseRate
FROM sys.dm_db_index_usage_stats ixu
INNER JOIN sys.indexes ix 
ON ixu.object_id = ix.object_id
AND ixu.index_id = ix.index_id
INNER JOIN sys.objects ob 
ON ixu.object_id = ob.object_id
WHERE   ob.type = 'U'
AND ob.is_ms_shipped = 0
AND ixu.database_id=DB_ID()
--AND ix.object_id=OBJECT_ID('TableName')