August 2, 2011 at 5:01 am
I appreciate ReIndexing has been done to death, but I'm just wondering a few things.
I see most people suggest using a custom script to run re-indexing across their databases (i.e. http://sqlfool.com/2011/06/index-defrag-script-v4-1/) rather than using the Maintenance Plan wizard.
Where it gets messy is that there's so many of these type of scripts around, and it worries me in terms of which one(s) can be trusted, and what's an ideal setup for an environment.
Using the script above as an example, does this take care of all my re-indexing needs including stats, etc? How does it take care of the options that the Maintenance Plan wizard gives you like online index rebuilds, etc?
How do the rest of you handle this in a typical setup?
August 2, 2011 at 6:34 am
I use that partcular script in prod and it works just great.
I used to do stats too but there was a bug so it has been taken out for the time being and then she had a first child).
Here's my full story with that script (read the whole thread).
http://www.sqlservercentral.com/Forums/Topic1126147-146-1.aspx
August 2, 2011 at 6:44 am
PhilipC (8/2/2011)
I appreciate ReIndexing has been done to death, but I'm just wondering a few things.I see most people suggest using a custom script to run re-indexing across their databases (i.e. http://sqlfool.com/2011/06/index-defrag-script-v4-1/) rather than using the Maintenance Plan wizard.
Where it gets messy is that there's so many of these type of scripts around, and it worries me in terms of which one(s) can be trusted, and what's an ideal setup for an environment.
Using the script above as an example, does this take care of all my re-indexing needs including stats, etc? How does it take care of the options that the Maintenance Plan wizard gives you like online index rebuilds, etc?
How do the rest of you handle this in a typical setup?
Think on those scripts as suggestions to you - not as fully customized solutions for your particular needs.
As a rule of thumbs never use a script you cannot tell what it's doing. You, and only you know the exact requirements for your particular environment.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 6:50 am
That there is a beastly script. It does a whole lot more than just reorg/rebuild when needed. That script will create functions, tables, procedures and do your laundry all at once! That's not a complicated script, it just does a lot. You can use it, but before you run it you must understand it and what it is trying to do. It does not rebuild statistics and by default it does index rebuilds online.
I wrote my own script to handle index maintenance. I learned a lot online, asked lots of questions on this site and pieced my script together.
My script goes through all tables in every database. It reorganizes between 10 and 30 percent and rebuilds when fragmentation is over 30 percent. It does some logging and email notification but it is very simple.
August 2, 2011 at 6:51 am
Forgot part of the question.
Yes it does online indexing.
It does BETTER than maintenance plan because it reindex only what needs to be, not everything.
You can set delays between index operations and even an overall time limit which you can't do in MP.
This script is really leaps and bounds above whatever the MP can offer you.
August 2, 2011 at 6:58 am
calvo (8/2/2011)
That there is a beastly script. It does a whole lot more than just reorg/rebuild when needed. That script will create functions, tables, procedures and do your laundry all at once! That's not a complicated script, it just does a lot. You can use it, but before you run it you must understand it and what it is trying to do. It does not rebuild statistics and by default it does index rebuilds online.I wrote my own script to handle index maintenance. I learned a lot online, asked lots of questions on this site and pieced my script together.
My script goes through all tables in every database. It reorganizes between 10 and 30 percent and rebuilds when fragmentation is over 30 percent. It does some logging and email notification but it is very simple.
It also logs everything it does (including time and duration). That's what I used to change the FF and trace the progress of the DB.
As I said, very nice script. Not afraid at all to recommend it!
August 3, 2011 at 1:53 am
Thanks for your thoughts guys, much appreciated.
August 3, 2011 at 5:10 pm
Just wanted to add a couple of things to this. First, whether you build a script yourself or use one that someone else has provided - make sure you have a good understanding of what it is doing and how.
I've asked several developers and DBA's simple questions like: how does that procedure work..., or what is that code doing... When I get an answer like 'I don't know - it just does what I need', I shudder... In fact, if I have control over the system I then block the code from executing until it is well documented.
Also, note that it isn't the maintenance plans themselves that are the problem. It is the built-in tasks that can cause issues. For example, running the built-in task for integrity checks does not give you the option of performing a physical_only check on certain databases. Or, the update statistics task does not give you the option of only updating statistics on those stats that need to be updated.
When you need this additional capability, you have a couple of options. One, you can scrap the whole maintenance plan idea and script everything yourself (either procedures, or code in agent jobs, etc...). Or, you can just replace that built-in task with an Execute SQL Task and use your custom code there.
I prefer using maintenance plans - even if all tasks are Execute SQL Tasks running custom code. This is really just a personal preference for me...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2011 at 3:18 am
Thanks for your post Jeffrey, some well made points there.
I think after consideration I'm going to stick with MP within the environment I look after. The databases are quite small, largest being 30GB, and a full re-index, and checkdb, etc only take 10-15 minutes.
I tend to think options like this are great for large databases of perhaps a few hundred GB but I believe MP do the job ok for smaller dbs.
Feel free to disagree though 🙂
August 4, 2011 at 3:40 am
PhilipC (8/4/2011)
Thanks for your post Jeffrey, some well made points there.I think after consideration I'm going to stick with MP within the environment I look after. The databases are quite small, largest being 30GB, and a full re-index, and checkdb, etc only take 10-15 minutes.
I tend to think options like this are great for large databases of perhaps a few hundred GB but I believe MP do the job ok for smaller dbs.
Feel free to disagree though 🙂
We had this same plan in my previous company.
M&M
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply