March 5, 2019 at 10:32 am
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?
March 5, 2019 at 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;
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/
March 5, 2019 at 10:42 am
Michael L John - Tuesday, March 5, 2019 10:36 AMQuery 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.
March 5, 2019 at 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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 5, 2019 at 11:19 am
ScottPletcher - Tuesday, March 5, 2019 10:52 AMThe 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.
March 6, 2019 at 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2019 at 5:28 am
Today's SSC Featured Contents includes a section on capturing live execution plans using Extended Events.
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
March 6, 2019 at 6:11 am
ChrisM@Work - Wednesday, March 6, 2019 5:28 AMToday'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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2019 at 6:37 am
Grant Fritchey - Wednesday, March 6, 2019 5:16 AMAll 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.
March 6, 2019 at 6:38 am
ChrisM@Work - Wednesday, March 6, 2019 5:28 AMToday's SSC Featured Contents includes a section on capturing live execution plans using Extended Events.
That's good timing for an article!
March 6, 2019 at 6:46 am
Jonathan AC Roberts - Wednesday, March 6, 2019 6:37 AMGrant Fritchey - Wednesday, March 6, 2019 5:16 AMAll 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2019 at 7:37 am
Grant Fritchey - Wednesday, March 6, 2019 6:46 AMJonathan AC Roberts - Wednesday, March 6, 2019 6:37 AMGrant Fritchey - Wednesday, March 6, 2019 5:16 AMAll 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?
March 6, 2019 at 9:00 am
Jonathan AC Roberts - Wednesday, March 6, 2019 7:37 AMGrant Fritchey - Wednesday, March 6, 2019 6:46 AMJonathan AC Roberts - Wednesday, March 6, 2019 6:37 AMGrant Fritchey - Wednesday, March 6, 2019 5:16 AMAll 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2019 at 9:01 am
Grant Fritchey - Wednesday, March 6, 2019 9:00 AMJonathan AC Roberts - Wednesday, March 6, 2019 7:37 AMThanks, 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply