Sql-server – Why does SQL Server use this index

index-tuningnonclustered-indexsql-server-2008-r2

I have two indexes on a table with 786602 rows:

CREATE NONCLUSTERED INDEX [IX1] 
ON [dbo].[O] ([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3]) 
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
           SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX2] 
ON [dbo].[O]([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10]) 
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
           SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The following queries both use IX2:

select [c1], [c2], [c3] 
from [O] 
where A = 9 AND B = 10

select [c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10] 
from [O] 
where A = 9 AND B = 10

Disabling IX2 shows that query 1 then uses IX1 with the same performance.

Why does the first query not use IX1 as it seems to PERFECTLY match the requirements?

Is SQL Server really that smart and does not use it to show me that it can be removed? 🙂

Is it save to just DROP X1? (that was my original intention so I started testing the usage and just wondered why X1 is never used)

Best Answer

While I cannot confirm this with any official documentation, the reason must be based on the total size of included columns and the cost.

With the test i conducted, the indexes with 10 columns and 3 columns had exactly the same cost and logical/physical reads, which I assumed to be the same with the original poster's environment. I gradually increased the size of the 4th column, and at certain point, the opimizer started choosing the 1st index for the 1st statement. The table used had about 55k rows.

This only shows an anecdotal evidence and a theory on certain behavior of the optimizer. I hope someone who really knows the meanism of it will explain us all why, if this theory isn't correct.

Related Question