Sql-server – Can a un-used NONCLUSTERED INDEX still enhance query speed

database-designindex-tuningnonclustered-indexsql serversql-server-2016

This is a strange situation, but I'm hoping someone has an answer.

During some performance troubleshooting, we added a NONCLUSTERED INDEX to a table, as was requested by sp_BlitzIndex. We checked its usage the next day, and it showed 0 reads (0 scans/seeks, 0 singleton lookups), so we disabled it.

The very next minute, we get a complaint of the same app-slowness (performance trouble) that we were trying to check out & resolve in the first place when we added the INDEX.

Now, I know in theory, this sounds purely coincidental. The INDEX was provably, measurably, NOT USED. Disabling it SHOULD NOT have caused query performance degradation. But it's almost TOO coincidental.

Question

So my question, simply enough, is thus:

Is it at all possible, that a NONCLUSTERED INDEX, whose usage-stats (from the DMVs / sp_BlitzIndex) show NO usage, still have been helping query performance somehow on the affected table?

Best Answer

Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan.

I have come across situations when SQL Server has decided to use stats from one index and scan/seek another index.

Edit - This might not be applicable cause I just realized that you have disabled the index. I have not tested that scenario.

Related Solutions

Sql-server – Nonclustered index column order

Generally, an index should be on

(equalitycol1, equalitycol2, ..., nonequalitycol1, , nonequalitycol2, ...)
INCLUDE
(outputonlycol1, outputonlycol2, ...)

Simply (ignoring ranges) this breaks down into

equalitycol: WHERE equalitycol = something. Most selective first. SARGable. Seeks.
nonequalitycol: WHERE nonequalitycol <> something. Residual filters
outputonlycol: SELECT outputonlycol. No filtering or ordering

ORDER BY and GROUP BY columns will be equality or non-equality columns.

In this case...

Query 1 doesn't match the index at all, really, so most likely won't use the index. However, the optimiser may decide it's cheaper to use the index then use other table access such as a table scan or a RID/bookmark lookup.

For query 2, the optimiser may decide to use the index because col1 would be an equality match. And then use the unsorted col5 from the INCLUDE. YMMV, but there is a higher chance.

For the WHERE clause order, no: the optimiser works it out. Two indexes (col1, col2) and (col2, col1) are different though. Note (col1, col2) will be contained by (col1, col2, col3, col4) though so the first isn't needed.

Sql-server – Nonclustered Index still doing index scan

Try to create this index:

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(EdpCode, DateSubmitted)
INCLUDE (FinalApprovedRejectedBy, FinalAction, FinalActionDate, FinalActionReason );

Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article SQL Server Index Design Guide

Question

Best Answer

Related Solutions

Sql-server – Nonclustered index column order

Sql-server – Nonclustered Index still doing index scan

Related Question