August 11, 2017 at 7:36 am
Hi Everyone,
I extracted query plans for the currently running sessions , along with their wait types , SP's/Code/wait resource etc. The query plans do not have the 'Actual Rows' and other 'Actual' information in it. Has only 'Estimated' values . Any reasons why it misses them ? and how to get the Actual info too , which is essential to work with the stale statistics. Thank you .
Arshad
August 11, 2017 at 7:46 am
Arshad
The plans that are stored in cache are plans that are used for all executions of a particular query until the plan is evicted from cache. That's why it doesn't contain run-time ("actual") statistics. To get that, you need to capture the plan at run time, for example by pressing the Include Actual Execution Plan button in SSMS, or by enabling Query Store in SQL Server 2016.
John
August 11, 2017 at 9:03 am
John Mitchell-245523 - Friday, August 11, 2017 7:46 AMArshadThe plans that are stored in cache are plans that are used for all executions of a particular query until the plan is evicted from cache. That's why it doesn't contain run-time ("actual") statistics. To get that, you need to capture the plan at run time, for example by pressing the Include Actual Execution Plan button in SSMS, or by enabling Query Store in SQL Server 2016.
John
Thanks John . So ,does it mean that the execution of that query had finished ? But given the number of users our application has , around 800 connections are there normally doing something every time , shouldn't some of them be going through their 'run-time' when I query , especially during performance bottlenecks ? curious..
Arshad
August 11, 2017 at 9:07 am
No. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)
John
August 13, 2017 at 4:43 am
John Mitchell-245523 - Friday, August 11, 2017 9:07 AMNo. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)John
Then how what do I need to do to get the Actual.
Arshad
August 13, 2017 at 6:00 am
Arsh - Sunday, August 13, 2017 4:43 AMJohn Mitchell-245523 - Friday, August 11, 2017 9:07 AMNo. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)John
Then how what do I need to do to get the Actual.
Arshad
Run the query in Management Studio with the "Include Actual Execution Plan" button selected. Or run the query after having run SET STATISTICS XML ON, save the resulting xml file as a .sqlplan file and open it in SSMS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2017 at 2:13 am
GilaMonster - Sunday, August 13, 2017 6:00 AMArsh - Sunday, August 13, 2017 4:43 AMJohn Mitchell-245523 - Friday, August 11, 2017 9:07 AMNo. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)John
Then how what do I need to do to get the Actual.
Arshad
Run the query in Management Studio with the "Include Actual Execution Plan" button selected. Or run the query after having run SET STATISTICS XML ON, save the resulting xml file as a .sqlplan file and open it in SSMS.
Yeah I know that option in SSMS . I am asking for a way to do this without knowing the query itself , that is , for all the code that is currently running on the production database .
Arshad
August 14, 2017 at 2:20 am
Arsh - Monday, August 14, 2017 2:13 AMGilaMonster - Sunday, August 13, 2017 6:00 AMArsh - Sunday, August 13, 2017 4:43 AMJohn Mitchell-245523 - Friday, August 11, 2017 9:07 AMNo. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)John
Then how what do I need to do to get the Actual.
Arshad
Run the query in Management Studio with the "Include Actual Execution Plan" button selected. Or run the query after having run SET STATISTICS XML ON, save the resulting xml file as a .sqlplan file and open it in SSMS.
Yeah I know that option in SSMS . I am asking for a way to do this without knowing the query itself , that is , for all the code that is currently running on the production database .
Arshad
Upgrade to SQL Server 2016 and enable Query Store. In other versions, you're stuck with the aggregate data (minimum, maximum and last) that you get in sys.dm_exec_query_stats.
John
August 14, 2017 at 2:22 am
John Mitchell-245523 - Monday, August 14, 2017 2:20 AMArsh - Monday, August 14, 2017 2:13 AMGilaMonster - Sunday, August 13, 2017 6:00 AMArsh - Sunday, August 13, 2017 4:43 AMJohn Mitchell-245523 - Friday, August 11, 2017 9:07 AMNo. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)John
Then how what do I need to do to get the Actual.
Arshad
Run the query in Management Studio with the "Include Actual Execution Plan" button selected. Or run the query after having run SET STATISTICS XML ON, save the resulting xml file as a .sqlplan file and open it in SSMS.
Yeah I know that option in SSMS . I am asking for a way to do this without knowing the query itself , that is , for all the code that is currently running on the production database .
Arshad
Upgrade to SQL Server 2016 and enable Query Store.
Nope. Even Query Store is estimated plans and aggregated stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2017 at 2:23 am
Arsh - Monday, August 14, 2017 2:13 AMGilaMonster - Sunday, August 13, 2017 6:00 AMArsh - Sunday, August 13, 2017 4:43 AMJohn Mitchell-245523 - Friday, August 11, 2017 9:07 AMNo. If you're getting the plans by querying the DMVs for the plan cache, those don't have the actuals in them at all. They never do. They're compiled when the query is first executed, and don't change until they're evicted from the cache. (There's a bit more too it than that, but I don't think that's too much of an over-simplification for your question.)John
Then how what do I need to do to get the Actual.
Arshad
Run the query in Management Studio with the "Include Actual Execution Plan" button selected. Or run the query after having run SET STATISTICS XML ON, save the resulting xml file as a .sqlplan file and open it in SSMS.
Yeah I know that option in SSMS . I am asking for a way to do this without knowing the query itself , that is , for all the code that is currently running on the production database .
Arshad
You can use Extended Events, but the event is a *very* high overhead event and not one that I'd recommend you use on a production server. If you do, keep the session running for a very short duration and watch for negative impact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply