Execution plan for specific stored procedure in SQL server

You would have to reply on DMV sys.dm_exec_cached_plans. Below query will give you stored execution plans for the procedures. If you want to filter you have to add where OBJECT_NAME(procstats.object_id) ='stored_proc_name'. The source of query is This Blog. Please read the blog before proceeding.

 SELECT OBJECT_NAME(procstats.object_id) AS proc_name,

    --execution plan

     qplan.query_plan,

    --total stats

    procstats.execution_count,

    procstats.total_logical_reads,

    procstats.total_physical_reads,

    procstats.total_worker_time/1000000.0 AS total_worker_time_sec,

    procstats.total_elapsed_time/1000000.0 AS total_elapsed_time_sec,

    --last stats

    procstats.last_execution_time,

    procstats.last_elapsed_time AS last_elapsed_time_microsec,

    procstats.last_elapsed_time/1000000.0 AS last_elapsed_time_sec,

    procstats.last_logical_reads,

    procstats.last_physical_reads,

    procstats.last_worker_time AS last_cpu_time_microsec,

    procstats.last_worker_time/1000000.0 AS last_cpu_time

    FROM sys.dm_exec_procedure_stats procstats

    CROSS APPLY sys.dm_exec_query_plan (procstats.plan_handle) qplan

    WHERE procstats.database_id = DB_ID()

    AND procstats.type = ‘P’ — SQL Stored Procedure

    --AND OBJECT_NAME(procstats.object_id) = ‘Your proc name’

    ORDER BY proc_name

    --  total_logical_reads desc

    --last_execution_time desc

    --last_logical_reads desc

   -- last_worker_time desc

Its quite possible that due to memory pressure and other parameters your procs plan is flushed out in that case if you want to get estimated plan

SET SHOWPLAN_ALL ON
GO
EXEC STOREDPROC_NAME
GO
SET SHOWPLAN_ALL OFF

One method to get the plan for a cached stored procedure is the query below. Note that you may have multiple plans for the same proc due to different session settings.

SELECT qp.query_plan
FROM sys.dm_exec_procedure_stats AS ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE 
    ps.database_id = DB_ID(N'YourDatabase')
    AND ps.object_id = OBJECT_ID(N'YourDatabase.YourSchema.YourProc');

Hi @S_NO ,

Please check these posts if help:
https://dba.stackexchange.com/questions/50552/sql-server-2005-get-execution-plan-of-a-overnight-stored-procedure

https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan-in-sql-server

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this