Often I need to examine an execution plan from a production server. Just as often, I can’t re-run the stored procedure in that environment. There are several techniques for simulating the environment on a test server such as exporting statistics and using the un-documented DBCC AUTOPILOT command.
These methods take a bit of setup and time. So my first step is usually to hit up the cached execution plan, assuming that the query or procedure has been run since the last server restart. Below is a simple script to return the execution plan and some stats for any object whether it be a stored procedure, function, etc.
Query the query cache
SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) sqlT CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp INNER JOIN sys.objects o ON sqlT.objectid = o.object_id INNER JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = 'object_name' AND s.name = 'schema_name';