If you aren’t an SQL Server guru, you are not restricted to the out of the box implementation for maintaining your database. There are certain features that make sense to do periodically instead of maintaining it real time. For example, as we talked about a couple days ago, you can configure your database to automatically maintain statistics. If you have this feature turned on, each time you modify data in a table, the statistics are maintained at the same time. This means it takes just a little bit longer to make the change to the table, while SQL Server finishes updating the statistics relating to that table. Yes, keeping the statistics up to date is important. However, unless you alter the data in a table drastically throughout the day, having statistics that are a little bit old will probably increase performance since they don’t need to be maintained each time a record is inserted or changed.
When you have a DBA on staff that knows what to do, they will probably set up automated scheduled tasks to maintain things like statistics overnight, or weekly, depending on the activity of the database. Most people using an SQL Server database won’t do this additional step and instead keep the auto update happening. There is a middle road you can follow.
SQL Server has a wizard to assist you in creating scheduled maintenance tasks that will do a number of things for you on a scheduled basis instead of managing it real time. Using this tool you can setup the needed plans, and you can turn off the automated managing of different features. Just a note, I tend to leave auto create statistics turned on.
Using the maintenance plan wizard you can do a number of maintenance tasks:
- Backup Database
- Check Database Integrity
- Update Statistics
- Rebuild Indexes
- Reorganize Indexes
- Along with other less important features
What is nice about the wizard is that it will create maintenance tasks and scheduled jobs which you can then modify at a later time if you like. The neat part about these tasks is that they actually generate the things to be done at execution time. So, for example, if you are updating statistics on columns or indexes, it will dynamically build the update
statements when the task is run, rather than having hard coded what needs to be accomplished. This is great because it will automatically include new tables, columns and/or indexes that are created after the maintenance task is created.
Maurice Pelchat has created another tool for managing databases you may want to consider. He writes:
Full automated maintenance meeting all the requirements our stated can be easy. I wrote and maintained for years a SQL script tool that does just that. It can be downloaded and used freely from YourSqlDba.Codeplex.Com. On the site, there is an online documentation that gives an overview of what it covers and how.
So, now you have a couple of ways to change how you manage the optimization of database objects, allowing you to schedule the maintenance in off hours, rather than performing the optimization at critical times of database usage.
Cheers,
Ben