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

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.