Transcript

There may be times when you want to bump the query execution plan out of the cache in SQL Server.

If you do this, the next time the query runs, it will compile a fresh execution plan, so you need to be careful exactly who you bump out of cache and how many things you bump out of cache.

You can do this in a very granular fashion

If you know the specific query that you’re looking for, you can take a fragment of the query and look in the dynamic management views in SQL Server. What we’re looking for is looking for the plan handle, or the SQL handle, of that query ‘cause you can use either one of those pieces of information and say, hey SQL Server, throw this plan out of cache.

So we’re going to run this query and look for a query that I run a few times against my test instance. I can see the plan handle for it, the SQL handle, I can click on the query text to make sure that I have really found the query text that I was looking for, and that I’m not just going to throw the wrong plan out of cache. I can see, for the execution plan in cache, how many times it’s executed and statistics about the CPU time and how many reads it’s done total.

And I can also click on the query execution plan to open it up and review it in a new window. One convenient thing about this is it does give me the plan, if it’s part of a stored procedure, it gives me the plan for the whole stored procedure so I can confirm at the top if this is the procedure I was looking for. Of course, maybe the plan I’m looking for isn’t even part a stored procedure. It just is in this example.

So I’m going to bump this plan out of cache. I’m going to go back to the left and grab, in this case I’ll grab the plan handle. I copied that to my clipboard, and I just take the plan handle, which is a beautiful a beautiful thing, right? I throw that into DBCC FREEPROCCACHE, and I’m saying hey, I want you to find the plan with this handle and out it goes. So I run DBCC FREEPROCCACHE with this specific handle and now, if I run my query again, looking in the dynamic management views, it is nowhere to be seen.

I booted this out of cache and it hasn’t re-run yet, so I don’t have another plan in cache for it yet.

There are other ways to do this, too.

SQL Server has a built-in procedure called sp_recompile

I can run it against my stored procedure, and say the next time someone runs the stored procedure, compile a fresh plan. You can also run sp_recompile against a table, and say anyone who uses this table, they all need to get a fresh plan the next time they run. Maybe that’s in five minutes, maybe that’s tomorrow.

You do have to be careful, particularly when I run this against tables. This does require a high level of lock against the table, and if you run this against the table on a busy system, you can cause a large blocking chain where you have to wait for your lock, and then a bunch of things pile up behind you, having to wait for a lock too.

So be careful if you do choose to use sp_recompile. It is less granular than getting the specific handle of the plan you want to throw out of cache, but still, you know, in my case, it’s saying okay only this one procedure I’m going to force recompiles on that and it gives this a specific message that says hey, I marked this for recompilation.

Those are the two that I personally find the most useful. There are lots of other things you can do.

If you use resource governor pools, which not a ton of people do but sometimes folks do, you can take the pool names that you have defined and you can clear all of the queries that are using that pool

Now, I haven’t configured resource governor so I just have, you know, the built in pools and this default pool is pretty much, it is all my user queries. But I am able to say, okay, I want to clear everything in this resource group, which in my case is all of my user stuff. But right, I can do that. It says hey, I completed this. So that’s kind of a big, a big option there.

In SQL Server 2016, we have a new option…

…which I think is pretty cool, particularly just for test instances and demos. I can say I want to clear the procedure cache from my current database using the alter database scoped configuration.

And I think this is, particularly just for learning, and if you’re looking at your plan cache, if you’re working on your test instance and you don’t want to impact other databases, I think this is a nice option that we got there using the database scoped configuration.

You can do something similar on previous versions of SQL Server, but it requires knowing the database ID. Instead of just working on the current database, I need to say, okay, what’s the database ID for the database where I want to clear the cache? In my case it’s the current database, so I’m just going to do SELECT DB_ID, and I’m database ID seven.

Knowing that, I can run DBCC FLUSHPROCINDB, it just rolls off the tongue, and clear the procedure cache for that database if I want to do that.

Of course, there is the nuclear option

It’s not a lot more nuclear than clearing out my default resource pool, because in my case that’s pretty much everything, but I can say I want everyone, everyone gets a new plan. You get a new plan and you get a new plan, and you get a new plan, and clear out the entire procedure cache. If my instance isn’t very busy, this may not be a huge deal. Hopefully, when everyone compiles a fresh plan, hopefully they get a good plan, but compilations can happen naturally over time too.

If I have a really busy instance where lots of queries run at the same time, this may cause a big burst in CPU, as all of these simultaneous queries have to generate a plan.

If I have an instance where some queries get really involved in compilation and they can take a while to compile, usually this isn’t the case, but some systems have very special queries where it takes a lot of CPU to compile them, more than usual, then that could also cause the burst even if it isn’t really busy.

So you’ve got to know your system, and know that if you do clear out the entire procedure cache there may be some performance effect from that afterwards. For that reason, if you don’t know, or aren’t so sure, it is much safer to run a granular option like finding the handle of the query that you want to throw out, and then specifically putting that into DBCC FREEPROCCACHE and then monitor the performance of that query afterwards.