An integral part of a SQL Server DBA or any DBA as a matter of fact is to keep the servers up and running with performance as it was when the whole database system was afresh. Now, we all know that’s a tough thing to do, because as time passes by the performance of the database system degrades and there can be multiple factors adding onto it. That is why it’s often said that the real job of a DBA begins when he/she has to maintain the performance of the database system. On this note, there are various methods one can use to maintain the database system across many technologies, but when we talk about SQL Server, we prefer the term maintenance plan. A maintenance plan in SQL Server is a group of tasks that are done sequentially in order to maintain the performance of the overall database system and improve them at times. The individual tasks in a SQL Server maintenance plan can be summarized as below:
- Check Database Integrity
- Shrink Database
- Rebuild Index
- Reorganize Index
- Update Statistics
- Execute SQL Server Agent Job
- History Cleanup
- Back Up Database – Full
- Back Up Database – Differential
- Back Up Database – Transaction Log
- Maintenance Cleanup Task
Now, all these individual tasks can be executed via a maintenance plan created in SQL Server, but the key thing to decide on, is the order of the tasks as well as the frequency. Here is a general guideline as to which task should be done in what order:
- A task such as cleanup history can be performed at any point in the plan but, for other tasks, there is a certain logical order.
- It makes sense to start the maintenance plan with the check database integrity task, because there is no point in running the rest of the plan if the integrity of our database is in question.
- The backup database – full should come before backup database – transaction log, as we can’t perform a transaction log backup before we perform a full database backup.
- If a rebuild index task or the reorganize index and update statistics is performed during the same maintenance window as the backup database – full task, then always perform the index maintenance task first. Why? This way, if its needed to perform a restore of the backup, it will have it’s indexes de-fragmented and will be ready for production
- The Maintenance cleanup task, if selected should be performed only after backup database – full has been completed. This way, we can ensure that a good backup has been made before deleting any older backups.
- If we choose separate schedules for each task, the scheduling of these tasks will determine the actual order in which they occur, and so the key thing to remember is that the logical task order we select is not absolute, and that it can be overridden by the schedules we set for each one.
If we were to write a code using the above guidelines it could look like as shown below:
if (Check Database Integrity succeeds)
Rebuild Index OR (Reorganize Index and Update Statistics)
Back Up Database (Full) or Back Up Database (Differential)
if (Back up Database (Full) succeeds)
Backup Database (Transaction Log)
Maintenance Cleanup Task
Shrink Database (never)
Run these anytime:
Execute SQL Server Agent Job
But what about the frequency of the tasks to be executed? Well, frankly, this will vary depending on individual needs, but here are some common guidelines for how often to schedule tasks:
- Check Database Integrity – DAILY (or at least WEEKLY)
- Rebuild Index WEEKLY (DAILY is better).
- Reorganize Index and Update Statistics WEEKLY (DAILY is better)
- Back Up Database (Full) WEEKLY
- Back Up Database (Differential) EVERYDAY EXCEPT FULL BACKUP DAY
- Backup Database (Transaction Log) – HOURLY
- Maintenance Cleanup Task – WEEKLY
- Shrink Database DO NOT SCHEDULE – ON DEMAND ONLY
- Execute SQL Server Agent Job – DEPENDS
- History Cleanup – WEEKLY (or other)
Now before using these general guidelines on any system, here are a couple of more things to keep in mind:
- Do not update statistics using maintenance plans. Allow SQL Server to update statistics as needed. If you notice performance degradation, identify large tables with numerous indexes and update statistics manually only on those indexes.
- Do not use maintenance plans for removing unused space in production databases. Doing so can generate substantial undue overhead on the server. Try removing unused space from production databases occasionally – perhaps once a month during scheduled maintenance windows.
- Use maintenance plans for checking database integrity on non-production servers.
- Do not use maintenance plans for running integrity checks in production databases. Instead, run DBCC CHECKDB and other integrity checks separately from database or transaction log backups. Attempting repairs found by DBCC CHECKDB puts the database in single-user mode, which is unacceptable during production hours.
- Use maintenance plans for rebuilding index in non-production environments
- Do no use maintenance plans for index maintenance in production databases. There are better ways to maintain indexes and the one I along with many industry leading experts use is a solution from Ola Hallengren. This is an all in one solution including backups, integrity check, index and statistics maintenance, and works across SQL Server 2005 to 2014.
To summarize, a database maintenance plan is critical for the best performance, but my preference is to not use the database maintenance plan wizard unless you really know what you are doing. It just doesn’t give you enough advice. And, if you just select the defaults, you will end up with a maintenance plan that is no optimal.
A better approach would be to create your own maintenance plan. If you write the code yourself then you can strategically target only the objects that have the warning sings and/or are out of date and you can set when to rebuild versus when to reorganize. But then again, all this coding has already been done by Ola Hallengren and you only need to configure the execution of the code as per your database environment and pick the schedule that is the most optimal for you using the guidelines that I provided earlier.
I hope this post serves you well in creating your own customized maintenance plan to increase the throughput of your SQL Server database. Please feel free to drop in comments regarding your approach towards database maintenance plan in SQL Servers.