表格与索引查找无需寻找

huangapple go评论58阅读模式
英文:

Table with index lookup without seek

问题

以下是翻译好的内容:

自SQL Server重新启动以来,以下代码返回不同类型的索引操作计数以及每种操作的最后执行时间。每个查询执行对指定索引的单独查找、扫描、查找或更新都被计为对该索引的使用,并增加相应的计数器。但是,当我运行以下查询时,有些情况下会返回索引查找计数为0,而查找计数大于0的情况。

例如,我有一个名为HEAP的表,该表有两个非聚集索引。对该表运行以下代码,结果如下。问题:如果没有索引查找,那么是如何使用查找的?由于它是一个HEAP表,查找是RID Lookup。不幸的是,sys.dm_db_index_usage_stats不提供导致这些统计信息的查询的详细信息。

表名 表大小 索引名称 索引类型 索引大小 查找次数 扫描次数 查找次数 更新次数 最后查找 最后扫描 最后查找 最后更新
Table1 15GB NULL HEAP 11.6GB 0 6 23 0 NULL 2023年5月8日 16:42:50 2023年5月6日 19:51:20 NULL
Table1 15GB idx_appNumber NONCLUSTERED 11.6GB 23 0 0 0 2023年5月6日 19:51:20 NULL NULL NULL
SELECT OBJECT_NAME(IX.OBJECT_ID) 表名
	   ,IX.name AS 索引名称
	   ,IX.type_desc 索引类型
	   ,SUM(PS.[used_page_count]) * 8 索引大小KB
	   ,IXUS.user_seeks AS 查找次数
	   ,IXUS.user_scans AS 扫描次数
	   ,IXUS.user_lookups AS 查找次数
	   ,IXUS.user_updates AS 更新次数
	   ,IXUS.last_user_seek AS 最后查找
	   ,IXUS.last_user_scan AS 最后扫描
	   ,IXUS.last_user_lookup AS 最后查找
	   ,IXUS.last_user_update AS 最后更新
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
英文:

From the time SQL Server restarted, following code returns counts of different types of index operations and the time each type of operation was last performed. Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter. But when I run the following query, in some cases it returns count of index seeks to be 0 and the count of lookups to be greater than 0.

For example, I have a HEAP table that has two nonclustered indexes. And on that table, the following code is showing the result as follows. Question: If there was no index seek then how Lookup was used? Please since it's a HEAP table, the lookups are RID Lookup. Unfortunately, sys.dm_db_index_usage_stats does not gives the details about the queries that caused this statistics.

Table Table_Size IndexName IndexType IndexSize SeeksCount ScansCount LookupsCount UpdateCount LastSeek LastScan LastLookup LastUpdate
Table1 15GB NULL HEAP 11.6GB 0 6 23 0 NULL 5/8/2023 4:42:50 PM 5/6/2023 7:51:20 PM NULL
Table1 15GB idx_appNumber NONCLUSTERED 11.6GB 23 0 0 0 5/6/2023 7:51:20 PM NULL NULL NULL
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 SeeksCount
	   ,IXUS.user_scans AS ScansCount
	   ,IXUS.user_lookups AS LookupsCount
	   ,IXUS.user_updates AS UpdatesCount
	   ,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

答案1

得分: 2

你可以清楚地看到idx_appNumber索引已经有了23次查找,这发生在堆上的RID查找同时发生的时候。这会是查询计划中一个相当正常的索引查找 -> RID查找对。

我建议将非聚集索引之一更改为聚集索引,拥有一个堆和一个非聚集索引没有任何意义。这将完全消除查找操作。为了快速加载,你可以截断表格并删除索引,然后在之后重新创建索引。

英文:

You can clearly see that the idx_appNumber index has had 23 seeks, this has happened at the same time as the RID lookups on the heap. This would be a pretty normal Index Seek -> RID Lookup pair in a query plan.

I'd advise to change one of the non-clustered indexes to clustered, it makes no sense to have a heap with an NCI. This would remove the lookups completely. For fast loading, you can truncate the table and drop the index, then recreate the index afterwards.

huangapple
  • 本文由 发表于 2023年5月11日 08:11:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76223324.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定