Database Reference
In-Depth Information
Chapter 24
Database Performance Testing
Knowing how to identify performance issues and knowing how to fix them are great skills to have. The problem,
though, is that you need to be able to demonstrate that the improvements you make are real improvements. While you
can, and should, capture the performance metrics before and after you tune a query or add an index, the best way to
be sure you're looking at measurable improvement is to put the changes you make to work. Testing means more than
simply running a query a few times and then putting it into your production system with your fingers crossed. You
need to have a systematic way to validate performance improvements using the full panoply of queries that are run
against your system in a realistic manner. Introduced with the 2012 version, SQL Server provides such a mechanism
through its Distributed Replay tool.
Distributed Replay works with information generated from the SQL Profiler and the trace events created by it.
Trace events capture information in a somewhat similar fashion to the Extended Events tool, but trace events are an
older (and somewhat less capable) mechanism for capturing events within the system. Prior to the release of SQL
Server 2012, you could use SQL Server's Profiler tool to replay captured events using a server-side trace. This worked,
but the process was extremely limited. For example, the tool could be run only on a single machine, and it dealt with
the playback mechanism—a single-threaded process that ran in a serial fashion, rather than what happens in reality.
Microsoft has added the capability to run from multiple machines in a parallel fashion to SQL Server. Until Microsoft
makes a mechanism to use Distributed Replay through Extended Events output, you'll still be using the trace events
for some of your performance testing.
Distributed Replay is not a widely adopted tool. Most people just skip the idea of implementing repeatable tests
entirely. Others may go with some third-party tools that provide a little more functionality. I strongly recommend you
do some form of testing to ensure your tuning efforts are resulting in positive impact on your systems that you can
accurately measure.
This chapter covers the following topics:
The concepts of database testing
How to create a server-side trace
Using Distributed Replay for database testing
Database Performance Testing
The general approach to database performance and load testing is pretty simple. You need to capture the calls against
a production system under normal load and then be able to play that load over and over again against a test system.
This enables you to directly measure the changes in performance caused by changes to your code or structures.
Unfortunately, accomplishing this in the real world is not as simple as it sounds.
To start with, you can't simply capture the recording of queries. Instead, you must first ensure that you can restore
your production database to a moment in time on a test system. Specifically, you need to be able to restore to exactly
the point at which you start recording the transactions on the system because if you restore to any other point, you
 
Search WWH ::




Custom Search