November 26, 2015 at 8:25 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply