Execution plan showing missing non-clustered index on already partitioned clustered indexes

We have a query where the table is partitioned on column Adate.

Row count: 56595943, partition scheme - yearly, no of partitions - 300

Clustered index columns : empid, Adate

Query :

select top 1 Adate
from emp
where empid = 134556 and Adate <= {ts '7485-09-01 00:00:00.0'}
order by Adate desc

The actual execution plan returns a clustered index seek operation with 93% of the total query cost on clustered index key.

But why is the optimizer recommending a missing index with 92% of cost?

missing index details: Improve query cost:92%

create nonclustered index IDX_NC on dbo.emp([empid], [Adate])

The missing index has an improvement measure of 14755268, as per Microsoft the improvement measure baseline is 1,000,000

Why is this happening? Do you recommend to have a nonclustered index on already clustered index columns?

Well - consider this:

• you do have the clustered index on (empid, adate)
• the clustered index contains the whole data, e.g. the leaf level pages of the clustered index contain the whole data records (all the columns in your table)

If you are searching and the query uses the clustered index, it might still need to load much more data than is actually needed.... the whole record, as many times as your criteria is found.

If you have a non-clustered index on just (empid, Adate), and your query really only requires Adate (in its SELECT list of columns), then this index will be much smaller - it contains only those two columns (none of the overhead of all the other columns, which are not needed for your current query). So scanning this index, or loading these index pages, will load much less data compared to the clustered index.

From that point of view, yes, even having a nonclustered index on the same columns that make up your clustered index can be beneficial for certain query scenarios - that's probably what the SQL Server query optimizer picks up here.