Geek City: Ordered Seeks and Scans

Kalen Delaney — Fri, 21 Jan 2011 23:28:00 GMT

I got a couple of really great questions during my SSWUG Workshop this morning, as I was discussing seeks and scans, and since the answers to the two questions are very related, I decided to address both of them in more detail in this blog post.

Most people think about a seek operation as retrieving just one or a few rows, and a scan as accessing the entire table or index leaf level. And that usually is what I mean when I am just describing SQL Server behavior. But there is also the possibility that SQL Server could perform a 'partial scan', starting at some row in the table or index leaf, and then scanning only to a particular ending row. However, there is no operator in a query plan called 'partial scan'. So how can you tell when you have one?

Before I specifically answer that, let me tell you about the first question in the workshop this morning. I had been showing the Properties window (available from View/Properties Window), pointing out that when you click on one of the icons in a graphical query plan, the Properties window shows the same information that is in the yellow box that pops up, plus additional information. One of the values shown in one called 'Ordered', and when 'Ordered' has the value true, then there is also a value called 'Scan Direction', which might make you think that ordering is only relevant to scans. But you will see True values for 'Ordered' even when the operation is a seek. This is usually an indication that a partial scan is being performed.

And because I was talking about heaps being unordered sets of rows, one of the delegates asked: "Are index seeks against a heap always unordered?"

The answer is, of course, "it depends". If you have a covered query, the index access could be an ordered seek of the covering index, even though the underlying table is a heap. Here is an example from the AdventureWorks database. I make a copy of the Sales.SalesOrderHeader table, so it is a heap, and then build a nonclustered index on the Freight column.

IF EXISTS (SELECT * FROM dbo.SalesOrderHeader)
DROP TABLE dbo.SalesOrderHeader;
GO
SELECT * INTO dbo.SalesOrderHeader
FROM Sales.SalesOrderHeader;
GO
CREATE INDEX ix_Freight ON dbo.SalesOrderHeader(Freight);
GO

Then I look at the plan for a covered query using the ix_Freight index:

SELECT AVG(Freight)
FROM dbo.SalesOrderHeader
WHERE Freight < 100;

And here is the plan showing the index seek:

And the Properties sheet for the Index Seek shows that Ordered - True and Scan Direction = FORWARD.

This is an example of an ordered seek on a nonclustered index, or what we can think of as a partial scan, or range scan.

The other question during the workshop was how to tell if the seek was really a range scan. Part of the answer is to look for the Ordered = True value, as I just illustrated. Another thing to notice in the graphical query plan is the thickness of the line moving rows to the left from the Index Seek, indicating that LOTS of rows are being returned from the Seek. That's another clue that we have a partial scan.

We can also get partial scans with clustered indexes. I'll build a clustered index on my new table.

CREATE CLUSTERED INDEX clus_OrderID ON dbo.SalesOrderHeader(SalesOrderID);
GO

There are 31465 rows in the dbo.SalesOrderHeader table, with SalesOrderID values ranging from 43659 to 75123. I'll SELECT a small range of data:

SELECT * FROM dbo.SalesOrderHeader
WHERE SalesOrderID < 44000

The plan shows a Clustered Index Seek, and the Properties shows Ordered = True.

If I make the range bigger, and look for SalesOrderID < 60000, I still get an Index Seek, and the same when using an upper limit of 75000. If I run a query that returns ALL the rows, I still get an Index Seek.

Both queries are using an Index Seek, but notice the difference in the thickness of the arrows.

It turns out that the only time the optimizer will tell us it is doing a scan is when it knows absolutely that it will be looking at every row in the table or index leaf. And the only way it can know this is if there is no filter condition (NO WHERE clause) or no index on the filter condition. With the index on SalesOrderID, there is no way the optimizer can be absolutely sure that it will have to scan the whole table. Someone might have inserted a value of 100001 since the last time statistics were updated, and we wouldn't want to perform a scan and return a value that is not in the specified range. So the Index Seek in the plan really just means that SQL Server will access the data using the index, but not with a complete scan. There is something in the query that give a starting point, a stopping point or both.

I've got another Workshop for SSWUG on Index Internals, coming up on February 25th. Hopefully, I'll get a lot more great questions during that one!

Thanks!

~Kalen

Did You Know? I'm doing 3 more online seminars with SSWUG!

Kalen Delaney — Fri, 11 Jun 2010 22:46:00 GMT

As I told you in April, I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops can be replayed for up to a week after the broadcast, and you can even buy a DVD of the workshop.

You can get more details by clicking on the workshop name, below, or check out the announcement on the SSWUG site at http://www.sswug.org/editorials/default.aspx?id=1948

Query Plans Workshop (June 30, 2010)
Learn about basic elements, tools for exploring your query plans, how to detect inefficient plans and best practice guidelines

Indexing Workshop (July 23, 2010)
Learn about basic index structures, tools for exploring those structures, index design considerations and best practice guidelines

Plan Caching Workshop (Aug 27, 2010)
Learn about basic management of the plan cache, tools for exploring the plans in cache and detecting when they are reused, query design considerations to provide the optimal caching behavior and best practice guidelines

For those of you who attended my presentation at the Colorado PASS seminar last Wednesday in Denver, note that the 3rd workshop (Plan Caching), includes most of what I talked about in my presentation, and a lot more besides. The scripts from that session are now up on my site at www.sqlserverinternals.com/conferences

Have fun!

~Kalen

Search results matching tags 'Query Plans', 'training', and 'indexes'

Geek City: Ordered Seeks and Scans

Did You Know? I'm doing 3 more online seminars with SSWUG!