INDEX--索引相关信息查看

定位

INDEX--索引相关信息查看

1. --查看可能缺失的索引
   
2. SELECT
   
3. mig.*
   
4. ,migs.*
   
5. ,mid.*
   
6. FROM sys.dm_db_missing_index_group_stats AS migs
   
7. INNER JOIN sys.dm_db_missing_index_groups AS mig
   
8. ON (migs.group_handle = mig.index_group_handle)
   
9. INNER JOIN sys.dm_db_missing_index_details AS mid
   
10. ON (mig.index_handle = mid.index_handle)

复制代码

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

复制代码

1. --查看索引结构
   
2. ;WITH T1
   
3. AS (
   
4.     SELECT IC.* ,
   
5.     C.name AS ColumnName
   
6.     FROM sys.index_columns IC
   
7.     INNER JOIN sys.columns C
   
8.     ON IC.object_id = C.object_id
   
9.     AND IC.column_id = C.column_id
   
10. )
    
11. SELECT
    
12. --DB_ID() AS DatabaseID,
    
13. DB_NAME() AS DatabaseName ,
    
14. SCHEMA_NAME(TB.schema_id) AS SchemaName ,
    
15. TB.name AS TableName ,
    
16. --IX.index_id AS IndexId,
    
17. ISNULL(IX.name, '') AS IndexName ,
    
18. IX.type_desc AS IndexType ,
    
19. ISNULL(IXK.ix_index_column_name, '') AS IndexKey ,
    
20. ISNULL(IXK.ix_index_include_column_name, '') AS IndexIncludeColumn ,
    
21. ISNULL(IX.filter_definition, '') AS FilerDefinition ,
    
22. IX.is_primary_key AS IsPrimaryKey ,
    
23. IX.is_unique AS IsUnique ,
    
24. IX.is_disabled AS IsDisabled ,
    
25. IX.fill_factor AS FileFactor ,
    
26. IX.has_filter AS HasFiler ,
    
27. IX.ignore_dup_key AS IgnoreDuplicateKey ,
    
28. DS.name AS DataSpace ,
    
29. ISNULL(PS.name, '') AS PartitionScheme ,
    
30. ISNULL(IXC.ColumnName, '') AS PartitionKey ,
    
31. IX.allow_page_locks AS AllowPageLocks ,
    
32. IX.allow_row_locks AS AllowRowLocks ,
    
33. IX.is_padded AS IsPadded
    
34. FROM sys.tables TB
    
35. INNER JOIN sys.indexes IX
    
36. ON TB.object_id = IX.object_id
    
37. INNER JOIN sys.data_spaces DS
    
38. ON DS.data_space_id = IX.data_space_id
    
39. LEFT JOIN sys.partition_schemes PS
    
40. ON IX.data_space_id = PS.data_space_id
    
41. LEFT JOIN T1 AS IXC ON IX.object_id = IXC.object_id
    
42. AND IX.index_id = IXC.index_id
    
43. AND IXC.partition_ordinal = 1
    
44. OUTER APPLY (
    
45. SELECT ix_index_column_name =
    
46. STUFF(REPLACE(REPLACE((
    
47. SELECT CASE WHEN T1.is_descending_key = 1
    
48.     THEN T1.ColumnName+ ' desc'
    
49.     ELSE T1.ColumnName
    
50.     END AS column_name
    
51. FROM T1
    
52. WHERE IX.object_id = T1.object_id
    
53. AND IX.index_id = T1.index_id
    
54. AND T1.is_included_column = 0
    
55. ORDER BY index_column_id
    
56. FOR XML AUTO),'<T1 column_name="',','), '"/>', ''),1, 1, '') ,
    
57. ix_index_include_column_name =
    
58. STUFF(REPLACE(REPLACE(( SELECT
    
59. T1.ColumnName AS column_name
    
60. FROM T1
    
61. WHERE IX.object_id = T1.object_id
    
62. AND IX.index_id = T1.index_id
    
63. AND T1.is_included_column = 1
    
64. ORDER BY index_column_id
    
65. FOR XML AUTO),'<T1 column_name="',','), '"/>', ''),1, 1, '')
    
66. ) AS IXK
    
67. --WHERE TB.name='t_coupon'
    
68. ORDER BY TableName,IndexKey,IndexIncludeColumn

复制代码

1. --查看索引的使用情况
   
2. --索引在重建或删除新建时sys.dm_db_index_usage_stats中相关的数据会被清除
   
3. --索引在重整是不会清除sys.dm_db_index_usage_stats的数据
   
4. SELECT  
   
5. DB_NAME(ixu.database_id) DataBase_Name,
   
6. OBJECT_NAME(ixu.object_id) Table_Name,
   
7. ix.name Index_Name ,
   
8. (
   
9.     SELECT MAX(s.rows)
   
10.     FROM   sysindexes s
    
11.     WHERE  s.id = ixu.object_id
    
12. ) AS Table_Rows ,
    
13. STATS_DATE(ixu.object_id, ixu.index_id) AS statistic_time,
    
14. ixu.user_updates,
    
15. ixu.last_user_seek,
    
16. ixu.user_seeks,
    
17. ixu.last_user_scan,
    
18. ixu.user_scans,
    
19. ixu.last_user_lookup,
    
20. ixu.user_lookups,
    
21. ixu.user_updates/(ISNULL(ixu.user_seeks,0)
    
22. +ISNULL(ixu.user_scans,0)+1) AS UseRate
    
23. FROM sys.dm_db_index_usage_stats ixu
    
24. INNER JOIN sys.indexes ix
    
25. ON ixu.object_id = ix.object_id
    
26. AND ixu.index_id = ix.index_id
    
27. INNER JOIN sys.objects ob
    
28. ON ixu.object_id = ob.object_id
    
29. WHERE   ob.type = 'U'
    
30. AND ob.is_ms_shipped = 0
    
31. AND ixu.database_id=DB_ID()
    
32. --AND ix.object_id=OBJECT_ID('TableName')

复制代码