SQL Server : same query running multiple times with different execution time

Question

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.

Answer 1

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.