There is a myth going around that for columns which are used as predicates always should have a clustered index which is backed up and recommended by Micorsoft http://technet.microsoft.com/en-us/library/ms190639. aspx
Let us see whether this indeed is true.Let us create a table T with approximately a million records.
Create Table T(Id Int ,NM Char(20) DEFAULT ”)
GO
Insert into T(id)
Select TOP(1000000)str(reverse(RAND(a.object_id)),10)/10000*2511+b.object_id/10000 From sys.columns a CROSS JOIN sys.columns b
As suggested let’s create a clustered index on the Id column which we will be using as a predicate.
Create Clustered Index IX_Id on t(Id)
GO
Run the following SET commands so that the no of pages processed,IO response and CPU times are displayed.
Set Statistics IO ON
GO
Set Statistics Time ON
GO
Now let’s run the following query which will use the clustered index .
Select MAX(nm) from T where id between 10000 and 50001 option (maxdop 1)
As expected we get a clustered seek as it is SARGable and a stream aggregate.Maxdop is used to prevent any kind of parallelism since the no of records the query is processing is huge. I am working on a single CPU 2 Core processor hence the setting maxdop 1 you can change the maxdop settings based on the CPU for your box.
CPU and IO time
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 92 ms.
No of pages processed
Table ‘t’. Scan count 1, logical reads 294, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now let us drop the index and test the query with a nonclustered index.
Drop Index IX_Id on T
GO
Create NonClustered Index IX_Id on t(Id)
GO
And run the following query again
Select MAX(nm) from T where id between 10000 and 50001 option (maxdop 1)
No of pages processed
Table ‘t’. Scan count 1, logical reads 5061, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
CPU and IO time
SQL Server Execution Times:
CPU time = 561 ms, elapsed time = 596 ms.
Ouch does not look efficient at all….plus the optimizer is recommending using a nonclustered covering index. Let us try doing that.
Drop Index IX_Id on T
GO
Create NonClustered Index IX_Id on t(Id)Include(NM)
GO
Select MAX(nm) from T where id between 10000 and 50001 option (maxdop 1)
No of pages processed
(1 row(s) affected)
Table ‘t’. Scan count 1, logical reads 272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU and IO time
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 55 ms.
Well well well..Perfect..What happened here ?? No of pages processed are 10% less and more importantly the CPU and IO time is almost half of what the clustered index took.So how did that happen?
Lets go back to the basics of how a clustered index is maintained.In a clustered index the leaf level is maintained by a clustered key and the Btree is scanned down from the start of the range using the next page pointers for a range query.
Note: Here we are talking of a ideal indexes which has 0% fragementation.
So what happens in a nonclusterd index.Well it’s the same as a clustered index where leaf order is maintained by the index keys except that a lookup is done for the columns which are not a part of the nonclustered index.Basically not much difference except than the lookup’s.
That’s the reason clustered indexes are always called as covering indexes.It also is the largest index in the table as it basically covers all the columns in it at the leaf level.But as the index becomes big and big and the ranges become large the efficiency decreases as it needs to process more heavy pages at the leaf level.
So what do with large range queries ? The only solution left would be to use a nonclustered covering index which is small,seekable and basically covers the columns with the least overhead and is an overall win win situation.
Regards
Sachin Nandanwar
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
