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')

复制代码