SQL Server provides performance tuners with two main methods of reviewing query performance, the estimated and actual execution plans. The estimated plan can be retrieved quickly, since the query doesn’t have to be executed. Alternatively, the actual plan will require you to execute the query entirely but it’s often worth the wait. The estimated plan is only as good as the estimates used to generate it, so as performance tuners, we want to review what actually happened.
So here’s the catch
In the real world, when you’re troubleshooting an issue, waiting for the entire query to execute is painful. If the query takes over 30 seconds, I know I’ll start to get impatient and I’ll get curious about what’s taking so long. This is where the estimated plan comes into play. While we’re waiting for our actual query to execute, let’s talk about one big reason we’d want to retrieve the estimated plan: parameters.
Parameters?
Yes, the parameters that were used to compile the plan. When SQL Server generates an execution plan, it records the parameters that were used. Here’s another tricky part: those might not be the same parameters that you’re using to test the query. Let’s take a look at an example using the Stack Overflow database.
10 second introduction to StackOverflow2010
(Download StackOverflow2010 here)
In the world of Stack Overflow, the Posts table has all the questions and answers from the site. There’s a lot of those questions and answers, but there’s just a few wiki type posts.
Here’s my stored procedure to retrieve all the posts of a certain type:
CREATE PROCEDURE [dbo].[QueryPostType] (@PostType INT)
AS
BEGIN
SELECT PostTypeId,
Tags,
Body
FROM Posts where PostTypeId = @PostType
END
Now to actually test this, we need to run the stored procedure once.
EXEC QueryPostType @PostType = 1;Alright. Executing the query with the parameter for the questions, post type of 1, should put that plan into the plan cache. Let’s take a look at the estimated plan for a different parameter, 3, which is the wiki post type.
EXEC QueryPostType @PostType = 3;
Just right click on that “SELECT” and look at the parameters. Here’s the useful part:
That’s all I have for this reason and this post.
I just wanted to provide the parameters in defense of estimated plans. This trick becomes really useful if you’re trying to determine if your query is a victim of parameter sniffing, since you can see what parameters were sniffed and placed into cache.