Did You Know: My next web seminar is this Wednesday!

Kalen Delaney — Mon, 28 Jun 2010 03:40:00 GMT

I know, I haven't made a geeky post in a while... but I promise that I'll do one after this seminar. I'll take some of the best questions that I get during the chat and write them up here. Query Plans Workshop (June 30, 2010) Learn about basic elements,...(read more)

Geek City QUIZ: What happens when you change a column in an index from a key column to an included column?

Kalen Delaney — Mon, 24 Nov 2008 07:15:00 GMT

This quiz is not really a generic question about changing an index key column to be an included column; it's about a behavior noticed by a reader in one particular query. Dejan Nakarada-Kordic from New Zealand sent me a very interesting puzzle. He had a reproducible query for which he thought an existing nonclustered index should be used, and it only ended up being used if the column was defined as an INCLUDED column instead of a key column. Here is the script Dejan sent me:

-- First, create the table
IF EXISTS (SELECT * FROM sys.tables
            WHERE schema_id = 1 and name = 'Table1')
     DROP TABLE dbo.Table1;
GO
CREATE TABLE dbo.Table1(
    Transaction_Serial_No [bigint IDENTITY(1,1) NOT NULL,
    Channel] char(4) NOT NULL,
    Retry_Counter int NOT NULL,
CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED
       (Transaction_Serial_No));
GO

--populate the table with 10000 rows
SET NOCOUNT ON;
declare @counter int;
set @counter = 0;
while (@counter < 10000)
begin
    set @counter = @counter + 1;
    insert into dbo.Table1(channel, retry_counter)
    values('ch1', 0);
    insert into dbo.Table1(channel, retry_counter)
    values('ch2', 0);
end;

-- Now build a nonclustered index on
IF EXISTS (SELECT * FROM sys.indexes
            WHERE object_id = OBJECT_ID('dbo.Table1') AND name = 'IX_Channel')
    DROP INDEX IX_Channel ON dbo.Table1;
GO
CREATE NONCLUSTERED INDEX IX_Channel ON dbo.Table1 (Channel, Retry_Counter);
GO

-- Examine the query plan for this query:
SELECT TOP(20) Transaction_Serial_No
        FROM dbo.Table1
WHERE Channel = 'ch2'
         AND Retry_Counter <= 10
ORDER BY Transaction_Serial_No;

The plan should show you a Clustered Index Scan being performed, even though the nonclustered index appears to be a covering index.

The query contains all 3 columns used in the query. The columns Channel and Retry_Counter are defined index keys, and because the table has a clustered index, the clustered key Transaction_Serial_No is also part of the index.

If you rebuild the index to use Retry_Counter as an INCLUDED column, the plan changes.

CREATE NONCLUSTERED INDEX IX_Channel ON dbo.Table1 (Channel)
INCLUDE ( Retry_Counter) WITH (DROP_EXISTING = ON);

-- Examine the plan again:

SELECT TOP(20) Transaction_Serial_No
FROM dbo.Table1
WHERE Channel = 'ch2'
AND Retry_Counter <= 10
ORDER BY Transaction_Serial_No;

The plan now shows a nonclustered index seek:

WHY should changing the column from a key column to an INCLUDED column change the plan?

You can try to figure out the answer for yourself, or read on.

-------------------------------------------------------------------------------------------------------------------------

It turns out that there are two extra factors to consider.

1) Although covering indexes are a great thing, and the optimizer will choose them over other possible indexes most of the time, the cost of sorting can sometimes outweigh the benefit of a covering index.

2) When a column is an INCLUDED column in an index, it comes after all the key columns and after the clustered index key that is always part of a nonclustered index on a table that has a clustered index.

So, the original index on (Channel, Retry_Counter) is the same as an index on (Channel, Retry_Counter, Transaction_Serial_No), but moving Retry_Counter to be an INCLUDED column changes the index to be on the columns (Channel, Transaction_Serial_No), with Retry_Counter in last place, and unsorted.

The query wants the data sorted on Transaction_Serial_No, and scanning the clustered index gives us the data in Transaction_Serial_No order. The first nonclustered index is sorted first by Channel, then by Retry_Counter, then by Transaction_Serial_No so that index is not useful for the sort.

The second nonclustered index is sorted first by Channel, then by Transaction_Serial_No. BUT the query has limited the data to only data with the Channel value equal to 'ch2', so since the first column values we're retrieving are all the same, returning data in the nonclustered index order will return the data in Transaction_Serial_No order and no sorting needs to be done.

You might notice in this data set that all the values for Retry_Counter are also identical, so you might then think we should be able to use the first nonclustered index to avoid having to sort the data, but the optimizer cannot be sure that the Retry_Counter values are all identical. The statistics might indicate that all the values are the same, but the optimizer can't be sure the statistics are 100% up to date.

Here are some things to try:

1) If you remove the ORDER BY, you will see that the first index, with no INCLUDED column, is used.

2) If you change the second condition to searching for a constant (AND Retry_Counter = 0), the first index can be used. If both of the first two index columns are constants in the retrieved data, it will be sorted by the third column (Transaction_Serial_No).

3) If you force the query to use the nonclustered index when Retry_Counter is a key, you'll see that SQL Server has to do a sort:

DROP INDEX IX_Channel ON dbo.Table1;
GO
CREATE NONCLUSTERED INDEX IX_Channel ON dbo.Table1 (Channel, Retry_Counter);
GO
SELECT TOP(20) Transaction_Serial_No
        FROM dbo.Table1 WITH (index = ix_channel)
    WHERE Channel = 'ch2'
        AND Retry_Counter <= 10
ORDER BY Transaction_Serial_No;
GO

Notice that the SORT operation is estimated to be 95% of the total query cost, so SQL Server would definitely like to avoid that if possible.

So the issue wasn't really because of INCLUDED columns, it was because of the order the columns appeared in the index, and the fact that SQL Server wants to try to avoid sorting whenever possible.

Have fun!

~Kalen

Did You Know: Can Query Tuning Become Unnecessary?

Kalen Delaney — Mon, 08 Sep 2008 01:49:00 GMT

I wrote a commentary for the SQL Server Magazine e-newsletter last week, and was expecting a to get a bit more feedback than I did.So I'm hoping my blog readers can help out. I was commenting on a message that one of my business partners had sent out wondering about the changes that new higher speed storage technology might bring.

Please read the whole commentary here (It's not that long.)

Basically, the comment is that new storage technology is appearing that could have I/O's per second (IOPS) rates thousand of times faster than they are now.

Most of the reasons for building good indexes and tuning our queries is to reduce the number of reads that SQL Server would have to perform. But if the reads are so fast that even with tables scans our queries are always 'fast enough', would you spend time on query and index tuning?

At this point, my question is NOT whether you think there will be available and affordable storage systems that can perform as suggested. My question is "IF there were such systems, would you still spend time tuning your queries and evaluating the best indexes?" Also, if query tuning does become unnecessary, what would you do instead to maintain your database system's performance? Or would you be out of a job?

I'd really like to hear what you think!

Thanks

~Kalen

Kalen Delaney : query tuning

Did You Know: My next web seminar is this Wednesday!

Geek City QUIZ: What happens when you change a column in an index from a key column to an included column?

Did You Know: Can Query Tuning Become Unnecessary?