The ability to view execution plans is a critical part of any successful troubleshooting effort. It is in the execution plans that you can find a great deal of information.
Just a sample of what can be found in execution plans.
-
-
- Data access type – Scan or a seek
- What indexes were used
- What type of physical join type – Hash Match, merge or nested loops
- If key lookups are being used
-
Of course this is a very small sample. Even in this short list, you can see the value in execution plans. Given the value of execution plans, it is important to understand how these can be captured. There are a number of methods that can be used.
SQL Server Management Studio is an effective method for capturing a single plan. When you have a query you would like to see the plan on you have two options, estimated and actual. The differences is pretty simple, estimated is what SQL Server thinks it will do while the actual execution plan is what it actually did to get the data you are requesting.
To get the estimated execution plan, click the “Display Estimated Execution Plan” under the Query menu. At this point, SQL Server will get all the information it needs to create the plan, however will not actually execute the query. Statistics are a key part of this and should kept up to date.
When getting the estimated plan, SQL Server does not actually execute the query. However, when the actual plan is used, SQL Server does execute the query. To get the actual execution plan you can do one of two things, click the toolbar icon highlighted below and execute the query. Or you can click the menu item under the Query menu and then execute the query.
As mentioned this method works great if you have the query and will capture the plan for a single query. However, what if you want to capture the execution plans over a period of time.
Within SQL Server you have two methods that can be used to accomplish this. One is Query Store and the other is Extended Events. Since this is a post about Extended Events, here is some good documentation on Query Store, click here.
When using Extended Events to capture the plan you have two options.
query_pre_execution_showplan – this is the estimated plan
query_post_execution_showplan – this is the actual plan
Either one of these should work. Once you have chosen the event, the XML for the plan can be found in the “Event Fields” tab and the showplan_XML column.
The easiest way to view this is to use SSMS. If you navigate to the target and right click on the target.
In this case, right click on the event file and then click “view target data”. When you do you will then you will see this. If you look towards the bottom you will see the Query Plan tab. Once you click this, you will then see the plan.
What is missing? If you look at the two plans below you will see a noticeable difference. The SELECT operator is missing on the top one. The top plan is from Extended Events and the bottom one is from SSMS.
Missing the SELECT operator is a big deal. There is a great deal of information that can be found here. This alone would be a good reason to not use Extended Events to capture execution plans.
If that alone isn’t enough to avoid using Extended Events to capture execution plans, take a look at this warning that can be found in the description of the two events mentioned above.
“Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.”
So if I shouldn’t use Extended Events to capture the execution plans, what should I use? The answer is Query Store.
Thanks for stopping by my blog and I hope you learned something. More importantly, you learned what not to use!