B2B: Maintenance Plans
This post is part of the challenge by Tim Ford (b|t) #EntryLevelBlogChallenge. Which of course is a personal challenge to technical bloggers to write one entry-level blog post a month over the next year and tag it on twitter with #iwanttoshare
All of by #EntryLevelChallenge post are tagged under “Back-2-Basics” category.
Today, I would like to talk about Maintenance Plans. This SQL feature is designed to allow DBA to automate maintenance tasks. Maintenance plans can be found under the “Management” folder in SSMS once you log into SQL Server. Right Click on the “Maintenance Plans” folder and select “New Maintenance Plan”
The quickest and easiest way to set this up is to use the “Maintenance Plan Wizard”. This is a step-by-step, check-box tool to setup everything possible with a database. The First Step allows you to name your maintenance plan and allows you to create the schedule you want to plan to execute on. This can either be one schedule for all jobs or individual schedules for each task. I would recommend individuals schedules because of the maintenance tasks can interfere with other tasks.
The next step to the wizard is where you actually pick which tasks you would like to run. Each task is given a brief description on the bottom in yellow.
- Check Database Integrity – uses the DBCC CHECKDB command to check the integrity of the database
- Shrink Database – AVOID USING THIS AT ALL COSTS. Shrinking databases on a regularly schedule actually does more harm than good to a database. If you are running out of drive space and shrinking databases must be done, please do it manually.
- Reorganzie Index – This task will defragment and compact your indexes on tables and views. There can be a performance gain, but the down side to this task is it is an “all-in” mentality. It will perform the task on an index whether it needs it or not.
- Rebuild Index – this task actually complete wipes index out and rebuild them so they are 100% new again. This task is also “all-in” approach and can be very I/O intensive.
- Update Statistics – statistics are the back-bone of the how the SQL Engine finds data. The SQL Engine uses statistics to determine the distribution of data in the data files. If statistics are out of date SQL may have to work harder to find data.
- Clean Up History – the execution and results of SQL Maintenance plans are stored in the MSDB system database. If this database is not kept in check it is feasible that it can grow uncontrollably.
- Execute SQL Agent Job – if you have built your own SQL Agent Job to do maintenance then you can actually execute it during this plan.
- Back Up Database (Full) – this does exactly what it says. This will perform a SQL Server FULL backup of the databases. This should be done on all databases including system databases.
- Back Up Database (Differential)—Differential backups are taken using this tasks. This can only be used for databases in FULL or BULKED LOGGED recovery model.
- Back Up Database (Transaction Log) – transaction log backups are crucial to “point-in-time” recovery of your database. It can only be used against databases in FULL or BULKED LOGGED recovery model.
- Maintenance Cleanup Task – this task should always be selected because it will cleanup any files left over by the execution of the plan.
The next step of the Wizard allows you to set the order of execution for the individual tasks you have selected. I prefer to always do Database Integrity Checks first, then indexes, then backups.
Finally you get to select which databases you would like this maintenance plan will execute against.
Depending on what tasks you select, there maybe follow up secondary tasks that you need to complete. Where to store reports, what files to clean up, etc. And then, click FINISH!
The Maintenance Plan Wizard is a simple way to setup maintenance plan. But do not fall into the trap of the “click-and-go” configuration. This is where you click all the check boxes and just keep clicking NEXT.
Please follow up and learn what each setup does and determine if you really need all the tasks. Many experienced DBAs will use custom scripts and custom maintenance plans for each type of database to ensure the SQL Engine does as little work as possible. The less time your SQL Server does performing maintenance tasks, the more time and resources it has to perform data tasks.