Wrong Index Being Used

Brandie Tarvin (2/8/2013)

Lynn Pettis (2/8/2013)

Roger Sabin (2/8/2013)

I understand what's been said.

But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account.

Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?

You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.

Lynn, for my own edification... This is because Name isn't included in either index, right? So the Clustered Index is easier to use to find name as associated with the rest of the data that actually is in the index?

set statistics io on
set statistics time on

select ProcessYear,
ProcessMonth,
VisionAccountNumber,
CustomerName
from UN_MonthlyCMS
where ProcessYear = 2013
and ProcessMonth = 1
and VisionAccountNumber = '1234'

select ProcessYear,
ProcessMonth,
VisionAccountNumber
from UN_MonthlyCMS
where ProcessYear = 2013
and ProcessMonth = 1
and VisionAccountNumber = '1234'

select ProcessYear,
ProcessMonth,
VisionAccountNumber,
CustomerName
from UN_MonthlyCMS with (index (IDX_MonthlyCMS_ProcessYear_ProcessMonth_VisionAccountNumber_CreditPlanNumber))
where ProcessYear = 2013
and ProcessMonth = 1
and VisionAccountNumber = '1234'

set statistics time off
set statistics io off

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

(0 row(s) affected)
Table 'UN_MonthlyCMS'. Scan count 1, logical reads 355528, physical reads 7, read-ahead reads 216495, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2312 ms, elapsed time = 147795 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'UN_MonthlyCMS'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 35 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'UN_MonthlyCMS'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.