December 11, 2015 at 5:29 pm
Is rebuilding Indexes the best alternative after archiving a lot of data or is there a better alternative?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2015 at 10:18 pm
Really depends on the before and after sys.dm_db_index_physical_stats, plus unit testing. Plans may become inappropriate if stats are not updated, so I would update them, even if the increase in fragmentation was minimal. But if fragmentation is high enough (or impactful enough) to warrant a reindex, that reindex will also update the stats (no need to do both).
December 12, 2015 at 9:18 am
After removing lots of data, yeah, I'd probably do index rebuilds. That will also rebuild the statistics, so you don't need to worry about them. Just remember that fragmented indexes primarily affect scans, not seeks, so if you're worried about performance, it depends on if you're seeing one or the other.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2015 at 5:44 pm
Welsh Corgi (12/11/2015)
Is rebuilding Indexes the best alternative after archiving a lot of data or is there a better alternative?
If the archiving is being done based on a date column, then yes, there is a much better alternative. Unfortunately, I don't believe you'll agree because you've already rejected partitioning as a solution on another thread. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply