Deletes And Indexes

Jeff Schwartz looks at the performance cost of indexes when it comes to deleting rows:

Many articles concerning SQL Server discuss how record insertion overhead increases with each additional index. They discuss b-tree manipulations and page splits in addition to leaf and non-leaf levels. However, few discuss the fact that deletion overhead increases as well, especially when large numbers of records are deleted by individual queries. Recently, I was working with a client who regularly purged large numbers of records from tables that ranged in size from large to gigantic. For example, one table contained over 6.5 billion records. I added an index (4th overall) to one table expressly for the purpose of expediting the large deletion process, and the deletion run ran longer, despite using the new index! To determine how the numbers of indices and records to be deleted interact, I conducted an experiment to test several combinations. The specifics of the tests and their corresponding results are summarized below.

Check it out.  There’s certainly more to the story than “add indexes to improve performance.”

Related Posts

Index Rebuilds Reset DMV Counters

Clive Strong notes that an index rebuild will reset certain DMV counters: As it transpires, an index rebuild will reset the counters for this index within the sys.dm_db_index_usage DMV and this is potentially very dangerous unless you are aware of this. Normally, we determine whether or not an index is in use by looking at […]

Read More

Resumable Online Index Rebuild

Arun Sirpal shows off a SQL Server 2017 and Azure SQL Database feature: Clearly I am in a paused state with 5.48% percent of the operation complete. In the original query window where I started the index rebuild, I receive: Msg 1219, Level 16, State 1, Line 4 Your session has been disconnected because of […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031