1

I have a very simple query 

SELECT count(*) FROM MyTable

There are 508,000 rows in it. When I run the above query for the very first time it takes approximately 53 seconds to return the result. If I rerun the query again, it takes milliseconds to return the result.

I believe SQL Server is caching the query/results? Is it possible that I could tell SQL Server not to cache query/results, or somehow clear the cache/result?

In my application I am trying to do some performance tuning, but problems like above don't help me out.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Imran Zahid
  • 25
  • 1
  • 7

1 Answers1

2

Yes, you can flush the data buffer like this (but seriously, don't!):

DBCC DROPCLEANBUFFERS

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

Ref

You have already ruled out the database as your bottleneck (which it is often), so rather than freeing the data buffer cache, I suggest you profile your code.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • thanks for the option. I read in many places not to use this method just as you have mentioned. To profile my application code I had to clean the buffers to see which were the bottle necks. Also is it safe to presume that databases WILL cache based on some algorithms to give better performance, thus I shouldn't worry on these things? Rather than running cold, I should always test application/code on warm running servers, correct? – Imran Zahid Dec 29 '13 at 06:07