CODEWITHSUNDEEP
sql-serversql-server-2008
Dustin Davis
Dustin Davis

Reputation: 14585

SQL - clearing execution cache

I'm trying to profile a few queries but as you know, when you run a query a second time, it comes back in 0ms. I'm using 

DBCC FREEPROCCACHE

but that doesn't seem to do the trick. what else can I run to clear any trace of execution/results cache?

Upvotes: 4

Views: 869

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

You'll also want to use DBCC DROPCLEANBUFFERS. This will test the queries with a cold buffer cache.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453067

CHECKPOINT;
DBCC dropcleanbuffers;

This should not be run on a production server though. CHECKPOINT is a database scoped command that will write the dirty buffers to disc so they will be affected by the next command but DBCC dropcleanbuffers is global and all data pages dropped from the buffer cache in this manner will need to be read in from disc when used next time.

Upvotes: 4

Related Questions