What "Clustered Index Scan (Clustered)" means on SQL Server execution plan?

Question

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?

Answer 1

I have been having issues with performance and timeouts due to a clustered index scan. However another seemingly identical database did not have the same issue. Turns out the COMPATIBILITY_LEVEL flag on the db was different... the version with COMPATIBILITY_LEVEL 100 was using the scan, the db with level 130 wasn't. Performance difference is huge (from more than 1 minute to less that 1 second for same query)

ALTER DATABASE [mydb] SET COMPATIBILITY_LEVEL = 130

Answer 2

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.

Answer 3

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.

Answer 4

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).

The error message has absolutely nothing to do with the query plan. It just means that you are out of space on TempDB.

Answer 5

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.

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.

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.

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.