什么“聚集索引扫描(Clustered)"意味着 SQL Server 执行计划? [英] What "Clustered Index Scan (Clustered)" means on SQL Server execution plan?

问题描述

我有一个查询无法执行，并显示由于文件组 'DEFAULT' 中的磁盘空间不足，无法为数据库 'TEMPDB' 分配新页面".

在排除故障的过程中，我正在检查执行计划.有两个标记为聚集索引扫描(聚集)"的昂贵步骤.我很难弄清楚这意味着什么?

如果您对聚簇索引扫描(聚簇)"有任何解释或有关在哪里可以找到相关文档的建议，我将不胜感激?

解决方案

如果您对聚集索引扫描"有任何解释，我将不胜感激(聚集)"

我会尽量用最简单的方式，为了更好地理解你需要理解索引查找和扫描.

SO 让我们建立表格

使用 tempdb GO创建表 scanseek (id int , name varchar(50) default ('some random names') )在 scanseek(ID) 上创建聚集索引 IX_ID_scanseek声明@i int设置@i = 0而 (@i <5000)开始插入 scanseek选择 @i, 'Name' + convert( varchar(5) ,@i)设置@i =@i+1结束

索引查找是 SQL 服务器使用索引的

您可以使用下面的 DMV 检查您的表根节点和叶节点

--检查索引级别选择索引级别,record_count,page_count,avg_record_size_in_bytesFROM sys.dm_db_index_physical_stats(DB_ID('tempdb'),OBJECT_ID('scanseek'),NULL,NULL,'DETAILED')去

现在我们在ID"列上有了聚集索引

让我们寻找一些直接匹配的记录

select * from scanseek where id =340

然后查看执行计划

您直接在查询中请求了行，这就是为什么您获得聚集索引 SEEK .

聚集索引扫描:当 Sql 服务器从上到下读取聚集索引中的 Row(s) 时.例如在非关键列中搜索数据.在我们的表中 NAME 是非关键列，所以如果我们在 name 列中搜索一些数据，我们将看到 clustered index scan 因为所有行都在聚集索引叶级别.

例子

select * from scanseek where name = 'Name340'

请注意:我做了这个简短的回答只是为了更好地理解，如果您有任何问题或建议，请在下方评论.

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'".

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?

解决方案

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-tree structure of the index to seek directly to matching records

you can check your table root and leaf nodes using the DMV below

-- 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"

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 .

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.

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.

这篇关于什么“聚集索引扫描(Clustered)"意味着 SQL Server 执行计划?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！