May 24, 2011 at 10:12 am
I am using sqlfool's defrag script. However, it is painfully slow just on the data gathering stage no matter how much you filter.
This is for a large server and with lots of tables/indexes in several databases. Anybody have any recommendations for some good scripts out there?
If you can steer me to the right direction, that would be great...
May 24, 2011 at 10:17 am
This site & tools are well regarded:
May 24, 2011 at 10:25 am
I use this one and works perfectly for me :
http://sqlfool.com/2010/04/index-defrag-script-v4-0
What scan / sample mode are you using?
May 24, 2011 at 10:35 am
Whisper9999 (5/24/2011)
I am using sqlfool's defrag script. However, it is painfully slow just on the data gathering stage no matter how much you filter.This is for a large server and with lots of tables/indexes in several databases. Anybody have any recommendations for some good scripts out there?
If you can steer me to the right direction, that would be great...
Which version of that script are you using?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 10:49 am
I'm using a pretty old version. I will try the latest version to see if it runs faster. Plus, now it has an exclusion table. So maybe if I exclude all the smaller tables it will run faster.
Is that why you're asking?
May 24, 2011 at 10:50 am
Yes. It was based on knowledge that the newest version had been optimized several times over.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 11:03 am
Whisper9999 (5/24/2011)
I'm using a pretty old version. I will try the latest version to see if it runs faster. Plus, now it has an exclusion table. So maybe if I exclude all the smaller tables it will run faster.Is that why you're asking?
Actually you need to make sure you don't reindex the same darn indexes every day. I've brought down the average tlog backup from 7-8 GB / day to less than 200 MB. The whole process now runs in less than 5 minutes with 30 000+ indexes in the system
What I did is reindex by default instead of reorg (takes longer to refragment and reindex is faster than reorg anyways I could this this because I'm not 24 / 7 and I could lock the tables). I use minimum 1000 pages and 10% before kicking the reindex.
Now the other big gain here was to check the logs daily and spot which indexes were getting worked more than ONCE weekly. I changed the fill factor on those from 100 to 80%.
Keep scanning on those and if 80 is not enough keep going down. The total cost in space from doing so was about 5% of the DB.
But now the overall application runs faster and the reindex jobs are basically non-existent now. Even with full sample I run it daily well under 10 minutes.
May 24, 2011 at 11:46 am
Okay, I'll try it. I also tried using 'sampled' instead of 'limited' per another suggestion but that hasn't seemed to have helped. And this is where the script is bogging down, by the way, and not on the defrags itself.
May 24, 2011 at 11:47 am
Ninja's_RGR'us (5/24/2011)
Whisper9999 (5/24/2011)
I'm using a pretty old version. I will try the latest version to see if it runs faster. Plus, now it has an exclusion table. So maybe if I exclude all the smaller tables it will run faster.Is that why you're asking?
Actually you need to make sure you don't reindex the same darn indexes every day. I've brought down the average tlog backup from 7-8 GB / day to less than 200 MB. The whole process now runs in less than 5 minutes with 30 000+ indexes in the system
What I did is reindex by default instead of reorg (takes longer to refragment and reindex is faster than reorg anyways I could this this because I'm not 24 / 7 and I could lock the tables). I use minimum 1000 pages and 10% before kicking the reindex.
Now the other big gain here was to check the logs daily and spot which indexes were getting worked more than ONCE weekly. I changed the fill factor on those from 100 to 80%.
Keep scanning on those and if 80 is not enough keep going down. The total cost in space from doing so was about 5% of the DB.
But now the overall application runs faster and the reindex jobs are basically non-existent now. Even with full sample I run it daily well under 10 minutes.
Thx for the tips, although we're 25X8X366. The problem tho really isn't with the defrag/reindex part, but rather with the stats pull from the DMV.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply