Maintenance Plans and Software Consultants
Last week, we shared part one of our internal lunch and learn series led by our software consultants covering SQL Server tuning. This week’s session covered best practices for improving SQL performance. I’d like to specifically focus on maintenance plans and how utilizing them results in a better performing database.
What is a maintenance plan, anyway?
Maintenance plans are workflows of tasks, which will make sure your database is optimized, is regularly backed up and is free of inconsistencies. You can create these maintenance plans manually or via the Maintenance Plan Wizard. Execution can be at scheduled intervals or on demand.
A maintenance plan can have multiple sub-plans, which can each run on different schedules. That means a single plan can do something like backup the database every day, but only rebuild the indexes once a week.
Two of the main issues you are looking to solve with maintenance is fragmentation of your indexes and outdated internal SQL server statistics. Both of these lead to inefficiencies, like excessive disk reads ultimately slowing down the speed of your server.
How do I decide what maintenance plan is right for my project?
There’s no single maintenance plan we can use across all our projects. Each project is different! Some databases will have indexes that get fragmented faster than others. Some databases can’t be brought down for maintenance, so all tasks need to be run online. That means each project’s maintenance plan will be unique. That being said, here are some general guidelines to follow:
- The Check Database Integrity task should be done nightly if possible, but at a minimum, once a week.
- The Rebuild Index task should run weekly, at a minimum.
- The Reorganize Index and Update Statistics tasks are almost always paired together and are used as a lightweight alternative to the Rebuild Indexes task (for systems that don’t allow downtime).
- The Shrink Database task should never be used. Books about Maintenance Plans purposefully leave out how to use this task to try to dissuade people from using it.
The built-in SSMS Maintenance Plans are great start for adding scheduled maintenance to your SQL Server, but they do have their limitations. One of the main complaints is that they blindly rebuild indexes regardless of fragmentation level. For databases that can’t be brought offline, this can create an unnecessary slowdown in performance since the system is rebuilding indexes when they don’t really need it.
The alternative often recommended is a free, award winning tool called “SQL Server Maintenance Solution” by Ola Hallengren. This tool is highly customizable and can do things like only rebuild indexes when their fragmentation is above x%.
Why should I care?
Without regular maintenance, your SQL server will gradually become less efficient. This performance degradation could easily be misattributed to a host of other concerns like server hardware, network bandwidth, etc. Additionally, regularly running maintenance plans can help keep your data backed up and give you an early warning of potential integrity issues, which can lead to data corruption. These plans are simple to set up and easy way for your company to save time and money.
Next week we’ll cover other tools you can use to monitor the performance of SQL Server.
Check out this post, for more on how Entrance’s software consultants have used SQL to help clients work more efficiently…