zondag 2 maart 2014

SQL Server : Execution plans - Table Queries - Clustered Index Scan (part I)

Introduction

One area that I have to discover in more depth are execution plans in SQL Server.  For some reason a query at my customer doesn't use an index in a query. We don't know why and that triggered me studying execution plans in more detail. So I will write some blogposts about the execution plan in the future. The first blogpost is about the Clustered Index scan.

Clustered Index scan

On of the common operators is the Clustered Index Scan. This happens when no filter is added in the WHERE clause, for instance. In this example I've queried the Product Table and a clustered Index Scan occurs.


In the tooltip the Object tells you that PK_Product_ProductID is scanned. Other interesting properties are Actual Number of rows and Estimated Number of Rows. If these differs the statistics may be out of date. 

An Index Scan happens when the optimizer decides that there are some many rows to return that it's quicker to scan all the values in the index rather than using the keys that are provided by the index.If the Scan returns more rows than expected then this operator can be optimized.

What is the difference between a Index scan and a Table Scan? In a table without a clustered index, data pages are not linked together and when an clustered index is present, the pages are linked together and makes scans a bit faster than a heap table.

Below a table that distinquishes the different types of operations...

 
Scan
Seek
Heap
Table Scan

Clustered Index
Clustered Index Scan
Clustered Index Seek
Non-clustered Index
Index Scan
Index Seek
Borrowed from Craig freedmans blog

Here is some more info about scans


Conclusion

A clustered Index scan is needed when all the data is needed in the query, but can be optimized when a filter is used that could result in a index seek. Is a index scan bad? Hmmm not if you need all the data.

Greetz,

Hennie