Defining Performance Expectations - Chapter 10

What Can You Expect
The first problem in tuning a SQL Server is to understand what level
of performance you can expect. If it ain't broke, don't fix it.
You need some effective measures to help you understand whether the
performance you're getting makes sense, given the query and the data.
When you tune performance, you should apply your time and effort to
problems that demand your attention, and to those that you can fix.

Focusing Tuning Efforts Where It Counts
Let's start by understanding basic physical limitations. The weak link
in the performance chain is often the physical disk drive. Today's
fast-caching RAID arrays can deliver about 2MB of data per second to
SQL Server, sometimes more. With multiple I/O channels and read-ahead
threads on a multi-processor NT server, you can look for two to three
times that volume of data. So a front-to-back scan (a table scan) of a
100MB table could run in less than a minute with a fast drive subsystem.
For many users, however, that's simply too long to wait for an answer.
Several techniques can improve that performance. Even faster, larger
drives would deliver the data faster to cache; an enormous data cache
would improve the chance that some portion of the table would be available
to you in memory when you ran the query; the DBCC PINTABLE command could
force the table to persist in data cache once it was read (although this
is probably not a good idea with large tables).
Another method might be to add CPUs and count on read-ahead threads to
improve scan times. Depending on your disk configuration and the amount
of contention on the system, this strategy may reduce scan time by a
factor of 40 percent or more.
Like all hardware-based, brute force techniques, these approaches have
fairly limited effectiveness, and their cost may become prohibitive.
The solution to this problem may be to deliver the same result while
reading less data. That may involve storing derived or duplicate
values, or changing the basic design of the table to reduce its size.
Adding a nonclustered index may also lead to useful performance
improvements.
In any event, what your users perceives as a problem does allow some
improvement and may be worth addressing.
So, when you choose where to focus your tuning efforts, there are two
key issues:
1. Where are the problems that need to be solved?
2. Which problems are the result of physical limitations?
It's crucial to understand the performance capabilities of SQL Server,
and to establish an understanding of how your hardware and software
configuration relates to the finite limitations of the system.
Based on that, you can develop reasonable expectations and capabilities
as well.
The following sections help you develop proper performance expectations
and focus your tuning activities:

Defining Performance
There are three basic ways to define SQL Server performance:
1. Response time for queries. How much time elapses when running a
specific query? Many organizations demand subsecond response time for
simple queries. Of course, more complex queries take longer than a second.
Comparing required response time to actual response time is an important
measure of performance.
2. Throughput. This is typically measured in transactions per second,
or TPS. A variety of industry-standard benchmarks can help you to compare
the performance of database servers running on various platforms, but they
won't help you define or predict the actual performance of the system, because
they don't reflect your own transactions.
Carry out your performance benchmarking with your own queries and data to get
meaningful throughput measures. You need to make sure that your database
and server are up to the challenge of managing the number of queries in
the amount of time you need.
3. Concurrency. This standard can reasonably be considered a subset of
throughput. Here's the basic question: Can your system handle 5,000 users?
Answering this question is usually a substantial task that requires you to
profile predicted query and throughput traffic, and then to answer the response
time and throughput questions under load.
4. Combination of throughput and concurrency. Can this system handle online
transaction processing (OLTP), decision support systems (DSS, also called data
warehousing or EIS-Executive Information Systems), and batch processing
(management and offline report systems) at one time?
This is the hardest thing to tune for, particularly on single-processor SQL
Servers.

Tradeoffs
Tuning for a single definition of performance is reasonably simple. For example,
to tune for fast response time to queries is usually simple if throughput and
concurrency don't matter.
Approaches that solve single performance include
1. Normalization (eliminating duplicate data) - can speed throughput
2. Denormalization (storing duplicate data) - can speed retrieval time
3. Creating or dropping indexes
4. Partitioning tables across databases
5. Adding hardware or reconfiguring software
Where solving a single problem is fairly straightforward, finding a way to solve
multiple performance needs is usually a delicate balance act. For example, you
may choose to speed throughput by removing indexes used to improve query
performance. Modifications to tables are much faster without the additional
indexes, so transactions require less work and the system can handle more
transactions per second. The tradeoff is that queries previously supported
by an index now might need to perform a table scan. A query that used to take
three minutes may now take several hours!

Common Tradeoffs
You need to consider some common tradeoffs when you're looking at performance
issues. None of these tradeoffs is simple or has a standard response. As you
evaluate your performance options, it's vital that you be able to state clearly
what you're getting and what you're giving up.

Update Performance versus Read Performance
Most database administrators (DBAs) develop database designs using a two-step
approach that starts with a logical design and then moves to a physical design.
A logical database design is a representation of data intended to remove all
duplication and to express clearly the relationship between data elements.
The physical design is a plan for how to store the data on a particular system.
One of the first hurdles to overcome in physical design is understanding that
it has a separate purpose from logical design. Logical design is for
understanding and describing data; physical design is for performance.
The first tradeoff is update performance versus select performance. A normalized
database allows updates to run fast, but it also requires more joins to resolve
multi-entity queries, and joins are costly. If you denormalize, you reduce joins;
thus many queries run faster.

Storage Efficiency versus Cost
This brings up the next tradeoff: storage efficiency versus cost. Denormalizing
frequently demands more storage because it requires the storage of duplicate
data. and that isn't free; however, it's often cheap compared to the cost of
unresolved performance issues.

Fast Execution versus Ad Hoc Access
Balancing OLTP and DSS is a specialized problem. OLTP applications typically
support a fixed set of data operations, most of them update operations, allowing
you to predict the appropriate data design and index selections. Small, lean
tables and few indexes provide the best performance in those circumstances.
DSS applications are based on ad hoc queries where users ask questions you can
never anticipate. Wide tables and lots of indexes help a DSS applications,
where retrieval time matters much more than the time to maintain the data.
Before you can address your performance issues, you need to define your
requirements and boundaries. You need to provide a physical design to enable
the response times that you want to achieve.

Expectations
Let's take a look at some specific queries and try to identify the
expectations. These queries are based on a table and index defined as
follows:
create table orders (...
item_num int,
warehouse int,
...)
Create index ord_index on orders (item_num, warehouse)
Here are some potential queries you might run against the table, with their
response times:
/* QUERY A: response time subsecond */
select sum(qty) from orders
where item_num = 1234 and warehouse = 432
/* QUERY B: response time 600 seconds */
select sum(qty) from orders
where warehouse = 432
/* QUERY C: response time 50 seconds */
select sum(qty) from orders
where item_num in (1234,2345) and warehouse = 432
Now let's pose two important, distinct questions:
1. Which queries have acceptable response times? (Of course, your
answer to this question depends on your users' demands.)
2. Which have expected response times?
Query A: Is the response time acceptable? In subsecond, is yes.
Is the response time expected? If you have a reasonable amount
of data for the test, look for real problems elsewhere.
Query B has response time that is unacceptable for most real-time operations.
To understand this we need to understand the physical design, and how the
server uses the physical design. Because the table is indexed on item_num,
and item_num isn't in the where clause, the server can't use the index.
Therefore, the only way to resolve the query is with a table
scan, which is the process of reading every page in the table.
Next comes understanding the data: How many pages of data do we have to
read to resolve the query? It also opens up a hardware question: How many
pages of data can SQL Server read in a second? If the data isn't in cache,
query performance depends on physical drive performance. Depending on the
server hardware, SQL Server can read between 200 and 1,000
pages (400 KB to 2MB) per second (typically) from SCSI disk drive.
Back to Query B. How many pages of data must we read? (SQL Server reads
one page at a time.) If the amount of data that needs to be read takes
several hundred seconds (for example, the table is 800 MB), and you're
willing to allow a few seconds for overhead, 600 seconds might be an
expected result-even though it isn't an acceptable one. During your
physical design phase, you need to identify the fact that you have
a potential problem query, with expected results that are unacceptable,
and you need to figure out a way to improve performance (this can be
an additional index or one of many other possible decisions).

Bottlenecks
A bottleneck is a resource that limits system throughput. A typical
bottleneck is a physical disk drive. You may need to get data from
the disk drive a bit faster and be limited by the speed of data retrieval
from the drive. But is this the real problem? Is the problem the disk
drive, the controller, or the operating system?
Unfortunately, eliminating a bottleneck has the net effect of shifting
the bottleneck to some other limiting resource. With any luck, though,
the new bottleneck will be wider.
It's essential to understand where bottlenecks can occur, where they
tend to occur, and under what circumstances. Potential bottlenecks
are your performance variables-those things that you adjust, tune,
and balance to get the best possible results for your application.

Performance Variables
A number of components can be bottlenecks. Some of these are based on
configuration options that can be set; others are built in and
unchangeable.

Physical Architecture
Physical (hardware) architecture specifically relates to these components:
1. CPU configuration
CPU performance is an important element in performance, but seldom the
bottleneck. Can you improve performance by reducing dependence on CPU
performance? The best approach is to make your code more efficient by
simplifying SQL and reducing cursors.
Can you make better use of multiple CPUs? By tuning configuration
options to improve the efficiency of read-ahead caching (RA
configuration options), you may be able to improve multi-processing
performance for a single query.
2. Disk I/O performance
You can't change the I/O rate for a physical device, but you can
reconfigure the physical design by introducing a RAID device or
splitting up data across multiple disks. You can also reduce the
amount of requested data. (For example, limit the number of rows
to be retrieved from the database. Will a user really need all
4,000,000 rows? Usually not.)
3. Network
The network is an unreliable component. In many systems, network
performance is very fast, but with increasing dependence on intranets,
more and more users are running client/server applications across a WAN.
For most applications, you should regard the network as a bottleneck:
make a point of reducing network traffic whenever and wherever possible.
You can do this by reducing the number of packets transmitted
or by increasing the packet size. You can also use stored procedures.
4. Concurrency
Concurrency issues are not tunable, but can sometimes be contained. For
example, if logging overhead is causing problems, you can try committing
transactions less frequently or putting the log on a higher-speed device.
Additionally, if sequential reads are slower than you would expect, you
may be able to redistribute the data on your disks. Overall server
architecture also affects concurrency. For example, additional memory
often helps the server handle greater numbers of users more efficiently.

Application
Application components relevant to database performance include three
critical elements:
1. Query
The most important issue from an application-tuning standpoint is to
make sure that your user writes queries properly. Substantial user
training is critical, particularly where ad hoc queries are involved,
because horrid SQL is probably the number one cause of bad performance.
Bad queries can have a variety of flavors: unnecessary joins,
insufficient joins, lack of search arguments, or an inability to
take advantage of server features (for example, the update in place).
2. Logical design
Logical design issues may involve changing table normalization to
reduce joins, or data partitioning to take infrequently referenced
data out of the scanned table.
3. Physical design
Physical design is often the first place to start working on performance problems.
Correct index selection can often fix - that is, improve the performance of-
otherwise problematic queries. Adding indexes tends to help queries but hurts
update performance. You can also store summary or redundant data.
Cursors are a favorite way programmers can foul up performance, particularly
concurrency. A poorly written cursor can lock huge amounts of data.

Server
Server performance relates to these elements:
1. Configuration
2. Optimizer
3. Lock Management
4. Concurrency
5. Maintenance activity
6. Dump/load
7. Index creation
8. Batch activity
9. Concurrency management
The database server is the most tunable component of your environment. For
example, you can configure and reconfigure memory, cache, locks, disk-resource
dissemination, connections, and dozens of other things. There are also some
components you can't easily tune.
One thing you can't change is the optimizer. The optimizer chooses its own
path, join order, and/or other search tactic.
Note: You can, for a particular query or session, force a join order or
index selection on the optimizer. See Chapter 13.
Most lock management is handled automatically by the server; the maximum
number of overall locks is configured by the administrator. The SQL Server
manages the use of the locks. You can improve performance by keeping
transactions short and by avoiding manual locking behavior. Also avoid
situations where deadlocking becomes likely.
Server overhead can sometimes be handled with hardware, such as using a
solid-state device for the log to increase transaction throughput where
the log has been positively identified as a bottleneck.

Concurrency
You probably will want to identify a batch window for performing (among other
things) maintenance activity such as dbcc and database dumps, index creations
and re-creations, or other batch activity (reporting, mailing labels, long-
running ad hoc queries). All of these activities tend to hog an entire
processor. It's also the type of activity that can lock tables or databases,
which reduces OLTP concurrency.

Tuning Your Approach
Before beginning to tune, gather as much information as possible about the
circumstances surrounding the perceived performance problem. Remember that
you can't tune for everything. You need to identify and prioritize problems
before addressing them. For example, you have a problem query that runs
for 40 hours. You can fix the problem by denormalizing your database and
adding four indexes to the tables. The cost of this solution is six
additional disk drives and three hours added to your batch(dump) window.
Ask yourself whether it's worth the cost. Are you fixing this query for the
CEO of the company, who needs this report updated every two hours, or is this
query being run every six months by an associate accountant? Sometimes the
decisions are easy.
If you're tuning an existing system, choose options that are transparent
to users (for example, indexes and segments) over those that require changes
to programming (like changing table structure).
Estimate your requirements prior to the final rollout. Find or build a
tool that simulates user activity, and act on the information you acquire.
It's rare for users or application designers to estimate data requirements
adequately, but you should make sure that you can handle at a minimum the
stated system requirements.
When you have specific problems, follow these steps:
1. Identify baseline time for your CPU and controllers so that you can
state your expected results.
2. Decide whether the results are expected
3. Examine the problem query. Is it too complex? Does the query solve
the user's actual need?
4. Are the indexes appropriately selected in the physical design? Is
the optimizer using the indexes you think it should?
5. Is the optimizer selecting the correct approach?
6. When in doubt, break down the query. Do individual components take
too long?
7. Finally, prioritize the problem. Does the situation, user, or
application warrant physical-design changes or other work on your
part?