Introduction to SQL Performance Analyzer

You can run SQL Performance Analyzer on a production system or a test system that closely resembles the production system. Testing a system change on a production system will impact the system's throughput because SQL Performance Analyzer must execute the SQL statements that you are testing. Any global changes made on the system to test the performance effect may also affect other users of the system. If the system change does not impact many sessions or SQL statements, then running SQL Performance Analyzer on the production system may be acceptable. However, for systemwide changes—such as a database upgrade—using a production system is not recommended. Instead, consider running SQL Performance Analyzer on a separate test system so that you can test the effects of the system change without affecting the production system. Using a test system also ensures that other workloads running on the production system will not affect the analysis performed by SQL Performance Analyzer. Running SQL Performance Analyzer on a test system is the recommended approach and the methodology described here. If you choose to run the SQL Performance Analyzer on the production system, then substitute the production system for the test system where applicable.

To analyze the SQL performance effect of system changes using SQL Performance Analyzer:

Capture the SQL workload that you intend to analyze and store it in a SQL tuning set, as described in "Capturing the SQL Workload".

If you plan to use a test system separate from your production system, then perform the following steps:

Set up the test system to match the production environment as closely as possible.
Transport the SQL tuning set to the test system.

For more information, see "Setting Up the Test System".

On the test system, create a SQL Performance Analyzer task, as described in "Creating a SQL Performance Analyzer Task".

Build the pre-change SQL trial by executing the SQL statements stored in the SQL tuning set, as described in "Measuring the Pre-Change SQL Performance"

Perform the system change, as described in "Making a System Change"

Build the post-change SQL trial by re-executing the SQL statements in the SQL tuning set on the post-change test system, as described in "Measuring the Post-Change SQL Performance"

Compare and analyze the pre-change and post-change versions of performance data, and generate a report to identify the SQL statements that have improved, remained unchanged, or regressed after the system change, as described in "Comparing Performance Measurements"

Tune any regressed SQL statements that are identified, as described in "Fixing Regressed SQL Statements".

Ensure that the performance of the tuned SQL statements is acceptable by repeating steps 6 through 8 until your performance goals are met.

For each comparison, you can use any previous SQL trial as the pre-change SQL trial and the current SQL trial as the post-change SQL trial. For example, you may want to compare the first SQL trial to the current SQL trial to assess the total change, or you can compare the most recent SQL trial to the current SQL trial to assess just the most recent change.

Introduction to SQL Performance Analyzer

Related Topics