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'".
我有一个查询无法执行“无法为数据库'TEMPDB'分配新页面，因为文件组'DEFAULT'中的磁盘空间不足”。
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?
在解决问题的方式我正在检查执行计划。标记为“Clustered Index Scan（Clustered）”的两个代价高昂的步骤。我很难找出这意味着什么？
I would appreciate any explanations to "Clustered Index Scan (Clustered)" or suggestions on where to find the related document?
我将不胜感激“聚集索引扫描（聚集）”的任何解释或有关在何处查找相关文档的建议？
4 个解决方案
#1
22
I would appreciate any explanations to "Clustered Index Scan (Clustered)"
我将不胜感激“Clustered Index Scan（Clustered）”的任何解释
I will try to put in the easiest manner, for better understansing 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-tree structure of the index to seek directly to matching records
索引查找是SQL服务器使用索引的b树结构直接查找匹配记录的位置
you can check yours table root and leaf nodes using 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
并查看执行计划
your requested rows directly in the query thats why u got 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 scan because all the rows are in clustered index leaf level.
聚簇索引扫描：当Sql server在聚簇索引中从上到下读取行时。例如，在非关键列中搜索数据。在我们的表中，NAME是非键列，因此如果我们将在名称列中搜索某些数据，我们将看到聚簇索引扫描，因为所有行都处于聚簇索引叶级别。
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.
请注意：我只是为了更好地理解这个答案，如果您有任何问题或建议请在下面评论。
#2
4
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 Indexes are 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.
如果由于记录数量而导致聚簇索引的搜索成本非常高，则可能需要在表中为经常搜索的字段添加非聚集索引，例如用于筛选记录范围的日期字段。
#3
3
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上空间不足。
#4
0
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将显示该步骤的描述。这将使您对“聚集索引扫描（聚集）”以及所涉及的所有其他步骤有基本的了解。
#1
22
I would appreciate any explanations to "Clustered Index Scan (Clustered)"
我将不胜感激“Clustered Index Scan（Clustered）”的任何解释
I will try to put in the easiest manner, for better understansing 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-tree structure of the index to seek directly to matching records
索引查找是SQL服务器使用索引的b树结构直接查找匹配记录的位置
you can check yours table root and leaf nodes using 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
并查看执行计划
your requested rows directly in the query thats why u got 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 scan because all the rows are in clustered index leaf level.
聚簇索引扫描：当Sql server在聚簇索引中从上到下读取行时。例如，在非关键列中搜索数据。在我们的表中，NAME是非键列，因此如果我们将在名称列中搜索某些数据，我们将看到聚簇索引扫描，因为所有行都处于聚簇索引叶级别。
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.
请注意：我只是为了更好地理解这个答案，如果您有任何问题或建议请在下面评论。
#2
4
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 Indexes are 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.
如果由于记录数量而导致聚簇索引的搜索成本非常高，则可能需要在表中为经常搜索的字段添加非聚集索引，例如用于筛选记录范围的日期字段。
#3
3
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上空间不足。
#4
0
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将显示该步骤的描述。这将使您对“聚集索引扫描（聚集）”以及所涉及的所有其他步骤有基本的了解。