5

I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server. The mentioned query is quite simple, and has the following shape>

set statistics io on
set statistics time on

SELECT t.field3
FROM Table t --WITH(INDEX(Index2))
WHERE t.field1 = 6 AND t.field2 = 'B' 
AND t.field3 >= CAST ('01-07-2012' As DateTime) AND t.field3 < CAST ('01-10-2012' As DateTime)
ORDER BY t.field3

If I run this query on Table t (which contains 52M rows) the statistics retrieved are:

  • Logical reads: 95
  • CPU time: 15 ms
  • Elapsed time: 6 ms

If I check the query's real execution plan it shows a 100% of an Index Seek on Index1, which is non clustered and contains fields field1, field2, field3 in this order. However, if I uncomment the line to force using Index2 instead, which contains the same fields in same order, but it is clustered (primary key), the statistics are:

  • Logical reads: 1456
  • CPU time: 15 ms
  • Elapsed time: 12 ms

Although total time spent is not incredibly worse, I would like to understand what is happening here. Can anyone give me an explanation? Is there a performance difference apart from little time difference?

Thank you very much !!

2
  • 5
    The leaf pages of the clustered index include something for every column of the table not just the columns in the index definition. Presumably the NCI gets about 15 times more entries per leaf page due to this. Could also be due to greater (internal) fragmentation and lower page density. Please post the CREATE TABLE Feb 13, 2013 at 14:26
  • 3
    Put another way - the clustered index is the table. So it contains all of the data in the non-clustered index columns, plus all of the data in the other columns too. SQL Server is making the right choice by choosing the narrower index, since it's all that's required for your query, and all of the data can be obtained by reading fewer pages. Feb 13, 2013 at 14:41

1 Answer 1

7

It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index. The clustered index is the actual table data. Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.

Let's set up a test scenario:

use testdb;
go

if exists (select 1 from sys.tables where name = 'TestTable')
begin
    drop table TestTable;
end
create table dbo.TestTable
(
    id int identity(1, 1) not null
        constraint PK_TestTable_Id primary key clustered,
    some_int int not null,
    some_string char(128) not null,
    some_bigint bigint not null
);
go

create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into dbo.TestTable(some_int, some_string, some_bigint)
    values(@i, 'hello', @i * 1000);

    set @i = @i + 1;
end

So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id) and a nonclustered index (IX_TestTable_SomeInt). As you've seen in your testing, but just for thoroughness:

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4

Here we have a nonclustered index scan on the IX_TestTable_SomeInt index. We have 4 logical reads for this operation. Now let's force the clustered index to be used.

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22

Here with the clustered index scan we have 22 logical reads. Why? Here's why. It all matters on how many pages that SQL Server has to read in order to grab the entire result set. Get the average row count per page:

select
    object_name(i.object_id) as object_name,
    i.name as index_name,
    i.type_desc,
    ips.page_count,
    ips.record_count,
    ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;

Take a look at my result set of the above query:

enter image description here

As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index. Therefore, in order to satisfy the query more pages need to be read from the clustered index.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.