Every nonclustered index incurs overheard when a table row is inserted or deleted, or when any of the nonclustered index key columns (or INCLUDEd columns) are updated. Filtered indexes in SQL 2008+ are a special case, obviously. The overheard takes a few forms:
- Buffer pool (i.e. memory and I/O) overhead of having to search the nonclustered index for the record to update.
- I/O overhead of having to flush the updated index page to disk during the next checkpoint
- Log space for the log records generated by the operation on the nonclustered index
- Resource overheard for those log records in terms of:
- Time to be read by the replication/CDC log reader Agent job
- Time to be read by log backups (and data backups, if applicable)
- Time and bandwidth to send the log records to a database mirroring mirror
- Disk space to store the log records in a log backup
- Time to restore the log records on a log shipping secondary or during a disaster recovery
- Locking overhead
- Page split overhead
- Time to consistency check
- Time to examine for fragmentation
- Time to update statistics
- Disk and backup space overhead
0 comments:
Post a Comment