Test Running a Tuned SQL is Not An Easy Job

In SQL Optimizer for SQL Server, we have built a very sophisticated test run facility for users to find the best SQL alternative from all generated SQL alternatives to solve the user’s specific problem. But I have found that many users are just looking for SQL alternatives to improve their SQL elapsed time without fully understanding the test run function. Here are some examples to explain how various SQL test run methods solve different SQL performance problems:

• For SQL statements that consume a lot of CPU/IO time that is significantly affecting other SQL statements in a database, users should find SQL alternatives that reduce CPU/IO time instead of just looking for a lower elapsed time only.
• SQL statements that run very slow the first time in a day may be due to the data flushing from the cache so users should select the SQL alternative with the shortest non-cached execution time to improve the first execution time of the SQL.
• Online application queries that fetch the first few pages of records from a SQL statement and then abort the query without retrieving all rows from the SQL should select the SQL alternative with best performance of retrieving first N records to improve the online query specifically.
• For some high frequency and short execution time SQL statements, users should focus on selecting better SQL alternatives with steady performance as the average performance time from multiple executions is important to overall system performance.
• SQL statements with parallel execution may affect the performance of other SQL statements during peak hours so tuning for short execution elapsed time may not help to improve overall system performance. SQL tuning to reduce total elapsed time from all processors should relieve the impact.

There are still many other tuning purposes that require the test run method to be configured properly. In current versions of SQL Optimizer, we rely on the users experience to select the correct test run methods but some users may not have this specific knowledge of converting performance symptoms to test run methods. To help these users, we will provide a test run wizard in coming releases to help our users to configure test run methods correctly for their environment. With the following "USAGE AND SYMPTOMS" page, users will be able to input more information about the SQL performance symptoms and the SQL usage patterns, SQL Optimizer will help users to determine what test run setting is the best fit for the SQL statement. This new approach can help users quickly pinpoint the best solution for their specific environment. It also turns complicated SQL tuning scenarios into simplified questions without requiring the user to have SQL tuning knowledge.