Tipping point

YOU NEED ADVENTUREWORKS TO RUN THIS EXAMPLE:

When working with Executionplans, I have often wondered about why a query could end up with two different executionplans, depending on your where clause.

Lets have an example.

— CODE START —
select ProductID, OrderQty
from sales.SalesOrderDetail
where ProductID between 776 and 777
— CODE END —

Returns the following executionplan:

Basically that means: I look in a nonclustered index to find the rows and then (one by one) i look up in the clustered index to find the data.

 

If I write

— CODE START —
select ProductID, OrderQty
from sales.SalesOrderDetail
where ProductID between 776 and 778
— CODE END —

then I get this executionplan

Basically that means: I scan all records in the clustered index to find the rows that I want.

This is called the Tipping point, and that is the point where the SQL Server decides that it is more efficient to scan all data instead of using a nonclustered index. because what happens when I use an index is that on the leaf level of the index I have the clustered indexkey, so when I have found the records in the nonclustered index that fits with my where clause, then it must go to the clustered index to find the data that I want to show that are not in the nonclustered index (in this situation OrderQty).

At some point the Query optimizer decides to forget about the nonclustered index, because it estimates that it is more efficient to scan the clustered index because the where clause is very wide.

That point is called “Tipping Point”.

Does it work?

If we continue with another example

— CODE START —

select ProductID, OrderQty
from sales.SalesOrderDetail
where ProductID between 776 and 800

— CODE END —

In this example the execution of the query took 1,21229 SQL Units (the sum of IO Cost and CPU Cost). If I had forced it to use the nonclustered index:

— CODE START —

select ProductID, OrderQty
from sales.SalesOrderDetail with (Index(IX_SalesOrderDetail_ProductID))
where ProductID between 776 and 800

— CODE END —

it would have taken 7,01266 SQL Units. So yes… The SQL Server is in most situations quite clever when it comes to choosing between a scan and using an index. Look at the plans below, and you will see, that the one the query optimzer chooses it self, is much more efficient than the one I choose by adding the tablehint.

That was tipping point. A nice little thing about this is the question. Could I have done it even more faster?

Yes, I could have used a covering index, meaning that I will add Qty to the leaf level of the nonclusterend index. If I do this, then the Query will only take 0,04 SQL Units!! YES THAT IS CORRECT.. 0,04!!!!. The cost is space and time to insert/update rows.

But that is a completely other story.

Søren

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s