Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Robin Schumacher

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  


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter