I understand that using the first index (the clustered one) allows SQL Server to retrieve any column in the record once it has found the record it wants (using the where clause). Whereas, the second index requires a separate lookup to get any column not in the index which does take more time.

I am having a hard time understanding why the first index would be used when it requires tremediously more IO and time than the second index to return the same records. Why isn't it obvious to the query optimizer that the first index costs more than the second index?

For example, executing the following queries (the first one uses the clustered index, the second one uses the second index, the third one is being forced to use the second index though it has a non-indexed column):

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

you get this:

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.

Though these queries are using a bogus account number so that no actual records are found, when I ran them with a real account number I got similar IO usage and times.