Database Reference
In-Depth Information
After you detect potentially redundant indexes, you should analyze all of them on a case-by-case basis. In some
cases, consolidation is trivial. For example, if a system has two indexes:
IDX1(LastName, FirstName) include
(Phone)
and
IDX2(LastName) include(DateOfBirth)
, you can consolidate them as
IDX3(LastName, FirstName)
include(DateOfBirth, Phone)
.
In the other cases, consolidation requires further analysis. For example, if a system has two indexes:
IDX1(OrderDate, WarehouseId)
and
IDX2(OrderDate, OrderStatus)
, you have three options. You can
consolidate it as
IDX3(OrderDate, WarehouseId) include(OrderStatus
) or as
IDX4(OrderDate, OrderStatus)
include(WarehouseId)
. Finally, you can leave both indexes in place. The decision primarily depends on the
selectivity of the leftmost column and index usage statistics.
■
Sys.dm_db_index_operation_stats
function provides information about index usage at the row level.
Moreover, it tracks the number of singleton lookups separately from range scans. it is beneficial to use that function when
analyzing index consolidation options.
Tip
Finally, you should remember that the goal of index consolidation is removing
redundant
and
unnecessary
indexes. While reducing index update overhead is important, it is safer to keep an
unnecessary
index rather than
dropping a
necessary
one. You should always err on the side of caution during this process.
Detecting Suboptimal Queries
There are plenty of ways to detect suboptimal queries using both standard SQL Server and third-party tools. There are
two main metrics to analyze while detecting suboptimal queries: number of I/O operations and CPU time of the query.
A large number of I/O operations is often a sign of suboptimal or missing indexes, especially in OLTP systems.
It also affects query CPU time—the more data that needs to be processed, the more CPU time that needs to be
consumed doing it. However, the opposite is not always true. There are plenty of factors besides I/O that can
contribute to high CPU time. The most common ones are multi-statement user-defined functions and calculations.
■
Note
We will discuss user-defined functions in more detail in Chapter 10, “Functions.”
SQL Profiler is, perhaps, the most commonly used tool to detect suboptimal queries. You can set up a SQL Trace
to capture a
SQL:Stmt Completed
event, and filter it by
Reads
,
CPU
, or
Duration
columns.
There is a difference between CPU time and Duration, however. The CPU column indicates how much CPU time
a query uses. The Duration column stores total query execution time. The CPU time could exceed duration in parallel
execution plans. High duration, on the other hand, does not necessarily indicate high CPU time, as blocking and I/O
latency affect the execution time of the query.
■
Do not use client-side traces with sQL profiler in a production environment due to the overhead it
introduces. use server-side traces instead.
Important
Starting with SQL Server 2008, you can use Extended Events instead of SQL Profiler. Extended events are more
flexible and introduce less overhead as compared to SQL Traces.
