Cleaning the cache for a Query.

Question

Cleaning the cache for a Query.

T.Ashish

SSCarpal Tunnel

Points: 4248
More actions
June 18, 2013 at 5:35 am

#275315

Dear All,
While testing a query when we run it second time, it uses cache so performs better then previous run.
Do we have any way to clean cache of a particular query without affecting cache of other queries?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

If it's a stored procedure you can use sp_recompile proc_name.

I'm not sure about removing a single query from the plan cache. Perhaps you could put the query in the stored proc and specify WITH RECOMPILE in the stored proc.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Just did some testing on this - It seems you can remove a single plan from the cache, if you know the plan handle.

DBCC FREEPROCCACHE (plan_handle)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

T.Ashish (6/18/2013)
Dear All,
While testing a query when we run it second time, it uses cache so performs better then previous run.
Do we have any way to clean cache of a particular query without affecting cache of other queries?

You mean data cache? If so, then no - the data read and cached during execution of your query is available to other queries.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

T.Ashish (6/18/2013)
Do we have any way to clean cache of a particular query without affecting cache of other queries?

Why do you want to?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ryan.mcatee SSC Eights! Points: 860 More actions · Answer 5

You could DROPCLEANBUFFERS, but I would probably not do that on a production SQL instance. Obviously, there are a lot of factors at play in a decision like that.

My first thought is- why not look at the execution plan and see just how the query optimizer intends to satisfy the query and go from there.

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 6

I want to do this because when I check performance of a query and I need to run it again and again, I can't clean everything, because I'm not the only user on database.

For my local copy I can use these two commands :

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

And WITH (RECOMPILE) works only for Stored Procedure.

For query editor we have OPTION (RECOMPILE), but even with this option it seems that SQL is using cache.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

DBCC DROPCLEANBUFFERS with clear the entire clean part of the buffer pool.

DBCC FREEPROCCACHE will remove all plans from cache

If you have the plan_handle of the query, you can remove that single plan from cache.

There is no way to only remove the pages in the buffer pool related to the query

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

T.Ashish (6/18/2013)
I want to do this because when I check performance of a query and I need to run it again and again, I can't clean everything, because I'm not the only user on database.

So don't clear anything. Run the query multiple times and ignore the first execution time and characteristics as that's the one that incurs the caching cost.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass