SQL Server 执行计划中的“Clustered Index Scan (Clustered)”是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31949355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What "Clustered Index Scan (Clustered)" means on SQL Server execution plan?
提问by Bin
I have a query that fails to execute with "Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'".
我有一个查询无法执行，并显示“由于文件组 'DEFAULT' 中的磁盘空间不足，无法为数据库 'TEMPDB' 分配新页面”。
On the way of trouble shooting I am examining the execution plan. There are two costly steps labeled "Clustered Index Scan (Clustered)". I have a hard time find out what this means?
在排除故障的过程中，我正在检查执行计划。有两个标记为“聚集索引扫描（聚集）”的昂贵步骤。我很难弄清楚这意味着什么？
I would appreciate any explanations to "Clustered Index Scan (Clustered)" or suggestions on where to find the related document?
我很感激对“聚集索引扫描（聚集）”的任何解释或有关在哪里可以找到相关文档的建议？
回答by Neeraj Prasad Sharma
I would appreciate any explanations to "Clustered Index Scan (Clustered)"
我很感激对“聚集索引扫描（聚集）”的任何解释
I will try to put in the easiest manner, for better understanding you need to understand both index seek and scan.
我将尝试以最简单的方式，为了更好地理解您需要了解索引查找和扫描。
SO lets build the table
所以让我们建立表格
use tempdb GO
create table scanseek (id int , name varchar(50) default ('some random names') )
create clustered index IX_ID_scanseek on scanseek(ID)
declare @i int
SET @i = 0
while (@i <5000)
begin
insert into scanseek
select @i, 'Name' + convert( varchar(5) ,@i)
set @i =@i+1
END
An index seek is where SQL server uses the b-treestructure of the index to seek directly to matching records
索引查找是 SQL Server 使用索引的b 树结构直接查找匹配记录的地方
you can check your table root and leaf nodes using the DMV below
您可以使用下面的 DMV 检查您的表根节点和叶节点
-- check index level
SELECT
index_level
,record_count
,page_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('tempdb'),OBJECT_ID('scanseek'),NULL,NULL,'DETAILED')
GO
Now here we have clustered index on column "ID"
现在这里我们在列“ID”上有聚集索引
lets look for some direct matching records
让我们寻找一些直接匹配的记录
select * from scanseek where id =340
and look at the Execution plan
并查看执行计划
you've requested rows directly in the query that's why you got a clustered index SEEK .
您已经直接在查询中请求了行，这就是为什么您获得聚集索引 SEEK 的原因。
Clustered index scan:When Sql server reads through for the Row(s) from top to bottom in the clustered index.
for example searching data in non key column. In our table NAME is non key column so if we will search some data in the name column we will see clustered index scanbecause all the rows are in clustered index leaf level.
聚集索引扫描：当Sql server从上到下读取聚集索引中的Row(s)时。例如在非关键列中搜索数据。在我们的表中 NAME 是非关键列，所以如果我们在 name 列中搜索一些数据，我们会看到，clustered index scan因为所有行都在聚集索引叶级别。
Example
例子
select * from scanseek where name = 'Name340'
please note: I made this answer short for better understanding only, if you have any question or suggestion please comment below.
请注意：我做了这个简短的回答只是为了更好地理解，如果您有任何问题或建议，请在下面评论。
回答by Martin Noreke
Expanding on Gordon's answer in the comments, a clustered index scan is scanning one of the tables indexes to find the values you are doing a where clause filter, or for a join to the next table in your query plan.
扩展 Gordon 在评论中的回答，聚簇索引扫描是扫描其中一个表索引以查找您正在执行 where 子句过滤器的值，或者连接到查询计划中的下一个表。
Tables can have multiple indexes (one clustered and many non-clustered) and SQL Server will search the appropriate one based upon the filter or join being executed.
表可以有多个索引（一个聚集索引和许多非聚集索引），SQL Server 将根据正在执行的过滤器或联接搜索适当的索引。
Clustered Indexesare explained pretty well on MSDN. The key difference between clustered and non-clustered is that the clustered index defines how rows are stored on disk.
MSDN 上对聚集索引进行了很好的解释。聚集索引和非聚集索引的主要区别在于聚集索引定义了行在磁盘上的存储方式。
If your clustered index is very expensive to search due to the number of records, you may want to add a non-clustered index on the table for fields that you search for often, such as date fields used for filtering ranges of records.
如果您的聚集索引由于记录数量而导致搜索成本很高，您可能需要在表上为您经常搜索的字段添加非聚集索引，例如用于过滤记录范围的日期字段。
回答by Curt
A clustered index is one in which the terminal (leaf) node of the index is the actual data page itself. There can be only one clustered index per table, because it specifies how records are arranged within the data page. It is generally (and with some exceptions) considered the most performant index type (primarily because there is one less level of indirection before you get to your actual data record).
聚集索引是其中索引的终端（叶）节点是实际数据页本身的索引。每个表只能有一个聚集索引，因为它指定了记录在数据页内的排列方式。它通常（并且有一些例外）被认为是性能最高的索引类型（主要是因为在您到达实际数据记录之前少了一个间接级别）。
A "clustered index scan" means that the SQL engine is traversing your clustered index in search for a particular value (or set of values). It is one of the most efficient methods for locating a record (beat by a "clustered index seek" in which the SQL Engine is looking to match a single selected value).
“聚集索引扫描”意味着 SQL 引擎正在遍历聚集索引以搜索特定值（或一组值）。它是定位记录的最有效方法之一（被 SQL 引擎寻找匹配单个选定值的“聚集索引查找”击败）。
The error message has absolutely nothing to do with the query plan. It just means that you are out of space on TempDB.
错误消息与查询计划完全无关。这只是意味着您在 TempDB 上的空间不足。
回答by Edward Brey
If you hover over the step in the query plan, SSMS displays a description of what the step does. That will give you a baseline understanding of "Clustered Index Scan (Clustered)" and all other steps involved.
如果您将鼠标悬停在查询计划中的步骤上，SSMS 会显示该步骤的功能描述。这将使您对“聚集索引扫描（聚集）”和所有其他涉及的步骤有一个基本的了解。
