• 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