Let’s imagine for a moment. A third-party vendor has sold your company a new application that uses SQL Server as its back-end database. It’s a total solution that includes hardware, their customized application software, and a license for SQL Server. In fact, a consultant comes on site and does the entire setup and nobody at your company has to lift a finger. This is great, as your company’s IT department is already too busy keeping the network up and running, and you don’t have a DBA on staff.

At first, everyone is impressed with how the application is boosting the productivity of its users and how fast it performs. Over time, as more data is stored in the database, and as more and more users utilize the application, something happens. Users begin complaining that the application is getting slower, and some users report having to wait 10-, 15-, even 30-seconds or for a screen to appear. Reports that once took less than a minute to run are now taking half an hour to run. Users are getting frustrated, and the once poplar application is now impeding the productivity of the entire company.

A manager finally gets fed up enough with the application’s poor performance, so she calls the vendor’s support line, asking for help. She is told that SQL Server has a scalability problem and that the company will have to purchase larger hardware in order to boost performance. This sounds like a reasonable answer, but before she spends the money on new hardware, she talks to the IT manager, and he recommends that the company hire an SQL Server consultant to check out the server before they purchase new hardware. A second opinion can’t hurt.

The IT manager brings in a SQL Server consultant, who spends the better part of the day reviewing the SQL Server’s configuration, running reports, taking Profiler and Performance Monitor traces, and more. When she is done, the SQL Server consultant presents the results of her review, as follows:

• Every SQL Server and database setting is default.
• The SQL Server has no maintenance plans of any kind.
• The original database, which was only 50 MB originally, has now expanded to just over 3.9 GB.
• The fill factor on all existing indexes was set to 0 (the default value) for all indexes.
• Index fragmentation on most of the indexes exceeded 90% and higher.
• The top 10 worst-performing queries are all performing table or index scans to execute, either because some of the tables had no indexes at all, or because of missing indexes. It appears that if a table had an index at all, it was on the primary key only.
• The Performance Monitor shows that disk I/O is a major bottleneck, with CPU and memory a close second and third.
• And so on….

Based on the SQL Server consultant’s findings, she recommended that she be allowed to perform some simple maintenance tasks and to see what the effect of these would have on performance.  She said that it was generally best to start on the obvious problems first, and if resolving them didn’t solve the performance problems, then she would try some more advanced options. Some of her “simple” recommendations included:

• Perform a 24-hour Profiler trace, and then use the Index Tuning Wizard to analyze the results and recommend missing indexes. The recommended indexes would then be added.
• Rebuild all of the indexes, using appropriate fill factors. In some cases, different fill factors would be used on different tables.
• Create a customized index maintenance plan that would run on the server on a periodic basis, ensuring that those indexes that tended to become fragmented were defragmented, and also ensuring that all index and column statistics were current and complete.
• And so on….

The IT Manager gave the consultant the OK to perform the basic steps she had recommended. Over the next couple of days, during available maintenance windows, she implemented them. On the day after all of her recommendations had been implemented, the users were amazed at how fast the application was running. It was performing about as fast as it did when it was first installed.

The IT manager congratulated the SQL Server consultant on a job well done, and commented that she must surely be an exceptional DBA, as she was able to speed up the application without having to purchase new hardware.

The consultant was very honest, and said she didn’t do anything exotic to fix the performance problems. She explained to the IT manager that the performance problem largely stemmed from a lack of proper database maintenance, specifically index maintenance. All she did was to perform the necessary maintenance to get the application back up to speed, and in addition, she created a database maintenance plan that would prevent much of this from happening again. She also explained to the IT Manager that not every maintenance task can be done automatically, and proceeded to describe these to him, so he could perform them as needed.

While the above example is a little contrived (in order to keep it short), it is actually a realistic portrayal of the sad state of many SQL Server databases in the real world. There are hundreds of thousands of databases throughout the world that get little or no maintenance. And in many cases, this lack of maintenance directly results in poor SQL Server performance.

There is a common myth among many IT professionals that SQL Server maintains itself. This is not true. In fact, there is an entire set of tasks that should be performed on SQL Servers periodically in order to ensure that they not only perform at their optimum performance, but also that they have the highest availability possible.

Why Do Indexes Require Maintenance?

If you create a read-only table, such as a listing of all the states in a country, this list will almost never change, and because of this, indexes on the table don’t need any maintenance (assuming the table already has the appropriate indexes). It is only tables that change over time that require index maintenance. Unfortunately, this is likely to be a significant portion of all the indexes in your databases. For example:

• As data is inserted, updated, and deleted from tables, indexes can become fragmented over time, which can increase the amount of disk I/O that is required to retrieve data.
• In addition, as data is modified, the distribution of the data often changes over time. This can affect how the query optimizer uses indexes when creating optimal execution plans.
• A side effect of changing data is changing index and column statistics. In order for the query optimizer to be able to make the optimal choice when creating an executing plan, it needs index and column statistics that are representative of the data and that are up-to-date.
• Over time, it not uncommon for new queries to be run against your data. It is very likely that your current indexing scheme may not best meet the index needs of these new queries, and new indexes may be needed.
• In other cases, indexes may no longer be used, and if this is the case, then they should be removed to reduce the overhead of maintaining them.

In other words, most databases are not static. Data changes over time, and the ways the data is used by users change over time. In order to optimize the performance of your database’s indexes, they also need to change to keep in step, and this is what high performance index maintenance is all about.

Won’t the SQL Server Maintenance Plan Wizard Perform Index Maintenance for Me?

In theory, if you start up the SQL Server Maintenance Plan Wizard, and you answer all of the questions correctly, much of the index maintenance I discuss in this article can automatically be set up for you. Unfortunately, the Maintenance Plan Wizard has a lot of drawbacks. For example:

• While the Maintenance Plan Wizard can be used to remove fragmentation from indexes and update index and column statistics, it cannot be used to help you identify missing or unused indexes. If you skip these important maintenance tasks because they are not available from the Wizard, you miss out on a lot of potential performance benefits.
• The Maintenance Plan Wizard is harder to use than it appears. It gives the illusion that if you answer the questions it asks, that all database maintenance will properly be done for you. But that is not always true. For example, the Wizard doesn’t explain the pros and cons of using different types of index defragmentation options, which means you have to guess which option is best. There is no guidance provided. Nor does the Wizard ensure that you choose all the appropriate index maintenance tasks needed by your databases. There is no assistance provided to tell you which options you should select, based on your database’s needs.
• The Maintenance Plan Wizard forces you to defragment all indexes, whether they need defragmentation or not. In the real world, it is often much better to pick and choose which indexes need maintenance, rather than blindly defragmenting everything.
• The Maintenance Plan Wizard allows you to perform redundant maintenance tasks that will consume more of your SQL Server resources that necessary. In other words, it allows you to make poor choices that can hurt your server’s performance.
• Because the Maintenance Plan Wizard is a generic tool, it doesn’t offer you the many options that are available to you create the maintenance plan yourself. Many of these options can directly affect the performance of your server.
• Once you create a plan using the Wizard, it is hard to modify after the fact, and troubleshooting problems is more difficult than it needs to be.

Because of these drawbacks, I suggest you skip the Maintenance Plan Wizard and create your own index maintenance tasks that you can schedule to run using the SQL Server Agent.

What are the Key Index Maintenance Tasks?

Up until this point, I have talked about a variety of different index maintenance tasks that should be performed on a periodic basis.  let’s take a look at them at a high level. They include:

• Identify and remove index fragmentation
• Identify skewed and outdated index and column statistics and ensure they are representative and up-to-date
• Identify and create missing indexes
• Identify and remove unused indexes
• Creating and monitoring index maintenance jobs

Let’s take a brief look at each one of these, one at a time.

With Index Maintenance, Everything Counts

When you study index maintenance tasks, you may occasionally ask yourself this question, “This task I am supposed to perform doesn’t seem like it will help a whole lot on its own, so why bother?” What is really interesting about database maintenance in general, and index maintenance in particular, is that lots of little things quickly add up to a huge thing.

For example, adding a single index to a table might only benefit a particular query by boosting its performance by a measly 5%. Or removing the fragmentation from a single table might reduce disk I/O by 2%. Individually, these improvements may seem insignificant. What is critical to understand is that boosting performance a little bit in a lot of places quickly adds up to a large performance boost. In addition, sometimes simple changes, like adding a missing index to a table, might speed up a query by 400%. On other words, sometimes you get small changes, sometimes you get big changes, but in any event, when you add up all the improvements in performance that result from index maintenance, the overall benefit can often be large.

Is Index Maintenance the Only Maintenance Tasks DBAs Need to Perform Periodically?

SQL Server is very complex, and while it is important to properly maintain your indexes, it is just a small part of the tasks the DBA needs to perform. There are many other maintenance tasks that you need to perform besides index maintenance, including, but not limited to:

• Database and Log File Management
• tempdb Maintenance
• msdb Maintenance
• Data Corruption Detection
• Database and Log File Protection
• Job Monitoring
• Performance Monitoring

Index maintenance, and the maintenance tasks above, all must be performed on a regular basis to ensure that your SQL Server not only performs optimally, but are also as highly available as possible.

Summary

In this introductory article, we have learned that index maintenance is a defined set of tasks that DBAs need to perform on a periodic basis in order to ensure that their indexes are working optimally. We also learned that it includes many steps, including identifying and removing index fragmentation, identifying outdated index and column statistics and updating them, identify and creating missing indexes, identifying and removing unused indexes, and creating and monitoring index maintenance jobs.