I recently released a Pluralsight Course Analyzing SQL Server Query Plans, and it is really doing great in terms of viewership and I have received some really great comments and feedback about this course. This week, I will be discussing different aspects of Analyzing SQL Server Query Plans. Today we will discuss How to Compare Estimated and Actual Query Plans and Related Metadata.
Three Kinds of Plans to Compare
In SQL Server now we have three different kinds of plans to compare. Here is the blog post I suggest you read it before continuing reading: 3 Different Ways to Explore Actual Execution Plans. Whenever I am hired to help the customer with the Comprehensive Database Performance Health Check and if they have a problem with query plans, I always tell them to Save and Send Execution Plan Via Email.
Actual Execution Plan
When SQL Server generates an execution plan running an actual query, considering available resources. It is only visible after the query is executed and it contains many of the actual execution data. Along with actual execution data it also contains few estimated data as well so in true sense we can’t just say that it is containing 100% of the execution plan. Another important thing is that it also considers the currently available resources to your query and also display appropriate warnings if there is any. You can enable an actual execution plan with the shortcut key CTRL + M. If you are using SSMS, you can enable it via either of the following commands.
SET STATISTICS PROFILE ON
SET STATISTICS XML ON
Estimated Execution Plan
The most probable plan the engine is likely to use when SQL Server generates an execution plan without running an actual query and display. They are definitely quicker as they do not have to execute the whole plan. Additionally, there is no real execution data along with it and no resource consideration to build an execution plan. With that said, I am still a big fan of this kind of execution plan when my queries are taking long time to execute and I can use the estimated plan to see what is going on without actually running the query. The shortcut key for displaying this plan is CTRL + L. If you are using SSMS, you can enable it via either of the following commands.
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
Live Query Statistics
The live query statistics display real-time insights into the query execution process as well as the control flows of the operator. They are only visible while query is executing and not available before or after it. Well, that is why they are called Live Query Statistics. If you are using Activity Monitor (not my favorite tool), you can also see the live query from it as well. As it is running live, it contains real information about the execution plan. There are no shortcuts available for this one. While, this works great most of the time, there have been some bugs reported in the past with some queries. Personally, I use it quite a lot of time to explain how queries work to my clients.
Pluralsight Course Analyzing SQL Server Query Plans
In the Pluralsight Course Analyzing SQL Server Query Plans, I discuss how to Create Efficient Query Plans Using Query Store. I explain to you how you can implement your query store and use it later on to identify the troublemaking query plans. The course is of only 2 hours and 30 minutes and if you have a Pluralsight subscription, you can watch it for free. If you do not have a Pluralsight subscription, you can still watch the course for FREE by signing up for a trial account.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
