SQL : What is a Clustered Index Scan, why/when it is it used?

Question

SQL Server 2008

Looking at execution plan says Clustered Index Scan taking up 70%, Why? What is it?

Answer 1

A Clustered Index is a special index in SQL Server and is the order in which SQL Server will physically store the data on the disk (hence you can only have one clustered index per table, as you can only physically store it one way :) ).

So, your SQL is causing the engine to scan the table using this index.

Answer 2

A clustered Index forces that the underlying table records be physically saved in the order defined by the index. It is usually used by the primary key.

If you're getting a Clustered Index Scan, and you're using a order by clause against some key other than the primary key, you're most probably missing a better index...

Answer 3

A clustered index scan indicates that all the rows of the clustered index are being read. It generally performs poorly on large tables and is often an indication that additional indexing or query tuning are needed.

See also: Clustered Index Scan Showplan Operator

Answer 4

Most likely it's looking through the primary key index which by default is clustered. Not quite a table scan but close enough.

In simple terms, you are missing an index that matches your WHERE clause and includes the SELECT clause.

It can also happen when you do SELECT * FROM ... because it's easier to scan the PK/clustered index than use any WHERE index + key lookup

Schema + query would help.