Databases Reference
In-Depth Information
During the DS2 simulation, we captured all the database events in a workload. The DS2 database resided
on a SQL Server 2000 instance. We started a SQL 2005 Profiler trace on a remote server to connect to the
DS2 database. There were 185,768 trace events collected.
In addition to the SQL Profiler's TSQL_Replay template definition, we added the following columns:
CPU, Reads, Writes, and Duration. We configured the workload definition to capture the user login
named DS2, and then saved the trace output data into the file
DS2_40Threads_20Min_3SecThink.trc
.
SQL Profiler provides options to replay a trace in either a single thread or multiple threads. In the follow-
ing scenarios, for simplicity and clarity, we used a single-threaded execution.
Scenario 1: ValidatingPerformance Improvement
After establishing a workload that represents a specific scenario for the current environment, the point
is then to reuse this workload for specific performance tuning in a different environment. Performance
gains can be measured based on the whole workload.
In this scenario, we will use index tuning with the workload we have collected. First, we will replay the
workload and record the total time it takes in the testing environment. Then, we will try to find oppor-
tunities that will enhance performance through index tuning. At the end, we will replay the workload
again to compare and confirm any gain.
Having generated the workload, we use the following steps to complete this scenario:
1.
Replay the workload on a developer's desktop PC and record this replay time to use as a
benchmark reference.
2.
Perform index tuning exercises by using SQL DTA, to make a tuning change.
3.
Replay the workload again on the same developer's desktop PC to confirm performance
gain.
Figure 10-17 shows the results of Step 1. The replay time was 26 minutes, 29 seconds, and 650 ms (1,589
seconds). This replay time will be used as a base reference.
It is important to repeat each step at least three times to confirm you are getting reproducible results and
are not seeing the results of a cold versus warn cache. It is wise to let SQL Server automatically update
statistics. (Example: exec sp_dboption 'ds2', ' auto update statistics', 'true')
In Step 2, we used Database Tuning Adviser (DTA) to validate indexes in the DS2 database by feeding
each of the DS2 stored procedures into the tool. Figure 10-18 shows that the DTA recommended a new
index for the PRODUCTS table. DTA estimated a 98 percent improvement from which the stored pro-
cedure BROWSE_BY_CATEGORY will benefit. The question is how this improvement will affect the
overall workload.
In this scenario, we used Profiler replay as a validation tool to confirm the performance improvement.
After adding the new index recommended by DTA to the PRODUCTS table, we replayed the workload
again (see Figure 10-19).
Search WWH ::
Custom Search