How to get the execution plan of a stored procedure?

Think about a situation where you have identified a badly performing stored procedure in Production environment and now you want to see the execution plan of it for further analysis. Please note, executing the procedure with sample parameter values in Production environment should be done with extra care because if the procedure is only SELECT then no issue but if it has DML operations, then issue.

You can use following DMVs to get the execution plan either XML format or graphical one.

USE AdventureWorks2008R2

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID=60

Note: I executed the SP first so that plan will be created. In actual environment this is not necessary.

select Plan_handle from sys.dm_exec_procedure_stats ps

where ps.database_id=DB_ID('AdventureWorks2008R2')

              and object_id=object_id('dbo.uspGetEmployeeManagers')  

--Copy and paste the plan handle to the below query

select * from sys.dm_exec_query_plan(0x05000500EAE8807F40E13287000000000000000000000000)

Cheers for reading this blog post.