You can view a SQL Performance Analyzer comparison report, which compares the performance of SQL statements before and after the change.

Before you view SQL Performance Analyzer comparison reports, you must ensure that you've created a SQL Performance Analyzer task. SQL Performance Analyzer measures the impact of system changes both on the overall execution time of the SQL workload and on the response time of every individual SQL statement in the workload. By default, SQL Performance Analyzer uses Elapsed time as a metric for comparison. Alternatively, you can choose a metric for comparison from the following SQL run-time statistics when creating the SQL Performance Analyzer task:

• CPU time
• Buffer gets
• Disk reads
• Disk writes
• Physical I/Os

If you chose to generate explain plans only in the SQL trials, then SQL Performance Analyzer will generate the plans using existing optimizer statistics. For information on how to:

• Create a SQL Performance Analyzer Task, see Create a SQL Performance Analyzer Task.
• Create a trial, see Create a Trial.

To view a SQL Performance Analyzer comparison report in Database Management SQL Performance Watch, go to the SQL Performance Watch home page for the database, click the name of a task and in the Comparisons section, expand the referenced trial, and click Comparison Report.

On the SQL Performance Analyzer report page, you can:

• View details of the SQL Performance Analyzer task, trials, name of the execution, name and owner of the SQL tuning set, and the number of SQL statements analyzed.
• View a summary of the workload impact on the chosen comparison metrics.
• View a breakdown of the comparison metric value before and after the change, and the number of SQL statements by performance, changes to the SQL execution plan, and problems. In the SQL statements by performance, SQL statements by plan change, and SQL statements by problems charts, you can hover the mouse over the bars to view the number of SQL statements in that category. For example, in the SQL statements by problems chart, if an Errors bar is displayed, click the bar to view the number of SQL statements with errors.
• View the top SQL statements by workload impact. The impact is determined by the difference (in percentage) of their comparison metrics to the total value of the comparison metrics of all the analyzed SQL statements in the pre-change trial. In the Top SQL statements by workload impact section, you can:
  • Filter the SQL statements by Category or search for SQL statement by text or ID.
  • Click the SQL ID to view the SQL Performance Analyzer result for the SQL statement. This section displays the SQL text, an analysis of the comparison metrics and findings, if any, and detailed information on the variation in the SQL execution plans.
  • Click Save report to save the comparison report in SQL Performance Watch.