SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Maintenance Plans are a Big Plus for SQL Server Administration


How Maintenance Plans are a Big Plus for SQL Server Administration

Author
Message
prettsons
prettsons
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 1391
Comments posted to this topic are about the item How Maintenance Plans are a Big Plus for SQL Server Administration

SQL Database Recovery Expert :-)
ronang
ronang
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 136
I like the article, thanks Prett. I would like to know though what are the drawbacks for using Maintenance Plans VS Scheduled sprocs.
Chris Taylor
Chris Taylor
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1917
I can imagine there'll be a few comments on this topic regarding why you shouldn't use maintenance plans, I'll make a start:

You may redesign indexes by specifying a new fill factor and contribute to rearranging the data stored on both the data pages and index pages. By providing a new fill factor for indexes, you can ensure that the database has proper organization of data and free space. This facilitates smooth and faster database growth


You have to be very careful in doing this blanket across the board, fillfactor should only be amended after you've done the relevant analysis and proved that by changing it you'll get a benefit. See Kendra Littles post on fillfactor.

You can update the statistics information for all indexes in your database in order to improve the performance of query optimizer. The query optimizer uses this information to speed up access to data. The index statistics information is updated on a routine basis automatically by the SQL Server, but this option is a must-have if you want the updates to occur immediately.


Why would you want to rebuild all indexes and statistics if not required? Unnecessary overhead on the system for little / no benefit. There's several other options out there including Ola Halengren's maintenance solution and Michelle Uffords defrag script, both of which will look to rebuild/defrag/update stats based on thresholds you set.

Don't get me wrong, everything has its place and in some environments (ie non-production) they can work as a quick n easy solution but if you're to post about something like maintenance plan's then I believe you should also explain the drawbacks.

_________________________________________________________________________________

SQLGeordie

Web:- Jarrin Consultancy
Blog:- www.chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
GilaMonster
GilaMonster
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117375 Visits: 45532
About the only thing I use Maint plans for is backups and CheckDB and even for backups they're not ideal as you can't set some options on the backups (like CheckSum).

They're a poor choice for index and stats maintenance when you have more than a trivial database, rebuilding everything and updating everything sounds great in practice but in reality there often isn't time to do that, plus it's a massive waste of resources. Generally a more targeted index and stats maintenance method should be used.

Good for a novice DBA just getting started

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ricardo.harneker
ricardo.harneker
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
;-)
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17214 Visits: 8600
I recommend to my clients to never EVER use Maintenance Plans for ANYTHING. All standard/routine database maintenance activities should be done using Ola.Hallengren.com's FREE, FULLY DOCUMENTED, INCREDIBLY POWERFUL/FLEXIBLE scripts!!! The only clients I have been to that did not switch to those already had full-featured home-grown scripts and jobs in place.

Oh, and this statement:

"You can shrug off empty database pages to compress your data files."

sounds to me like you are recommending shrinking database/tlog files with a maintenance plan. If so, PLEASE remove that from the article or add in a HUGE caveat about how bad that practice is!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Steve Jones
Steve Jones
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: Administrators
Points: 83569 Visits: 19223
Personally I like maintenance plans and being able to build some workflow into the maintenance. However, many of the tasks in maintenance plans I'd remove and use Execute T-SQL Tasks instead with custom scripts, like Ola's or SQLFool's index maintenance scripts.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Dave Pendleton
Dave Pendleton
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 168
Any DBA regularly shrinking a data file should be, um, "reassigned."
rstone
rstone
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 824
I agree with TheSQLGuru. Even if the maintenance task has improved, it will be a long time before I trust them again. They were once a pain to use and migrate. (It required a custom xml transformation the last time I tried to migrate a plan years ago.) I ended up using my own T-SQL maintenance job until I found Ola's work (http://ola.hallengren.com/). I've been using Ola's script for several years without any issues. There was another recent article using PowerShell to do maintenance. It can be good to consider (or reconsider) ways to improve on a process. However, sometimes, it's hard to improve on something that already works.

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
SQLSquire
SQLSquire
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
Well one thing I have noticed while working with live clients is that there is no way to conveniently reindex on a regular basis unless the database itself is frequently not in use. Currently we monitor backups and database size\growth manually via scripts.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search