The estimated query plan and the plan cache (Part 1)

While looking at execution plans and parameter sniffing.   I started to wonder about estimated query plans and what differences there were if there was a plan already in cache or not for that query.

I already knew that if I ran estimated plan that it would not store a plan in the cache from previous reading.  Then I started to wonder how the estimate plan worked if there was a plan in cache versus no plan in cache .

So first let us add this stored procedure to the AdventureWorks2012 database.


CREATE PROCEDURE spGetPersonByType
 @PersonType NCHAR(2) -- Types IN (18484), EM(273), SP(17), SC(753), VC(156), GC(289)

AS

BEGIN

SELECT       p.[FirstName],
             p.[LastName],
             p.[PersonType],
             e.[EmailAddress]
 FROM       [Person].[Person] p
 INNER JOIN [Person].[EmailAddress] e 
           ON e.[BusinessEntityID] = p.[BusinessEntityID]
 WHERE p.[PersonType] = @PersonType

END

Next we will clear out the plan cache using DBCC FREEPROCCACHE. This way we can see the behavior of estimated query plan when there is no plan in cache.


DBCC FREEPROCCACHE

Now we will highlight the following statement and not run it but use Ctrl+L to bring up the estimated query plan.

EXEC [dbo].[spGetPersonByType] @PersonType = N'sp'

We get back the following execution plan:

EstimatePlan1aWe get a simple plan with a nested loop join. Now if we look at the properties of the select we will see a few things. One is that the Parameter Compiled Value for our single parameter was ‘sp’. Also the other thing to note is the RetrivedFromCache comes back as false. Both of these is not surprising but still lets me know that this plan that is generated is the same as if I ran the proc for the first time using ‘sp’ as the parameter value.

EstimatePlan1b

Now we will get the estimated query plan for the same proc but parameter value. Again we don’ want to actually run this procedure yet.

EXEC [dbo].[spGetPersonByType] @PersonType = N'in'

We see that we get a simple plan again but with a merge join instead of a nested loop join.

EstimatePlan2a

If we look at the properties of the select we see something very similar to the one before.  The  Parameter Compiled Value has change from ‘sp’ to ‘in’.  Which is because we changed the parameter value passed in.   The RetrivedFromCache still is false.  The query hash is the same but the query plan hash is different.   This is because the query is the same just with different parameters but because of the different values of the parameters the plan that is generated is different.

EstimatePlan2b

Now let us actually run the following statement.   Then after it is runs  pull the estimated plan for the statement

EXEC [dbo].[spGetPersonByType] @PersonType = N'sp'

We get the same estimated execution plan with the nested loop joins as we did previously with this statement

EstimatePlan1aWhen we look at the properties we see one slight difference.   Now the RetrivedFromCache is true.  Since we actually ran the statement a plan got put into cache.   Then we pull the estimate plan we were able to retrieve it from cache without having to actually generate a plan.

EstimatePlan3b

Now let look at the estimated query plan for the statement with ‘in’ as the parameter value.

EXEC [dbo].[spGetPersonByType] @PersonType = N'in'

So now we no longer get the merge join plan for this statment we get the nested loop join plan.   Again as we see the same properties as before with the key being the Parameter Compiled Value is ‘sp’  which make a the nested loop plan.   Even thought the statement has ‘in’ as the parameter value.

EstimatePlan3bSo while this might not be surprising behavior.   I just wanted to not take it for granted and take a look at what all is happening more in depth.

That will do it for part 1 on this topic.     Next week I will look at if the plan get invalidate by updated stats or schema.   Before the plan gets rebuilt with a actually run of the query will the estimated plan pull the old one from cache or generate a new one that won’t be stored in cache.

2 thoughts on “The estimated query plan and the plan cache (Part 1)

  1. Pingback: SQL New Blogger Challenge Weekly Digest | The Rest is Just Code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s