I have a small but actively queried table in SQL Server that has 94 rows that are frequently read and\or updated. The clustered index safely fits into 1 8KB page and has significant empty space on that page. This leads me to believe that any future updates won't push it onto a second page.

In addition to the clustered index, over the years several nonclustered indexes have been added.

My question is, on a conceptual level, can the nonclustered indexes improve SELECT performance? If SQL Server can't read less than 1 data page at a time, then any nonclustered index read could be just as easily satisfied by reading the single page for the clustered index or am I misunderstanding the concept?

I don't wan to include the table definition, but here's some stats from sp_BlitzIndex on that table showing it's use:

ClusteredIndex      Reads: 1 (1 scan) Writes:180,544,146
Nonclustered Index  Reads: 0 Writes:103
Nonclustered Index  Reads: 63,425,182 (57,447,576 seek 5,977,606 scan) Writes:180,544,146
Nonclustered Index  Reads: 150,953,542 (150,953,542 seek) Writes:180,233,055
Nonclustered Index  Reads: 0 Writes:311,091

The clustered index doesn't seem to get read, but it I would think it could without the other indexes. Further interesting detail, sp_BlitzLock shows 39 deadlocks in this database and all 39 involved this tiny table, which I find interesting.


do the nonclustered indexes even matter?

Yes. You have multiple copies of this table (or subsets of it), and updates have to be coordinated across all the copies. Query performance is probably not enhanced by the non-clustered indexes, but they are probably the cause of your deadlocks.

But for SELECT query performance, the non-clustered indexes shouldn't help.

  • 1
    Thanks, I should have been more clear, I meant could the nonclustered indexes be of benefit, and not the generic "do they matter". I have edited the question to make that clearer. – BCM Dec 1 '20 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.