Post reply

How to see currently executing store procedure execution plan?

  • December 31, 2014 at 11:20 am

    #397823

    Hi

    when there is store procedure executing and taking hours. How can see the execution plan for that store procedure beside Profiler? and/or how to check if the SP using which plan to execute?

    thanks

  • December 31, 2014 at 12:24 pm

    #1767267

    install sp_whoisactive on your server from this source:

    http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

    when you run that command, it will return a clickable cell in SSMS for each current command , and that will open an xml containg the actual script that is currently running for a long time.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 31, 2014 at 12:50 pm

    #1767275

    Thanks for your reply. As you said I can see the script but how can I see execution plan while it was running or after it ran?

  • December 31, 2014 at 12:57 pm

    #1767276

    yes you can..

    there is some really nice optional features for the procedure.

    try running it with these flags:

    EXEC sp_whoisactive @get_outer_command = 1,@get_plans=1,@get_full_inner_text=1

    here's the list of parameters(sp_help sp_whoisactive)

    Parameter_nameTypeLengthPrecScaleParam_orderCollation

    @filtersysname256128NULL1SQL_Latin1_General_CP1_CI_AS

    @filter_typevarchar1010NULL2SQL_Latin1_General_CP1_CI_AS

    @not_filtersysname256128NULL3SQL_Latin1_General_CP1_CI_AS

    @not_filter_typevarchar1010NULL4SQL_Latin1_General_CP1_CI_AS

    @show_own_spidbit11NULL5NULL

    @show_system_spidsbit11NULL6NULL

    @show_sleeping_spidstinyint1307NULL

    @get_full_inner_textbit11NULL8NULL

    @get_planstinyint1309NULL

    @get_outer_commandbit11NULL10NULL

    @get_transaction_infobit11NULL11NULL

    @get_task_infotinyint13012NULL

    @get_locksbit11NULL13NULL

    @get_avg_timebit11NULL14NULL

    @get_additional_infobit11NULL15NULL

    @find_block_leadersbit11NULL16NULL

    @delta_intervaltinyint13017NULL

    @output_column_listvarchar80008000NULL18SQL_Latin1_General_CP1_CI_AS

    @sort_ordervarchar500500NULL19SQL_Latin1_General_CP1_CI_AS

    @format_outputtinyint13020NULL

    @destination_tablevarchar40004000NULL21SQL_Latin1_General_CP1_CI_AS

    @return_schemabit11NULL22NULL

    @schemavarchar-10NULL23SQL_Latin1_General_CP1_CI_AS

    @helpbit11NULL24NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 31, 2014 at 1:37 pm

    #1767284

    a quick follow up:

    when you run the command above , you get results like this:

    you cna see any of the blue links or the plan itself becomes clickable.....

    one of the columns is the plan itself, which you can simply click to view/save.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 2, 2015 at 1:18 pm

    #1767473

    is there a way to extract the execution that was last created/used for a stored procedure.

    I try to extract from sys.dm_exec_cached_plans but for some reason it doesnt show for all stored procedures

  • January 2, 2015 at 1:27 pm

    #1767475

    isn't it true that only procedures that have been called would have a cache in place?

    so if it's not in cache, it's either been aged out, or never called, or created with the WITH RECOMPILE option, so it never saves a cache anyway, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 2, 2015 at 1:57 pm

    #1767483

    mxy (1/2/2015)

    is there a way to extract the execution that was last created/used for a stored procedure.

    I try to extract from sys.dm_exec_cached_plans but for some reason it doesnt show for all stored procedures

    The only thing you can get from that is the estimated plan that was saved. It is the "guide" SQL Server uses when a command executed, not "truly" the actual plan. Because from the query optimizer going through the motions there is a chance that it chose to modify that plan for actual execution.

    The sp_whoisactive is your best option for getting the actual execution plan. You can also use that command to output data to a table for frequent capturing over time. There are a number of blog post and examples I believe out there on how to do it.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply