Indexing Methodology
Throughout this book, I’ve discussed what indexes are, what they do, patterns for building them, and many other aspects for determining how a SQL Server database should be indexed. All that information is necessary for the last piece in indexing your databases, which is a methodology for managing indexes. To do this, you need a process for applying that knowledge to determine the indexes that are best for your environment and provide the greatest gain to performance.
In this last chapter, I’ll discuss a general practice that can be used to build an indexing methodology. You’ll look at the steps necessary to manage indexes. This methodology can be applied to a single database, a server, or your entire SQL Server environment. Regardless of the type of operations or business the database supports, you can use the same methodology for building indexes.
The Indexing Method
Before you can begin creating and dropping indexes, you first need a process to analyze current and potential indexes. This process needs to provide a way to observe your databases and determine the indexes that are appropriate for your databases. As mentioned in previous chapters, indexing should be more of a science than an art. The information needed to properly index a database is available; through some research, you can identify potential indexes. Similar to how scientists use the Scientific Method to prove theories, database administrators and developers can use the Indexing Method to prove what indexes a database requires.
The Indexing Method used in this book is comprised of three phases: Monitor, Analyze, and Implement (see Figure 15-1). Within each component are a number of steps that, when completed, help to provide the appropriate indexing for the database. At the completion of the Implement phase, the Indexing Method restarts the first phase, making indexing a continuous and iterative process.
Figure 15-1. Indexing Method cycle
When starting with the Monitor phase, the primary activity is to observe the indexes. The observations entail reviewing both the performance and the behavior of the indexes (that is, the indexing concepts described in Chapter 13). SQL Server will use the indexes that it finds most beneficial from those available. By observing this behavior, you can identify the indexes that are most often used and how they are used.
After the observations, the Analyze phase of the Indexing Method begins. In the Analyze phase, detailed in Chapter 14, the statistics collected in the previous phase are used to determine what indexes are best suited for the database. The goal is to identify what indexes need to be created, dropped, and modified. Along with this, the impacts of any indexing changes are also identified.
The last phase of the Indexing Method is the Implementation phase. In this phase, the indexes from the last phase are applied, or deployed, to the databases. For every database and environment, the deployment process may be different. For instance, the process for deploying indexes on third-party databases differs from applications owned by your company. Within this phase, though, there are core concepts that apply to all environments; outside of physically building the indexes, you will need to communicate the change plan and possible effects of the change. Then, you need to track the changes over time. There is more to implementing indexes than just executing a CREATE INDEX statement.
After the last phase completes, the Indexing Method begins again with the first phase. In this way, indexing is a continuous and iterative process. The indexes that provide the best performance today may not be the best indexes for tomorrow. Two events primarily contribute to the need for changing indexes over time. The first is data usage, where the functions and features of applications can change over time, so the purpose of the application can also change. Second, the data population and distribution can, and usually will, change over time. With these changes, indexes may shift out of usage, and other data access paths may be required. Data changes aren’t the only things that can cause index use to change; the optimization in a future SQL Server version or service pack may change how the optimizer uses indexes.
Now that the basics of the Indexing Method are covered, the remainder of this chapter will focus on the Implement portion. The concepts for the Monitor and Analyze phases are covered in Chapters 13 and 14, respectively. It’s important too that as you learn more about indexes, you will discover new patterns that can be used to identify indexes. As you learn more about indexing and your databases, you will find other ways to look at performance and usage statistics that provide more, or better informed, guidance. Use this book and the information you learn to continue to expand your indexing methodology.
Implement
The final phase of the Indexing Method is the Implement phase. This phase does as the name implies: it implements the indexing changes that were determined as necessary through the Analyze phase. There isn’t much to this phase from a process perspective, but there are some important steps that need to be done during the Implement phase that will help build out a successful process. The aim of the entire process is to improve the performance of the database environment. With this aim, there are three key points to remember during implementation.
While the last step is the only one where the database is modified, the other two help ensure that the changes will be noticed and that you can continue to use the Indexing Method in the future.
The first hurdle in modifying the indexes on any database is the need to communicate with management and users of the database your intent to change the database. Modifications to the database can often raise red flags, especially when they are being prescribed by nonowners of the application the database supports. Preparing for and implementing open lines of communication between the owners of applications and the database administration team will help not only in the indexing process but in other areas of mutual interest. Without this communication, teams can be blindsided by the indexing changes, which may impact something that the analysis did not uncover or a feature that is planned but not yet released.
When it comes to communication, there are basically two items that need to be prepared for the owners of the databases: an impact analysis of the indexing changes and a status report of the changes after implementation.
When preparing for changes to indexing on a database, it is important to highlight the intended changes to the application performance. Historically, this has often been a guessing game. There was not a lot of easily accessed information that would indicate where an index is being used, how it is being used, and the frequency of use.
With the processes laid out in the Monitor phase, you gain the ability to confidently know the use of an index. You can determine when it was last used and what operations were included. There is information that can also be used to identify the trend in which an index will no longer be used or is being used more frequently.
Through the Analyze phase, steps were laid out that allow the identification of execution plans that are utilizing different indexes. Use these steps to identify where an index change will have an impact and then perform sample executions of the T-SQL statements before and after the indexing changes are made.
In the end, the impact analysis will function in two important roles during the Implement phase. First, it will communicate to managers and peers the intent of the indexing changes, informing them of the changes to validate what is being done and allowing them the chance for feedback. Second, the impact analysis provides an insurance policy in case an index change has an unexpected negative impact. This isn’t to say that there won’t be negative repercussions to poor indexing recommendations, but with others involved and the impact documented, it is more likely that a negative impact can be mitigated quicker and possibly identified before actual implementation.
On the opposite end of the Implement phase is the status report. As the name implies, the status report is a document that provides feedback to managers and peers about the actual impact of indexes. This document does not need to be very deep, but it does need to cover some key points. The status report should cover the following information:
- All index changes made
- Status on deployment of changes
- Brief performance review
- Information on any regressions noted
- What was learned in the deployment process
- Summary of issues encountered
Don’t get too mired in the details while writing the status report. If all goes well, there will be additional Monitor and Analyze phases in the near future. In the end, the status report needs to communicate two things. First, it provides an honest assessment of the successes and failures in the indexing deployment. Second, and most importantly, it lists what benefits are now being realized by the indexing changes. This is most important because it is the ROI that managers need to see to be able to justify the time and effort spent on indexing.
The primary deliverable from the Analyze phase is a list of index changes that are planned for the databases in your environment. During the Implement phase, those indexes need to be reviewed and prepared for deployment. As part of preparing the indexes for deployment, three steps need to occur.
- Prepare the deployment and rollback of the schema.
- Save index changes to source code control.
- Share results of peer review with impact analysis.
Prepare Deployment and Rollback of Schema
Usually, at the completion of the Analyze phase, you have a list of the index changes that are being proposed. This list typically is not in a state that can be used for deployment at the end of the phase. Between that point and the execution of the changes, the indexing changes need to be put into a state that can be used for their deployment.
When building the deployment scripts, be sure to observe the idea of “doing no harm” to the database. In other words, you need to build scripts that are intelligent enough that they can be executed multiple times with the identical results. Also, this means that scripts should be available to reverse any indexing changes being made. Never assume that the previous indexing state of a table is being stored in source code control. Check to be certain that the existing state is known and develop scripts to revert to that state if needed.
The deployment scripts also need to be aware of the edition of SQL Server that is being used. For instance, if you are using Enterprise Edition, leverage online index rebuilds for indexes that are being rebuilt with new characteristics. If appropriate for the index, Enterprise Edition also allows for compression on the index, which can save space and improve performance in many cases.
Save Index Changes to Source Code Repository
As mentioned, the current state of the indexes on tables should be in a source code repository. If they are not, then with this iteration through the Implement phase, it’s time to do so. Source code repositories offer a place to store the code, or schema, for a database to allow your organization to determine what the index, table, or store procedure schema was at a specific date and time. Source code is often well managed from an application perspective. Developers are usually quick to choose a tool and leverage it for their applications.
Source code repositories allow you to recover to a point in time for the database schema.
Peer Review with Impact Analysis
The last thing to do before the Execution step is to seek a peer review of the indexing changes. There is nothing worse than working in a vacuum and not understanding the whole impact of the changes that are being proposed against the applications that use the databases. It is easy to get tunnel vision by focusing on the indexing goal and miss the business goals of the current deployment or overlook something that wasn’t apparent in the index analysis.
The best way to avoid these pitfalls is to find a peer to review the indexing changes. Bring the peer the index deployment scripts and the impact analysis and go over the changes. Your peer doesn’t necessarily need to know everything about the environment, just a basic understanding of indexing. The aim of the peer review is to explain each change. In this dialogue, your peer serves as a sounding board as you explain the indexing need. This serves a dual role. First, your peer will be able to provide feedback on the indexing change. Second, by discussing the changes, you may hear yourself describe an indexing change that doesn’t sound correct when it is explained.
In some environments, you may not have a peer that you can turn to review the indexes. In these cases, consider going to your manager for the peer review. If that is not possible, talk to your manager about leveraging peers in your technical network. Leverage the forums and social networks to find either a peer or group of peers that will be willing to review your changes with you. Using social networks, such as Twitter, to connect with a technical peer and review some indexing changes is much better than not having a peer review at all.
With the peer review complete, the indexes are ready for the next step in the Implement phase: the step where the indexes are actually applied to the databases.
Note Within the SQL Server community, Twitter is one of the more active social networking tools. Use hash tags #sql and #sqlserver to find general information on SQL Server. When looking for answers to questions specifically about SQL Server, you can utilize the hash tag #sqlhelp. Twitter also allows you to add people to your conversation by including their Twitter handle in the tweet. For instance, the authors of this book, Jason Strate and Grant Fritchey, are available through the handles @stratesql and @gfritchey, respectively.
The last piece of the Implement phase is the execution of the T-SQL scripts that will apply the indexing changes to the database. These scripts should already be prepared through the Deploy Scripts step, and the scope of the changes should be well known from the Communication step. Thus, the Execution step should be relatively painless as the preparation work is already completed.
From an execution standpoint, the manner of execution is completely dependent on your organization’s change control process. In some environments, there are automated processes where scripts can be loaded to a deployment mechanism and executed on a schedule. In others, the DBAs simply open SQL Server Management Studio and execute each script until all the changes are completed. Whatever the mechanism, the key is that at this stage the indexes get deployed.
As the deployment progresses, be sure to catalog the changes made and any issues that arise during execution. Pay attention to unintended blocking on the databases. If indexes are being deployed in an offline state, be sure to select an execution window that is during the database maintenance window. Remember, even online index operations can cause short-lived blocking.
At the beginning of this chapter, the discussion started by looking at the three phases of the Indexing Method. The diagram for the process (Figure 15-1) shows the three phases in an endless loop, with each phase leading to the next. This choice in layout was intentional. Indexing is not a fixed-point activity. Once the first round of the Indexing Method is completed, it is important to start the next round of indexing.
It can be tempting, when databases are properly tuned, to let the practice of indexing slip and to focus on other priorities. Unfortunately, new features are often added to applications as frequently as new data is added to the database. Both of these events will change the way in which indexes are used by the database and the effectiveness of the current state of “good” indexes.
To maintain the desired performance of the database platform, indexes must be continuously reviewed. This isn’t to say that a full-time resource always needs to be assigned to monitoring, analyzing, and implementing indexes. There does, though, need to be an acceptance that at some interval an evaluation of the state of indexing will be completed.
Summary
As this chapter showed, the Indexing Method is quite similar to the Scientific Method. Within your database platform, statistics can be collected on indexes in order to identify where indexing issues may exist. These statistics can then be further utilized to determine the types of indexes to modify and where. Indexing tools such as the Database Engine Tuning Advisor and missing index DMOs can be leveraged to discover “the low-hanging fruit,” giving you a head start on analysis that you may not have discovered otherwise. By following the phases laid out in the Indexing Method, you can build a stable, repeatable indexing process that can help improve the performance of your database platform and achieve stable performance over time.