May 1, 2012 at 6:11 am
I am going to do data purging in my databases.
I guess it will have around 20% of data will be removed.
After that, what thing I need to do?
1. re-indexing
2. update statistics
3. shrink the data files.
Please advice what are the best practices after data purge
May 1, 2012 at 6:50 am
As far as I remember when you rebuild the index the statistics are getting updated, so there is no reason to update the statistics again. If you just reorganize the index, then updating the statistics can be beneficial.
If you don't have a problem with disk space, I wouldn't shrink the database. If you shrink the database you'll cause fragmentation again. Gail Show (GilaMonster) wrote a very nice article with a demo that shows it at her site - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/
May 2, 2012 at 8:38 am
I agree. All you need to do is Rebuild the indexes. Don't shrink the .mdf unless you really need to gain the space back. REMEMBER, when you shrink the .mdf you are causing fragmentation within the database so you cause more harm than good.
May 3, 2012 at 10:51 am
Depending on whether or not you have a large enough maintenance window reindex is OK (remember reindex locks out the users from the table !). However if concurrency and uptime are your goal I would suggest the following:
- defragment non-clustered indexes
- defragment clustered indexes
- update statistics with full scan
- mark the tables for recompile
The real benefit of defragment over reindex is concurrency since only 1-2 pages or 1 extent is ever locked at any given point in time. Also, if you have enough server horsepower and your I/O subsystem can handle the load, you can run multiple sessions of defragmentation simultaneously !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply