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.
Best Answer
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.