Capturing Optimizer Diagnostics (internally), ... almost there

[kevin_sherlock]

WHAT I'M TRYING TO ACCOMPLISH

I've been playing around with the concept of trying to capture some optimizer diagnostics with the tools provided in ASE 15.7.   Specifically, the showplan_in_xml() builtin function.  In theory, this can allow you to programatically capture many useful diagnostics, such as estimated and actual logical io, physical io (not that important), rowcounts (huge benefit), as well as all aspects of the showplan output including table scans, index scans, key positioning, index selection, etc.

Imagine the power imparted to the common customer who can analyze these kinds of diagnostics to determine (or confidently predict) that statistics for a particular column or colums need to be updated because io/rowcount estimates made by the optimizer greatly differ from the actuals.   Or, that a particular table is being scanned, or a join order is changing, large io is or is not being used, etc.  All of this available by using TSQL scripts rather than clumsy or expensive client side tools.

WHAT CHOICES DO WE HAVE?

Currently (as of ASE 15.7 SP110 which is what I'm testing with), we have a few choices to capture this info.

MDA tables monSysPlanText and the like capture query plans, but don't include io and rowcount estimates.  It's also a bit difficult to separate out the query plan elements.

The closest I can get to something useful is the combination of:

set plan for show_execio_xml to message on

/* execute your SQL here */

select showplan_in_xml(0) as "xmldoc" into #xmlplans

As a result, you have an XML document stored as a TEXT column named "xmldoc" in the temp table #xmlplans.  At this point you would proceed to shred the xml using a function like xmlextract().  Going farther, you could create a "diagnostics" table based on that xmldoc using columns derived by the xmlextract() function, etc.

As documented, the showplan_in_xml(n) function will access the plans for up to 20 statements.  The argument passed to the function is the n'th statement in that bufffer.  Special arguments zero "0" accesses ALL statements, and "-1" access the LAST statement in that buffer. 

HOWEVER, several things stand in the way of making this useable, and I don't quite understand the implementation of showplan_in_xml() in this regard.

ROADBLOCKS

Firstly, passing the argument "0" to the function, ie "select showplan_in_xml(0)" produces an invalid XML document when more than one statement is optimized.  That means that your call to xmlextract will fail.  So, the question is, "what exactly is the purpose of the "0" argument to this function?".

Secondly, ANY call to showplan_in_xml(n) will result in the buffer being purged!  So, if the "0" argument isn't useful, you might think that simply iterating over all of the 20 statements for 20 separate XML docs would do.  But alas, no.  The first call (presumably "select showplan_in_xml(1)") wipes out the buffer the next call.

So, we are left to somehow manipulate an invalid XML doc from showplan_in_xml(0) which is tough to do since we are manipulating a TEXT column.  Fun, fun.

UNABASHED PLEA FOR CHANGES HERE

It's apparent that showplan_in_xml was developed to support client applications that can process these documents on the client side.  However, "set plan for show_execio_xml to message on" seems to offer a path inside ASE that deadends very quickly.   If there is still an avenue for enhancement requests for ASE, let's extend this functionality somehow so that useful XML documents can be generated and we can begin to access this incredibly useful info inside ASE using XML functionality already provided.  Also, perhaps extend the limit of 20 statements buffered to something configurable using "sp_configure"?