Actual Execution plan for query store

Question

is it possible to capture actual execution plan inside query store ? or else need to use back xevent ?

Answer 1

As noted already, Query Store is the estimated plan. The (at least one) reason for that is that QS aggregates data.

Say that it aggregates data over, one hour as an example. And a certain query plan was used 123 times over that hour. What would you want to see in the "actual plan"? An average over those 123 executions? That would still not be the actual plan. In order for QS to be able to get the actual values, it would have to have the option to store every single execution run-stats! Imagine the overhead! And I know you can, since you are questioning that overhead when you refer to the alternative: an XE trace.

Answer 2

Hi michaelchau,

In order to view the execution plan of a stored procedure or query, click the Include Actual Execution Plan button on the menu in Management Studio as noted in the screenshot below.
You can also use the keyboard shortcut Ctrl+M.

If the response helped, do "Accept Answer" and upvote it.

Best regards,
Cathy

Answer 3

Good day,

is it possible to capture actual execution plan inside query store ? or else need to use back xevent ?

The answer is yes for both options.

You can capture the EP in Extended Event using the event query_pre_execution_showplan (for estimated plan) and the event query_post_execution_showplan (for the actual plan). The SML of the execution plan is stored.

Regarding the query store, this is basically the main goal of the query store, so it is very simple and any tutorial on query store will show you how to do it

In both option you must prepare the tools in advance and you cannot capture the EP if you did not enable the tools first

For more information you can check the following two links and google can find several more

https://www.sqlshack.com/force-query-execution-plan-using-sql-server-2016-query-store/

http://davebland.com/extended-events-and-execution-plans

Answer 4

thanks.
You can capture the EP in Extended Event using the event query_pre_execution_showplan (for estimated plan) and the event query_post_execution_showplan (for the actual plan). The SML of the execution plan is stored.

making xevent to add event of query_post_execution_showplan will DRAG down the whole performance of the database, for database which are very busy ????