Do Not Forget to Maintain Your Indexes

Comments 1

Share to social media

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.

Identify and Remove Index Fragmentation

The easiest way to remove index fragmentation is to defragment all of your database’s indexes every day. If you have a small database, and you have defined maintenance times, this might be a viable option. On the other hand, many databases are relatively large and must be available 24×7. In these cases, it is often not practical to defragment every index every day. Instead, you want to have the ability to identify only those indexes that really need to be defragmented, and focus on those.

In addition, SQL Server offers several different ways to perform index defragmentation. Each option had its pros and cons, and you need to know which option works best for your environment.

Identify Skewed and Outdated Index and Column Statistics and Ensure they are Representative and Up-to-Date

An important component of index is the statistics that accompany them. Index statistics are used by the query optimizer to help it determine if, and when, an index should be used when executing a query. If these statistics are skewed, or out-of-date, then the query optimizer might not make the best choice when selecting the indexes used to perform the requested action.

Another complicating factor of maintaining indexes is that the choices of how you choose to remove index fragmentation affects how you maintain index statistics. There are many pros and cons.

You may also notice that I have included column statistics as part of the index maintenance plan. Although column statistics are not, technically-speaking, directly related to indexes, they are important to the query optimizer so it can make good decisions. In addition, when you maintain index statistics, you automatically maintain columns statistics at the same time.

Identify and Create Missing Indexes

There are essentially three ways to approach index optimization. The first is to leave it to automation, using tools such as the SQL Server Database Engine Tuning Wizard to help you select missing indexes based on Profiler traces you have collected. The second is to hand tune indexes based on your analysis of the execution plans of critical queries. The third option is to use a combination of the first two steps.

Automation is the best option to start with, because you can produce a huge amount of gain for a minimal time investment by automating index optimization. Because most DBAs don’t have a lot of extra time on their hands, this may be their only option for index optimization. And even if you have time, why not take advantage of a tool that can help you identify many missing indexes with minimal effort? While I refer to this process as automation, it is not really 100% automated. Some of the steps of this maintenance task still should be done by hand, and DBAs also need to make a judgment about the indexes the Database Engine Tuning Wizard recommends before you blindly let it create new indexes for your databases.

If you have the time, and the necessary experience, then handing tuning indexes can be very beneficial, especially for very critical queries. How to hand tune queries is a large subject beyond the scope of this article,

Identify and Remove Unused Indexes

Unused indexes add unnecessary overhead to SQL Server because they have to be modified every time data is modified in a table. What’s the point of using up resources modifying indexes that never will be used? Unused indexes should be removed from your databases. SQL Server 2005 and SQL Server 2008 make it easy to identify unused indexes in a database using DMVs. Essentially, these DMVs track every time an index is used, which means you can easily determine which indexes can be safely removed. While in theory you could entirely automate this maintenance task, I prefer to do it manually because there are often some circumstances that automation can’t easily take into account.

Creating and Monitoring Index Maintenance Jobs

This is the step where you create the scripts necessary to perform automated index maintenance, and then schedule them with the SQL Server agent. In addition, you will want configure your SQL Server to notify you if any of these jobs fail, so you can ensure that index maintenance is performed as you expect.

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.

About the author

Brad McGehee

See Profile

Brad M. McGehee is a MCITP, MCSE+I, MCSD, and MCT (former), and, until recently, the Director of DBA Education for Red Gate Software. He is now the editor of the SQL Server Central Stairway series. Brad is also an accomplished Microsoft SQL Server MVP, with over 16 years SQL Server experience and over 7 years training experience. Brad is a frequent speaker at User Groups and industry events (including SQL PASS, SQL Server Connections, devLINK, SQLBits, SQL Saturdays, TechFests and Code Camps), where he shares his 16 years of cumulative knowledge and experience. A well-respected name in SQL Server literature, Brad is the author or co-author of more than 15 technical books (freely available on SQLServerCentral) and over 275 published articles.

Brad McGehee's contributions