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 :)

asked 01 Apr '11, 16:35

Breck%20Carter's gravatar image

Breck Carter
25.7k427586845
accept rate: 20%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107

question asked: 01 Apr '11, 16:35

question was seen: 397 times

last updated: 01 Apr '11, 16:35