Databases Reference
In-Depth Information
to consider the way users interact with the database to apply design adjustments based on data access
patterns. Common design decisions that may negatively affect database performance include poor
choice of keys, schema over-normalization, and missing or inappropriate indexes. Each of these can
be addressed in turn. More detail is provided in Chapter 8.
Indexes are important because they can significantly reduce the cost of query execution. The query
optimizer is responsible for evaluating execution plans and determining the lowest cost plan, which is
then cached in memory (so that it may be re-used by subsequent executions of queries with the same
structure). The cost of executing a query is an arbitrary value determined by cost for the resources
required to deliver query results. Indexes are important to the query optimizer because they reduce
the number of reads required to return a result set. Disk Input/Output (I/O) is almost always the
slowest task when returning results to an application or user. As a result the purpose of indexes is to
reduce the number of reads required to locate a particular row or set of rows.
Indexes are much like the contents page at the front of this topic or the alphabetical index at the end
of the topic. These indexes need to be maintained. For example, if a new section is added to this topic
or if content is changed or removed when revising editions, the indexes should be updated to reflect
these changes. In much the same way, indexes within a database should be maintained to ensure the
data referenced is still present and valid. You've probably guessed by now that there is some overhead
involved in maintaining indexes, particularly in environments where there is a high level of new data
inserted or changes to current data.
Ultimately there's a balance to achieve where indexes are useful and improve query execution
performance while maintenance overhead is manageable. Determining this balance up-front at design
time can be achieved if detailed knowledge of patterns of user activity is known. (A typical situation is
where an existing system is being replaced or upgraded.) However, in situations where you're working
on a brand new implementation, these should be regularly reviewed to ensure relevance and accuracy.
SQL Server 2005 has a great DMV, which allows you to evaluate index usefulness. This means that
evaluating index usefulness is significantly improved compared with SQL Server 2000, where deep
analysis of SQL Profiler traces was required to extract the same information!
SQL Server creates statistics automatically (if the default settings haven't been changed); these are used
when the query optimizer is evaluating index usefulness. Statistics model the shape of data by sampling
at pre-determined intervals throughout the entire data set. Statistics are then recorded with the data
and used by the query optimizer to estimate how many rows will be returned by executing various
components of a query. The query optimizer will iterate through different combinations of reading tables
directly and using indexes, once the optimum execution plan is identified. This will be executed, and the
execution plan will be held in cache in case it needs to be re-used by a query executing with the same
structure in the future.
Types of Bottlenecks
A bottleneck is any resource restricting database performance. The purpose of performance
troubleshooting is to identify any resource causing a bottleneck and resolve it. For example, with a server
running with 100 percent CPU utilization, you could say there was a CPU bottleneck.
Bottlenecks are many and various. Often performance troubleshooting is an iterative process whereby
a number of problems are identified and resolved before an optimal configuration is determined. You
should be working toward a database server that provides acceptable performance and is balanced in
Search WWH ::
Custom Search