January 12, 2016 at 3:58 pm
Say you have a table with a clustered index for the PK and a generic non-clustered, non-unique index to cover a specific query. If the clustered index is not fragmented but the non-clustered index is heavily fragmented (say > 90%), would inserts and updates to the underlying table have any more impact to performance than if the non-clustered index was only slightly fragmented (< 5%)?
January 12, 2016 at 4:57 pm
You'll have to test to be sure, but I expect the answer to be no.
Fragmentation of indexes mostly affects scan performance (which includes seeks that are not for singleton rows!).
Modification performance is mostly affected by page splits, so if any of the indexes is rebuilt with a fillfactor of 100 and there are inserts that are not sequential for the indexed columns, or updates to indexed columns, or updates to varying length columns included in the index, then you'll get page splits. Most people are primarily concerned with page splits of the clustered index (because it's the biggest --> more pages --> more page splits), but they do in fact occur on all indexes.
January 13, 2016 at 3:27 am
I would say no.
Logical fragmentation affects the performance of large range scans from disk. Emphasis Large and Disk.
An insert is not going to require that SQL do a large range scan (unless it's got such a scan in the select portion). An update, unless it's updating a significant portion of the table, is also not going to cause a large scan.
In either case, if it's a frequently used table, the nonclustered index, being smaller than the cluster, may well be in memory already, at which point logical fragmentation has no effect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply