How to clean buffer cache without using DBCC command?

Question

How to clean buffer cache without using DBCC command?

SQL Guy 1

SSCoach

Points: 15979
More actions
November 26, 2018 at 9:10 am

#376481

My situation is that I don't have permissions to run DBCC dropcleanbuffers/freeproccache, but I need to find a way to indirectly clean cache on DEV server because I am tuning queries there and I need to know the impact of the changes, and I am the only one using that server.
I tried to set database offline/online but the latter one did not work, again because of my insufficient permissions.
Any other tricks that may cause cache to be cleaned?
Thanks
December 14, 2018 at 5:15 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

You shouldn't really need to completely flush the buffers. Comparing Logical I/O should be sufficient, since you can't really know what physical I/O will occur for any given query at any given time anyway. SET STATISTICS IO ON should give you that info.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Joe Torre SSChampion Points: 10248 More actions · Answer 2

If you have permissions to create a stored procedure you could use WITH RECOMPILE

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

inshah2297 - Friday, December 14, 2018 5:15 AM
You shouldn't generally need to totally flush the cushions. Looking at Coherent I/O ought to be adequate, since you can't generally recognize what physical I/O will happen for some random question at some random time at any rate. SET Measurements IO ON should give you that data.
SQL DBA,SQL Server MVP(07, 08, 09) Investigator James Blackburn, in shutting contention in the Deadly Vision murders preliminary:
On the off chance that later on, you should cry a tear, cry one for them [the murder victims]. In the event that later on, you should state a supplication, say one for them. What's more, if later on, you should light a flame, light one for them.

"SET Measurements IO ON"? :blink: Do you mean "SET STATISTICS IO ON"? And why are you copying Scott's post and then changing it with incorrect information?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

SQL Guy 1 - Monday, November 26, 2018 9:10 AM
I need to find a way to indirectly clean cache on DEV server because I am tuning queries there and I need to know the impact of the changes, and I am the only one using that server.

Don't. The query when running on production won't be running against a clean cache (hopefully), so don't test against one. Sure, you can get exaggerated improvements if you do, which looks good to management, but it's not realistic.

Run the query a couple of times, ignore the first execution as it will have the overhead of compiles and data load.

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