August 16, 2012 at 11:47 am
I recently had to delete around 22 million rows out of 30 million rows in a table. How I went about it was to copy the 8 million rows I wanted to keep to a temporary table. Then I truncated the original table and copied the 8 million rows back to the original table. I ordered the insert statement by the primary key with clustered index.
The reason I did it this way was to try to keep the log file to a reasonable size. I also thought that reinserting the data ordered by the clustered index after a truncation would mean that the index fragmentation would be nice and low. Nice and clean implementation, right? The log file did stay nice and relatively small BUT the indexes (1 clustered and 6 non-clustered) on the table were all at 99.x% fragmentation when all was done. I checked and the statistics on the table had been updated as expected.
My question to you all is why didn't inserting into the table ordered by the clustered index field ensure that index fragmentation would be low? I ended up rebuilding the indexes and that worked fine but I didn't expect to have to do it.
thanks!
August 16, 2012 at 11:49 am
Was there a database shrink anywhere in the process? Autoshrink?
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
August 23, 2012 at 12:52 pm
I didn't manually shrink the database but Autoshrink is set to Yes on that database. I'm not sure what can trigger an Autoshrink to execute. Would it do it after deleting a large number of rows from a single table?
August 23, 2012 at 12:58 pm
Yes, it can. As you have seen a shrink process can destroy performance of your indexes due to fragmentation. If you must perform a shrink routine to reclaim disk space after a large delete you want to invoke those yourself and immediately rebuild fragmented indexes, preferably all during a maintenance window. The general consensus is to turn AUTO_SHRINK OFF.
Auto-shrink – turn it OFF! by Paul Randal
edit: spelling
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 1:02 pm
mlcote (8/23/2012)
I didn't manually shrink the database but Autoshrink is set to Yes on that database. I'm not sure what can trigger an Autoshrink to execute. Would it do it after deleting a large number of rows from a single table?
Yes. With autoshrink on SQL checks the DB on a regular basis to see if there's free space and if there is the shrink runs.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply