Skip to Content
Author's profile photo Kevin Sherlock

Capturing Optimizer Diagnostics (internally), … almost there


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.


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.


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.


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”?

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Kevin Sherlock
      Kevin Sherlock
      Blog Post Author

      BTW - i'm also exploring "sp_opt_querystats" as an alternative for those who can or already have installed job scheduler.

      Author's profile photo Jeff Tallman
      Jeff Tallman

      I can't comment on multi-statement batches and show_plan_in_xml(), but I have been doing a lot of work with the cached statement cousin - show_cached_plan_in_xml() and have a lot of shredding logic you can borrow.....also, I have a variation on sp_opt_querystats I call does the column stats but not the index densities....but would give you a start.

      Author's profile photo Kevin Sherlock
      Kevin Sherlock
      Blog Post Author

      Thanks Jeff, I'll take you up on your offer wrt show_cached_plan_in_xml() and shredding logic.

      As for sp_opt_querystats and your sp_help_stats, are you possibly confusing this with sp_showoptstats ??? sp_opt_querystats runs on job scheduler (and is passed a single batch) which for the purposes outlined in my discussion above, doesn't necessarily work for me.
        sp_showoptstats of course is just a variation of what I wrote in sp__optdiag.  wondering if your sp_help_stats runs like sp_opt_querystats via job scheduler, etc.

      Author's profile photo Jeff Tallman
      Jeff Tallman

      Sigh....I hate syntax...yes, I was thinking of sp_showoptstats....which has all XML output (there is an undoc'd text output param, but it doesn't work as there is no code to implement it).   Got pulled in by all the discusson on XML.    Shoot me a private email and I will send you the proc I use for show_cached_plan_in_XML().