| |
 |
|
SQL Server Tips by Burleson |
Addressing Showstopper
Problems with Performance Testing
How can performance testing be used to bring
database showstopper problems to light? A good beginning is to
follow these general guidelines:
-
Testing Space Outages: These problems
typically rear their heads in dynamic OLTP environments in which
data is constantly being added and changed. However, data
warehouses can encounter space issues as well during periodic
warehouse updates and refreshes. Performance testing can
simulate database Data Manipulation Language (DML) activities
such as INSERT, UPDATE and DELETE by repetitively executing SQL
statements or stored procedures that either add data in a
general fashion to one or more database objects or follow a more
transactional process and add data throughout related database
objects. For the latter, stored procedure executions work best.
-
Testing the Reaching of Resource Limits:
Certain resource limits can be tested merely by simulating the
logging on of hundreds of user accounts (database
process/session limits, etc.) Other limit tests require
challenges similar to having those hundreds of sessions issue a
query at the same time.
-
Testing Lock Contention: The task of
simulating potential locking problems can be easy or difficult
depending on the underlying application design. The best way to
accomplish this is to have simulated sessions repetitively
executing database stored procedure logic that performs
transactional work (DML activity). Addressing Performance
Draining Problems with Performance Testing
What about properly testing performance inefficiencies that can
only appear with a sufficient load being introduced to the
database? How about problems that take time to develop? There
are practical ways to carry out these tests such as:
-
Testing I/O and File Contention: This
can only be done well when the expected numbers of concurrently
connected sessions are brought against the database and those
sessions are performing repetitive examples of transactional or
query work. Duration may or may not play a role in flushing out
problems in this area.
-
Testing CPU and Memory Exhaustion: As
with testing I/O, a full user load performing real work will
tell the tale. Sometimes, however, just creating sessions on the
database will bring memory problems to light as each session
consumes memory even when idle. Other specific database-related
memory metrics, such as cache hit ratios, etc., can only be
evaluated through repetitive and consistent pressure from query
activity.
-
Testing Database Object Fragmentation:
This process can be greatly accelerated by imposing a DML load
on the database over a short duration of time that is heavier
than the system would normally take weeks or months to
accomplish. The goal is to determine if wasted space, row
chaining/forwarding, extended index depths, etc., results from
heavy INSERT, UPDATE, and DELETE activity.
-
Testing SQL Query Combination Traffic:
This task is somewhat more difficult in that a baseline of
various SQL queries on a standalone basis needs to be acquired.
Those baselines must then be compared against their per
execution measures when they are introduced to a database in
combination.
-
Testing Production Data Volume: Loading
a database with a representative data volume can be difficult if
there is not a current system to pull it from. There are,
however, data generators on the market that can help with this
task.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
