home


Save and display query plans and statements for all spids on server (SQL Server 2005 only)
Author Nigel Rivett

When you run the sp s_GetQueryPlans it will create a file of every statement (or a single spid) that is running on the system.
It will also create other files of associated query plans.
Double click on the files and they will open in management studio showing the query or query plan diagram.
This is useful for diagnosing problems on a system.

The files will be saved in the folder specified in the SP call.
You will need to change the bcp statements to include the login/password or integrated security.
The files will be saved as

yyyymmdd_hhmmss_plan_.sqlplan
yyyymmdd_hhmmss_statement_.sql


With thanks to Frank Bazan for drawing my attention to
http://blogs.msdn.com/sqlcat/archive/2005/09/23/473367.aspx



create proc s_GetQueryPlans 
@spid int = null ,
@dir varchar(256) = 'c:\temp\'
as
declare @dte varchar(100)
	select @dte = convert(varchar(8),getdate(),112) + '_' + replace(convert(varchar(8),getdate(),108), ':','')
select @dir = @dir + @dte + '_'

create table ##tbl (spid int, statement varchar(max), statement_plan xml)
insert ##tbl exec s_GetQueryPlan2 @spid
select * from ##tbl
if exists (select * from ##tbl)
begin
	declare @sql varchar(8000), @sp varchar(20)
	select @sp = 0
	while convert(int,@sp) < (select max(spid) from ##tbl)
	begin
		select @sp = convert(varchar(20),min(spid)) from ##tbl where spid > @sp
		select @sql = 'bcp "select statement from ##tbl where spid = ' + @sp + '" queryout ' + @dir + 'statement_' + @sp + '.sql -c -Usauser -Psapassword -S' + @@servername
		exec master..xp_cmdshell @sql
		select @sql = 'bcp "select statement_plan from ##tbl where spid = ' + @sp + '" queryout ' + @dir + 'plan_' + @sp + '.sqlplan -c -Usa -Pjanice -S' + @@servername
		exec master..xp_cmdshell @sql
	end
end
drop table ##tbl

go

create proc s_GetQueryPlan2
@spid	int = null
as
	--select pln.*, req.*
	select req.session_id, pln.statement_text, pln.statement_plan
	from sys.dm_exec_requests as req
		CROSS APPLY statement_level_query_plan(plan_handle) as pln
	where statement_text like
				'%' +
				replace(
				left(
							   substring((select text from master.sys.dm_exec_sql_text(sql_handle)), 
									   statement_start_offset/2, 
									   1+      case when statement_end_offset = -1 
											  then LEN((select text from master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
											   else statement_end_offset/2 - statement_start_offset/2 
											  end) 
						,3000)
				, '[','[[]') + '%'
	and (session_id = @spid or @spid is null)
go


--  statement_level_query_plan.sql ***********************************************
--
--      v1.3
--      Stuart Ozer
--      Microsoft Corporation
--      Provided AS-IS with no warranties
--
--
-----------------------------------------------------------

CREATE FUNCTION statement_level_query_plan(
@handle as varbinary(64) -- Handle for the overall query plan
)
RETURNS TABLE as 
RETURN (
  select 
        statement_nbr,                 -- Sequential number of statement within batch or SP
        statement_type,                       -- SELECT, INSERT, UPDATE, etc
        statement_subtree_cost,               -- Estimated Query Cost
        statement_estimated_rows,             -- Estimated Rows Returned
        statement_optimization_level,         -- FULL or TRIVIAL
        statement_text,                       -- Text of query
        statement_plan                -- XML Plan    To view as a graphical plan
                                                     --      save the column output to a file with extension .SQLPlan
                                                     --      then reopen the file by double-clicking
   from (
        select 
               C.value('@StatementId','int') as statement_nbr,
               C.value('(./@StatementText)','nvarchar(max)') as statement_text,
               C.value('(./@StatementType)','varchar(20)') as statement_type,
               C.value('(./@StatementSubTreeCost)','float') as statement_subtree_cost,
               C.value('(./@StatementEstRows)','float') as statement_estimated_rows,
               C.value('(./@StatementOptmLevel)','varchar(20)') as statement_optimization_level,
--             Construct the XML headers around the single plan that will permit
--             this column to be used as a graphical showplan.
--             Only generate plan columns where statement has an associated plan
               C.query('declare namespace PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                       if (./PLN:QueryPlan or ./PLN:Condition/PLN:QueryPlan) 
                       then
                               
                              { ./attribute::* }
                              { ./descendant::PLN:QueryPlan[1] }
                               
                       else ()
               ') as statement_plan
        from 
               sys.dm_exec_query_plan(@handle)
        CROSS APPLY 
--             This expression finds all nodes containing attribute StatementText
--             regardless of how deep they are in the potentially nested batch hierarchy
--             The results of this expression are processed by the Select expressions above
               query_plan.nodes('declare namespace PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]') 
                       as T(C) 
        ) x
  )
go


home