Capturing SHOWPLAN_ALL output to a table

• paul.bowman

  SSC Veteran

  Points: 277

  More actions

  November 26, 2015 at 8:25 am

  #104260

  SHOWPLAN_ALL produces a handy table format output which is fine for analysing individual T-SQL scripts (although for that, it's handier to just use the xml sqlplan output and SSMS's feature to present that graphically with pop-up details).

  What I would like to do is capture the SHOWPLAN_ALL output for a whole range of scripts and code objects, to help automate hunting for particular problematic code constructions (this is actually in relation to upgrading from 2008 to 2012 which creates a particular problem in relation to "sorted" inserts with order by clauses - see http://bit.ly/1NvKHL1). Having the SHOWPLAN_ALL output for all code objects would help to hunt down such relatively rare but problematic sql constructions.

  The difficulty I have is that "set showplan_all on" insists on being run as it's own batch, so there's no way to use sp_executesql as this doesn't allow batch separators (AFAIK - ditto EXEC(@sqlstmt)).

  At the moment the only way I can see to get around the single-batch limitations to dynamic sql set by sp_executesql (ah, if only they would include a parameter to set connection settings...) is to use command mode and :out my multi-batch dynamic sql (including the prior set showplan_all batch), :r that into a second :out file and then bulk insert the resulting output.

  Any suggestions as to a less contorted method welcome. Obviously, if at all possible, I would like to try and avoid attempting to shred the xml showplan output.

  Btw, the BOL ref for SHOWPLAN_ALL is deficient in not giving the column types of the table output. From my limited testing so far, the following table def seems to take the output ok:

  create table #showplan (

  StmtText nvarchar(max)

  , StmtId int

  , NodeId int

  , Parent int

  , PhysicalOp nvarchar(125) null

  , LogicalOp nvarchar(125) null

  , Argument nvarchar(max) null

  , DefinedValues nvarchar(max) null

  , EstimateRows bigint null

  , EstimateIO decimal(20,8) null

  , EstimateCPU decimal(20,8) null

  , AvgRowSize bigint null

  , TotalSubtreeCost decimal(20,8) null

  , OutputList nvarchar(max) null

  , Warnings nvarchar(max) null

  , Type nvarchar(125)

  , Parallel bit

  , EstimateExecutions bigint null

  )

  go