December 22, 2015 at 11:58 am
I have come across several tables recently where the clustered PK on Column (X), for example, is also been duplicated as a nonclustered index. When I look at operational and usage DMV's for the indexes there are reads on the NCI. Why would SQL Server use a NCI to retrieve rows if it has a clustered PK that is on the same index? I would have expected the usage stats to have no reads at all. Did someone do this to prevent aggressive use of the clustered PK, ie. to prevent excessive locking on the clustered index?
December 22, 2015 at 12:20 pm
lmarkum (12/22/2015)
I have come across several tables recently where the clustered PK on Column (X), for example, is also been duplicated as a nonclustered index. When I look at operational and usage DMV's for the indexes there are reads on the NCI. Why would SQL Server use a NCI to retrieve rows if it has a clustered PK that is on the same index? I would have expected the usage stats to have no reads at all. Did someone do this to prevent aggressive use of the clustered PK, ie. to prevent excessive locking on the clustered index?
If the NC index contains all of the columns required by a particular query, it's likely to be faster to read then the clustered index (because fewer pages have to be accessed).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 22, 2015 at 12:27 pm
If the clustered and NCI are the same column how would reading the NCI be fewer pages to access?
December 22, 2015 at 12:45 pm
Hitting the clustered index is hitting ALL THE DATA for each seek/scan. Remember, the clustered PK IS the data at the leaf level of the index. All that is on the nonclustered index is the indexed columns and perhaps at the leaf level any INCLUDEd columns.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 22, 2015 at 1:04 pm
Thanks Phil and Kevin. By focusing on what columns were in the indexes I was missing that the clustered index is all the table data ordered logically at the leaf level, so it has more pages. For example, the clustered index on one of these tables is just over 1 GB. The NCI that contains the same column as the clustered PK is only 106 MB since in that case that one column is the only thing in the index.
Is having a NCI be on the same column as the clustered PK a fairly common practice? I've just never heard of doing this and was surprised to find it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply