Post reply

How to get Execution Plan after query was run?

  • December 11, 2014 at 9:24 am

    #293397

    I am wondering is there possibility to get execution plan after the query ran by application? Another word, when I run 'top poor performance query' it gives me a 'link' for query plan, not the graphical execution plan.

  • December 11, 2014 at 9:28 am

    #1764135

    You need to copy and paste the plan into a text file and save it with a .sqlplan extension. You can then open it in graphical form in SSIS.

    John

  • December 11, 2014 at 10:34 am

    #1764156

    John Mitchell-245523 (12/11/2014)

    You need to copy and paste the plan into a text file and save it with a .sqlplan extension. You can then open it in graphical form in SSIS.

    John

    I believe that you mean SSMS.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 11, 2014 at 12:50 pm

    #1764200

    Have you tried clicking on that link? It will open a graphical plan within SSMS.

    But, if not, you can always query sys.dm_exec_text_query_plan. That will output just a raw varchar(max) column that you can save to a file with the right extension and then you'll have an execution plan.

    Note, these plans retrieved from cache are "actual" plans in that they are the plans that were used to manipulate your data. But, they're not "actual" plans in that they will have no run-time information (Actual Executions, Actual Rowcount, etc.).

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 11, 2014 at 2:35 pm

    #1764229

    Further on Grant't post, here's a code snip for retrieving the execution plan

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    /*

    The query

    */

    select

    *

    from sys.objects

    /* get the execution plan */

    ;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

    ,QUERY_PLAN AS

    (

    SELECT

    XP.query_plan

    FROM sys.dm_exec_requests ER

    CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) XP

    WHERE ER.session_id = @@SPID

    )

    SELECT

    *

    FROM QUERY_PLAN QP;

  • December 12, 2014 at 6:59 am

    #1764402

    Thank you all smart folks 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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