You can configure SPA Quick Check to validate the potential impact of a system change to the database workload before you implement the change in the production environment.

SPA Quick Check is a diagnostic tool that helps avoid performance regressions. It helps predict how a proposed change, such as a database parameter change, gathering pending optimizer statistics, or implementing SQL profiles, will affect the execution plans and the performance of SQL statements. SPA Quick Check uses a SQL tuning set and executes the SQL statements in a controlled environment, both before and after the simulated system change. It then compares the performance of the SQL statements in the before and after scenarios and generates a report, which provides information regarding the improved, regressed, and unchanged SQL statements. In addition, it also provides recommendations to mitigate the performance degradation of regressed SQL statements.

SPA Quick Check in SQL Performance Watch is integrated with Database Management Diagnostics & Management, and you can configure SPA Quick Check settings and validate the impact of editing session-modifiable initialization parameters in Diagnostics & Management. For information on how to validate using SPA Quick Check when editing database parameters, see Edit Database Parameters.

Before you access Configure SPA Quick Check in SQL Performance Watch, you must ensure that the Advanced diagnostics preferred credential is set. With the Advanced diagnostics preferred credential, you can view the default configuration settings for SPA Quick Check and make changes, if required. For information on the Advanced diagnostics preferred credential, see Set Advanced Diagnostics Preferred Credential.

To access Configure SPA Quick Check in SQL Performance Watch:

1. Go to the SQL Performance Watch home page for the database.
2. On the left pane under Resources, click Configure SPA Quick Check.
3. Review or make changes to the SPA Quick Check configuration settings:
   1. SQL tuning set: Select the SQL tuning set that contains the SQL statements you want to analyze for performance changes.
   2. Trial mode: Select an option to execute and analyze the SQL workload:
      • Test execute: Runs the SQL statements in a test environment without modifying the data. It simulates the execution to gather performance metrics and generate recommendations.
      • Execute plan: Generates an execution plan for each SQL statement, providing insights into how the database optimizer processes the query.
      • Hybrid: Combines both Test execute and Execute plan, offering a comprehensive analysis by running the SQL statements and also providing the execution plans.
   3. Per SQL time limit (seconds): Specify the time limit for SQL statement execution.
   4. Execute full DML: Select Yes to execute DML statement fully, including acquiring row locks and modifying rows.
   5. Workload impact threshold (%): Enter a value to indicate the threshold (percentage) of a SQL statement change impact on a workload.
   6. SQL impact threshold (%): Enter a value to indicate the threshold (percentage) of a change impact on a SQL statement.
   7. Disable multiple executions: Select Yes to execute each SQL statement in the SQL tuning set only once.
   8. Comparison metric: Select the metrics that you want to use for the comparison analysis.
   9. Use Resource Consumer Group: Select Yes to specify the Resource Consumer Group to be used for the analysis.
4. Click Save.