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.