Database Reference
In-Depth Information
Optimizing and Tuning Indexes
System optimization and performance tuning is an iterative, never-ending process, especially in cases when a system
is in development. New features and functions often require you to re-evaluate and refactor the code and change the
indexes in the system.
While index tuning is an essential part of system optimization, it is hardly the only area on which you must focus.
There are plenty of other factors besides bad or missing indexes that can lead to suboptimal performance. You must
analyze the entire stack, which includes the hardware, operating system, SQL Server, and database configurations,
when troubleshooting the systems.
■
Note
We will talk about system troubleshooting in greater detail in Chapter 27, “system troubleshooting.”
Index tuning of existing systems may require a slightly different approach as compared to the development
of new systems. With new development, it often makes sense to postpone index tuning until later stages when
the database schema and queries are more or less finalized. That approach helps to avoid spending time on
optimizations, which become obsolete due to code refactoring. This is especially true in the case of agile development
environments, where such refactoring is routinely done at every iteration.
You should still create the minimally required set of the indexes at the very beginning of new development. This
includes primary key constraints and indexes and/or constraints to support uniqueness and referential integrity in the
system. However, all further index tuning can be postponed until the later development stages.
■
Note
We will talk about constraints in greater detail in Chapter 7, “Constraints.”
There are two
must have
elements during index tuning of new systems. First, the database should store enough
data, ideally with similar data distribution as that expected in production. Second, you should be able to simulate
workload, which helps to pinpoint the most common queries and inefficiencies in the system.
Optimization of existing systems requires a slightly different approach. Obviously, in some cases, you must fix
critical production issues, and there is no alternative but to add or adjust indexes quickly. However, as the general
rule, you should perform index analysis and consolidation, remove unused and inefficient indexes, and, sometimes,
refactor the queries before adding new indexes to the system. Let's look at all those steps in detail.
Detecting Unused and Inefficient Indexes
Indexes improve performance of read operations. The term
read
is a bit confusing in the database world, however.
Every DML query, such as
select
,
insert
,
update
,
delete
, or
merge
reads the data. For example, when you delete a
row from a table, SQL Server reads a handful of pages locating that row in every index.
■
Note
every database system, including the ones with highly volatile data, handles many more reads than writes.
At the same time, indexes introduce overhead during data modifications. Rows need to be inserted into or
deleted from every index. Columns must be updated in every index where they are present. Obviously, we want to
reduce such overhead and drop indexes that are not used very often.
