April 18, 2010 at 6:31 pm
I use sys.dm_db_index_physical_stats to determine the extent of index fragmentation when I'm focusing on a single table. But as the fragmentaiton can change daily, and we have lots of databases and table, I let the nightly maintenance job "routinely" reindex all databases.
While there are shortcomgings to the approach, I don't know of another practical one that makes sure all indexes are rebuilt. Do you?
TIA,
Barkingdog
P.S. The same issue also applies to updating statistics also.
April 19, 2010 at 2:48 am
There is a script in BOL to rebuild/reorganize indexes.
April 19, 2010 at 12:33 pm
Quoting @BrentO's reply to my similar question to him:
"If you're using the maintenance plans built into SQL Server, then they're rebuilding every index every time, whether it needs it or not, and that'll cause problems. The easiest workaround is to use Michelle Ufford's excellent defrag scripts, which will operate based on thresholds. You get to define how fragmented indexes will be before they're reorg'ed or rebuilt, so you can only defrag pieces of the database. Here's the link:
http://sqlfool.com/2009/06/index-defrag-script-v30/
"
We changed our nightly maintenance plans to run the proc created by these scripts with thresholds we were comfortable with as opposed to the canned maintenance plans and have not only had zero problems, our Execution time went from hours to minutes*.
Hope this helps!
*Your results may vary 🙂
April 19, 2010 at 1:39 pm
I'm not sure it will cause problems if you have the time and resources to do it, but there's no need for rebuilding all indexes.
Michelle's script, Hot linked: http://sqlfool.com/2009/06/index-defrag-script-v30/, works very well. It checks each table before rebuilding indexes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply