Product suggestion: SELECT ... GRAPHICAL_PLAN option

[Breck_Carter]

Folks often know which queries are their Queries From Hell, and often they are ongoing problems... the execution plans change over time causing performance to oscillate by orders of magnitude. A method of continuous plan monitoring is desired, similar in output to the GRAPHICAL_PLAN function.

Here's a suggestion for a GRAPHICAL_PLAN clause:

SELECT ...
       [other clauses]
       GRAPHICAL_PLAN ( @long-varchar-result, @absolute-cost-milliseconds )
       [other clauses]

If the connection-level database option

SET TEMPORARY OPTION GRAPHICAL_PLAN_OUTPUT = 'OFF' 

when the SELECT is executed, the GRAPHICAL_PLAN clause would be ignored.

If

SET TEMPORARY OPTION GRAPHICAL_PLAN_OUTPUT = 'ON' 

then the query engine would hang on to the execution plan until the SELECT finished, and if the query took @absolute-cost-milliseconds or longer, the XML version of the plan would be written to the @Long-varchar-result; if not, then it would be set to ''. It would be up to the application code (e.g., surrounding SQL code in the procedure) to do something (save to a file, table, etc) with the result.

Folks with Queries From Hell are highly motivated to monitor their queries in production. Calling the existing GRAPHICAL_PLAN() function is out of the question for two reasons: (1) there is no way to test the absolute cost, and (2) it requires the query to be executed twice... which is not just a performance issue, but it may result in a different plan altogether.

The Database Profiler and Application Profiler are nice enough but in this context they're not the appropriate choice of equipment... like skydiving in a car.

The existence of the Database Profiler and Application Profiler, however, convince me that the underlying harness for this already exists within SQL Anywhere... perhaps it just needs to be sweetened with some Syntactic Sugar 🙂