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

Continuing from the last post we now are going to look at what happens with the estimate plan when the plan in cache is invalid.

The specific example we will look at is where the plan in cache has become invalidated by dropping and re-adding a constraint on table that is part of the plan.

We will be using the same database ( AdventureWorks2012 ) and stored procedure ( spGetPersonByType ) from the previous post.

First we blow away the plan cache with DBCC FREEPROCCACHE.  Then we will run our stored procedure so we put a plan in cache.

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

Now we have this plan in cache.


So now if we pull an estimated or actual plan for this stored procedure we will get this one with the Nested Loops join.

Next, drop and re-add an existing constraint on the Person table. We will do it on the constraint that determines the valid values for the PersonType column.

EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'CONSTRAINT',@level2name=N'CK_Person_PersonType'


ALTER TABLE [Person].[Person] DROP CONSTRAINT [CK_Person_PersonType]

ALTER TABLE [Person].[Person] WITH CHECK ADD CONSTRAINT [CK_Person_PersonType] CHECK (([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC')))

ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_PersonType]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [PersonType] is one of SC, VC, IN, EM or SP.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'CONSTRAINT',@level2name=N'CK_Person_PersonType'

Now let us pull the estimate plan for the following.

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

Here we get the Merge Join plan.   This was what I expected to see since the plan in cache had become invalidated it doesn’t use the plan in cache and uses a new one as if there wasn’t one in cache.


So now lets pull an estimated plan for the following.

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

The plan that comes back from this is a surprise to me.   We get back the Merge Join plan again.   In the previous post we saw that when there is no cached plan, the estimated plan will come back with the plan that would be generated for those specific  parameters.   Which in this case the @PersonType = N’sp’ would generate a Nested Loops join plan.

Here we will actually run the stored procedure using the value of ‘sp’ for the parameter with the include actual execution plan setting turned on (Ctrl + M).

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

We see that the actual execution plan that is generated is now back to the Nested Loops join plan. Which is good because it means that the actual execution plan was not stored in cache when the estimate plan was pulled earlier. Though we do see that in subsequent pulls of the estimate plan, it will continue to bring back that same plan no matter what the parameter values are sent in.

This is strange behavior, especially looking at what we saw in the previous post. I plan to look into this farther and may revisit it in a later blog post. If you have any insights on this behavior feel free to comment below.

One thought on “The estimated query plan and the plan cache (Part 2)

  1. Pingback: SQL New Blogger Digest – Week 2 | The Rest is Just Code

Leave a Reply

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

You are commenting using your 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