Adaptive Server version 15.7 includes the sp_opt_querystats system procedure, which allows you to analyze the query plan generated by the Adaptive Server optimizer and the factors that influenced its choice of a query plan.
This analysis helps determine if elements in the query or the execution environment affect how Adaptive Server executes the query and its performance. You need not run the selected query to perform the analysis.
sp_opt_querystats provides this information:
- The query plan generated by showplan
- Enabled traceflags and switches
- I/O activity for the query generated by set statistics io
- Missing statistics found for any of the tables involved in the query
- The estimated plan cost calculated by the optimizer
- The final plan and cost estimations calculated by the optimizer
- The abstract plan for the query
- The result of the query if the result set is executed (for example, if noexec is not on)
- The logical operator tree for the query generated by set option show
- Query execution time generated by set statistics time
- After you execute the query, the query execution time generated by set statistics time
You must install and configure the Job Schedule to run sp_opt_querystats.
See "Controlling Optimization" in the Performance and Tuning Series: Query Processing and Abstract Plans.