Prev: seeking set based solution
Next: I assume Row_Number doesn’t act only on rows of the window frame
From: Erland Sommarskog on 30 Apr 2010 17:59
Iter (Iter(a)discussions.microsoft.com) writes:
> I mean is that a way that I can get each query plans for each statement
> in the sp when I run the stored procedure. I know I need to copy that
> statement into SSMS and produce the query plan now. Is that a way that I
> can get all query plan when I run the sp? Just like sybase does. thank.

If the procedure has been executed so that it is in cache, you can
do:

SELECT qp.query_plan
FROM sys.dm_exec_procedure_stats eps
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) qp
WHERE eps.database_id = db_id()
AND eps.object_id = object_id('dbo.your_sp')

Then click on the XML document and you will see a graphical plan.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: seeking set based solution
Next: I assume Row_Number doesn’t act only on rows of the window frame