如何收集索引使用信息

Question

下面的查询正确地输出了查找、扫描等的数量。我对于理解输出中的某些行有疑问。在查询的某些输出行中，索引名显示为NULL，index_type显示为HEAP，NumOfSeeks显示为0；而在相同的行中，索引名显示为NumOfScans和/或NumOfLookups的一些非零数字。问:我们如何解释这些行？

注:没有一个表具有聚集索引，因此所有表都是堆。但是，在以下查询的输出的大多数行中，索引名正在显示(而不是以NULL形式显示)，而index_type也显示(作为NONSLUSTER)。因此，这个问题是针对上面描述的输出行的。

参考文献：sys.dm_数据库_索引_用法_统计数据.

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
       ,IX.name AS Index_Name
       ,IX.type_desc Index_Type
       ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
       ,IXUS.user_seeks AS NumOfSeeks
       ,IXUS.user_scans AS NumOfScans
       ,IXUS.user_lookups AS NumOfLookups
       ,IXUS.user_updates AS NumOfUpdates
       ,IXUS.last_user_seek AS LastSeek
       ,IXUS.last_user_scan AS LastScan
       ,IXUS.last_user_lookup AS LastLookup
       ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

Answer 1

堆是SQL Server中调用的基表，当它没有聚集索引时。您可以在堆上创建非聚集索引。

堆在sys.indexes中没有名称，因为它们不是索引。堆被描述为无序页面是最常见的方式。索引按索引键中的列对页进行逻辑排序。

您可以返回到sys.tables，并使用ISNULL将索引名替换为堆的基表名。

虽然在某些情况下可以使用无索引的查找，但行是通过内部标识符在堆中标识的，这些标识符不容易向用户公开，或者可以在通常编写的查询中用作可查找的谓词。

Answer 2

没有一个表具有聚集索引，因此所有表都是堆。问:我们如何解释这些行？

如果没有聚集索引，表本身就存储在堆数据结构中，正如您已经注意到的。sys.dm_db_index_usage_stats中的那些行表示在查询计划中堆被使用了多少次、它是否被完全扫描等指标。

根据定义，堆是一组无序的数据，因此不可能像聚集索引所能做到的那样有效地对其进行查找。这就是为什么搜索的数量是0，但是扫描的数量可以超过0(这同样意味着整个堆被扫描了)。如果表上有聚集索引，则数据将存储在B树中，而不是堆。然后，您将看到该聚集索引的一行(及其名称)，而不是堆的行。

无法查找通常是为什么堆的性能不如集群索引表，对于大多数一次不涉及大多数数据的查询来说。