Testing query speed using non-cached data?

Question

SQL Server 2014. A query I have takes 44 seconds to run the first time I run it. After that it takes 6 seconds. I'm sure this is because the data is cached in memory (and possibly because the query plan is cached as well). I want to find ways to speed up my query so the first time it's taking 6 seconds, but it's hard to test that when everything is cached.

How can I force my query to NOT used cached data? Or to put it another way, how can I force my query to run like it's the first time every time?

I tried adding option recompile but that didn't make any difference.

Thanks!

Answer 1

You seem to be looking for SQL Server DBCC DROPCLEANBUFFERS command. From the documentation:

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

This command can be used in conjonction with DBCC FREEPROCCACHE:

Clearing the procedure (plan) cache causes all plans to be evicted, and incoming query executions will compile a new plan, instead of reusing any previously cached plan.

Disclaimer: please consider the impacts of such command before running it; this should be used for testing purposes only, in non-Production environments!