SQL Server Index Internals – Clustered Index Seek (Range Scan)

In today’s blog, we will be diving into yet another topic from the SQL Server Indexes, Clustered Index Seek (Range Scan).

Seek and Scan are the most used terminologies when it comes to data access methods by SQL Server. And also, there is a never-ending misconception around which method is better over another. Well, it all depends. Depends on the query. SQL Server will choose the method best for the given query.

Before we start, a quick note on the index internals.

index

When an index is created, SQL Server internally builds a B-tree structure. There is a root node, followed by some intermediate level nodes and finally a leaf node. When SQL Server is scanning a clustered index, it is scanning all pages at the leaf level node leading to maximum I/O. Seek on the other hand looks for a particular set of data and traverses the B-Tree structure, moving from the root page to the leaf level.

It is important to remember that while dealing with indexes involving seek & scan, the number of pages involved is a critical factor. In this blog, we will look into an instance when Clustered Index Seek can be misleading. This is where ‘Range Scan’ comes into play.

We are using AdventureWorks database for the purposes of this demo. And then enable the STATISTICS IO and Actual Execution Plan before running a SELECT statement that reads all the data from the DatabaseLog table.

USE AdventureWorks
GO

SET STATISTICS IO ON

-- Enable Actual Execution Plan
SELECT * FROM dbo.Databaselog

The execution plan shows a Table Scan, since this table is a heap (no clustered index on the table). So, the data pages are not guaranteed to be in order. It is important to note that clustered index scan and table scan essentially mean the same thing.

index

Next, let’s read the data from a table that has clustered index

SELECT * FROM Person.Person

The Execution Plan confirms that a Clustered Index Scan is taking place.

index

The Messages tab displays a total of 572 logical reads. This value is crucial for performance evaluation.

   

index

Next, a ‘seek’ operation is performed on this clustered index when we run the previous SELECT query with a WHERE predicate which returns all entries with a ContactID value of 1.

SELECT * FROM Person.Person
WHERE ContactID = 1

The execution plan confirms that a Clustered Index Seek operation is taking place.

index

In the Messages tab, the value for logical reads is only 2, stating that only two pages were touched, the root page and the respective leaf level page where the ContactID 1 resides.

In case of a predicate containing a relational operator such as ‘greater than’ or ‘less than’, clustered index seek can prove to be misleading.

Let’s do a minor change to the predicate like below.

SELECT * FROM Person.Person
WHERE ContactID > 1 

Most of you would expect a scan to take place since the predicate has a ‘greater than’ operator, but the execution plan shows a Clustered Index Seek.

index

In the Messages tab, the number of logical reads shows 572, proving that the scan was performed nonetheless. It is not about which is the better of the two, but instead, comes down to the amount of I/O taking place in the background.

This background process can be termed as Range Scan. There is a Clustered Index on ContactID which is a part of the predicate that is locating the record having an ID of 1. Because the data is sorted in the order of ContactID column, SQL Server seeks to value 1 and from there on, proceed to scan the rest of the data and hence termed Range Scan.

Unfortunately, the iterator in our case is not very intuitive and hence does not inform us about the Range Scan that is taking place in the background.

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.