Save the actual execution plan of a SQL statement in a script

  • I have a script that runs in SSIS from an "Execute SQL Task". One statement that is taking about 1 hour in my test environment is taking about 10 hours in production, on similar sized data. I suspect the execution plan is different but don't know how find out what the actual execution plan is.
    So my question is: Is there any way I can save/log the actual execution plan of a query after it has run in batch mode?
    I've tried adding a unique bit of text (a generated GUID) to the SQL statement in the SSIS package (to make it easily identifiable). So the SQL in the script now looks something like this:
    SELECT --6F39BA4B-9EA3-4501-BCF1-8C0C1511B387
       [x].[Col1], 
    ...

    Then immediately after the query has run the script has the following statement:
    INSERT INTO [dbo].[SSIS_LOG_PLAN]([sql_text], [query_plan], [DateStamp])
    SELECT -- D450B7CE-B153-4C11-ADCC-0613566F51DE
           sqlplan.text sql_text,
           qp.query_plan,
           CURRENT_TIMESTAMP DateStamp
      FROM sys.dm_exec_cached_plans c
     CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS sqlplan
     CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) AS QP
     WHERE sqlplan.text like '%6F39BA4B-9EA3-4501-BCF1-8C0C1511B387%'
       AND sqlplan.text not like '%D450B7CE-B153-4C11-ADCC-0613566F51DE%'

    This gets an execution plan but it seems to be an estimated plan not the actual plan.
    Is there a better way to get the plan than this?

  • Query store?


    SELECT --TOP 10
        t.query_sql_text,
        q.query_id,
        OBJECT_NAME(q.object_id) AS parent_object,
        CONVERT(xml, P.query_plan),
        Q.last_execution_time
    FROM sys.query_store_query_text t
       INNER JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
       INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
       INNER JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
    WHERE OBJECT_NAME(q.object_id) LIKE '%Your object%'
    ORDER BY
       Q.last_execution_time DESC;

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John - Tuesday, March 5, 2019 10:36 AM

    Query store?


    SELECT --TOP 10
        t.query_sql_text,
        q.query_id,
        OBJECT_NAME(q.object_id) AS parent_object,
        CONVERT(xml, P.query_plan),
        Q.last_execution_time
    FROM sys.query_store_query_text t
       INNER JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
       INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
       INNER JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
    WHERE OBJECT_NAME(q.object_id) LIKE '%Your object%'
    ORDER BY
       Q.last_execution_time DESC;

    I think that started in SQL Server 2016, I'm using 2012 for this.

  • The estimated plan will be the actual plan.  What's missing from the estimated plan is the actual row counts; in an estimated plan, naturally the query didn't actually run, so SQL can't give you the actual row counts or exec counts.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher - Tuesday, March 5, 2019 10:52 AM

    The estimated plan will be the actual plan.  What's missing from the estimated plan is the actual row counts; in an estimated plan, naturally the query didn't actually run, so SQL can't give you the actual row counts or exec counts.

    Thanks Scott, the plan is saved after the query has run, so I'd have thought it could get the actual rows. Is there anywhere I can get this information from a script or is there a better way to do it?
    I think that might be enough though as really all I want is the actual execution plan that was used on that query, I'm not too interested in the numbers on the tables as I can get them from elsewhere.

  • All execution plans are estimated plans. If you execute a query in combination with capture of the plan, you can add runtime statistics to the estimated plan. However, that plan is always estimated, whether retrieved from the cache through the DMVs, Query Store, captured through SSMS, or captured using Trace or Extended Events (although those two, capture a structurally different plan, weird). There is no way to get the runtime statistics except by either running the query in SSMS and capturing it that way, or using Trace or Extended Events.

    In this case, I strongly recommend using Extended Events. Two reasons. First, trace or Extended Events, capturing an actual execution plan is expensive. Second, because the filtering of Extended Events is so much better than Trace, you can radically reduce the impact of capturing the plans.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Today's SSC Featured Contents includes a section on capturing live execution plans using Extended Events.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work - Wednesday, March 6, 2019 5:28 AM

    Today's SSC Featured Contents includes a section on capturing live execution plans using Extended Events.

    Probably using the lightweight trace event. That's handy for getting those living plans for queries actively executing. It doesn't do much in the situation where we are currently. Not saying it's not handy or cool though.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey - Wednesday, March 6, 2019 5:16 AM

    All execution plans are estimated plans. If you execute a query in combination with capture of the plan, you can add runtime statistics to the estimated plan. However, that plan is always estimated, whether retrieved from the cache through the DMVs, Query Store, captured through SSMS, or captured using Trace or Extended Events (although those two, capture a structurally different plan, weird). There is no way to get the runtime statistics except by either running the query in SSMS and capturing it that way, or using Trace or Extended Events.

    In this case, I strongly recommend using Extended Events. Two reasons. First, trace or Extended Events, capturing an actual execution plan is expensive. Second, because the filtering of Extended Events is so much better than Trace, you can radically reduce the impact of capturing the plans.

    Thanks Grant. I haven't used extended events before so I need to do a bit of reading. 
    Should I set extended events up and just leave it running through the entire ETL process? It runs on a Sunday starting at about 4 am so I don't really want to "man the desk" for any of the times it's running.

  • ChrisM@Work - Wednesday, March 6, 2019 5:28 AM

    Today's SSC Featured Contents includes a section on capturing live execution plans using Extended Events.

    That's good timing for an article!

  • Jonathan AC Roberts - Wednesday, March 6, 2019 6:37 AM

    Grant Fritchey - Wednesday, March 6, 2019 5:16 AM

    All execution plans are estimated plans. If you execute a query in combination with capture of the plan, you can add runtime statistics to the estimated plan. However, that plan is always estimated, whether retrieved from the cache through the DMVs, Query Store, captured through SSMS, or captured using Trace or Extended Events (although those two, capture a structurally different plan, weird). There is no way to get the runtime statistics except by either running the query in SSMS and capturing it that way, or using Trace or Extended Events.

    In this case, I strongly recommend using Extended Events. Two reasons. First, trace or Extended Events, capturing an actual execution plan is expensive. Second, because the filtering of Extended Events is so much better than Trace, you can radically reduce the impact of capturing the plans.

    Thanks Grant. I haven't used extended events before so I'll need to do a bit of reading. 
    Should I set extended events up and just leave it running through the entire ETL process? It runs on a Sunday starting at about 4 am so I don't really want to "man the desk" for any of the times it's running.

    You can. Filtering is the key to reduce the overhead. Not knowing what you're dealing with exactly I can't suggest specific filtering. I can tell you, more filtering is better, and put the most restrictive filter first so that as many events as possible are eliminated from capture. It helps to reduce the overhead.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey - Wednesday, March 6, 2019 6:46 AM

    Jonathan AC Roberts - Wednesday, March 6, 2019 6:37 AM

    Grant Fritchey - Wednesday, March 6, 2019 5:16 AM

    All execution plans are estimated plans. If you execute a query in combination with capture of the plan, you can add runtime statistics to the estimated plan. However, that plan is always estimated, whether retrieved from the cache through the DMVs, Query Store, captured through SSMS, or captured using Trace or Extended Events (although those two, capture a structurally different plan, weird). There is no way to get the runtime statistics except by either running the query in SSMS and capturing it that way, or using Trace or Extended Events.

    In this case, I strongly recommend using Extended Events. Two reasons. First, trace or Extended Events, capturing an actual execution plan is expensive. Second, because the filtering of Extended Events is so much better than Trace, you can radically reduce the impact of capturing the plans.

    Thanks Grant. I haven't used extended events before so I'll need to do a bit of reading. 
    Should I set extended events up and just leave it running through the entire ETL process? It runs on a Sunday starting at about 4 am so I don't really want to "man the desk" for any of the times it's running.

    You can. Filtering is the key to reduce the overhead. Not knowing what you're dealing with exactly I can't suggest specific filtering. I can tell you, more filtering is better, and put the most restrictive filter first so that as many events as possible are eliminated from capture. It helps to reduce the overhead.

    Thanks, Is it possible to filter on the SQL statement containing some specific text?

  • Jonathan AC Roberts - Wednesday, March 6, 2019 7:37 AM

    Grant Fritchey - Wednesday, March 6, 2019 6:46 AM

    Jonathan AC Roberts - Wednesday, March 6, 2019 6:37 AM

    Grant Fritchey - Wednesday, March 6, 2019 5:16 AM

    All execution plans are estimated plans. If you execute a query in combination with capture of the plan, you can add runtime statistics to the estimated plan. However, that plan is always estimated, whether retrieved from the cache through the DMVs, Query Store, captured through SSMS, or captured using Trace or Extended Events (although those two, capture a structurally different plan, weird). There is no way to get the runtime statistics except by either running the query in SSMS and capturing it that way, or using Trace or Extended Events.

    In this case, I strongly recommend using Extended Events. Two reasons. First, trace or Extended Events, capturing an actual execution plan is expensive. Second, because the filtering of Extended Events is so much better than Trace, you can radically reduce the impact of capturing the plans.

    Thanks Grant. I haven't used extended events before so I'll need to do a bit of reading. 
    Should I set extended events up and just leave it running through the entire ETL process? It runs on a Sunday starting at about 4 am so I don't really want to "man the desk" for any of the times it's running.

    You can. Filtering is the key to reduce the overhead. Not knowing what you're dealing with exactly I can't suggest specific filtering. I can tell you, more filtering is better, and put the most restrictive filter first so that as many events as possible are eliminated from capture. It helps to reduce the overhead.

    Thanks, Is it possible to filter on the SQL statement containing some specific text?

    Yes. Absolutely. It does depend on the event you're capturing how you want to set that up, but you can filter by statement or even part of a statement.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey - Wednesday, March 6, 2019 9:00 AM

    Jonathan AC Roberts - Wednesday, March 6, 2019 7:37 AM

    Thanks, Is it possible to filter on the SQL statement containing some specific text?

    Yes. Absolutely. It does depend on the event you're capturing how you want to set that up, but you can filter by statement or even part of a statement.

    Or query hash or plan hash or...

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 14 posts - 1 through 14 (of 14 total)

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