Execution plan for specific stored procedure in SQL server

S_NO 21 Reputation points
2020-09-09T11:39:29.85+00:00

Team,

Have SQL server 2008R2-wanted to know complete Query to get a execution plan from the cache for specific storedprocedure only.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,562 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Shashank Singh 6,211 Reputation points
    2020-09-09T12:38:15.727+00:00

    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
    
    No comments

  2. Dan Guzman 6,926 Reputation points
    2020-09-09T12:51:28.82+00:00

    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');
    
    No comments

  3. Cris Zhan-MSFT 6,561 Reputation points
    2020-09-10T08:03:59.507+00:00

    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