Properly test queries that are slow on first run

Question

How can you test a query that's only slow on first run?¹ Are CHECKPOINT and DBCC DROPCLEANBUFFERS the right tool? If so, is running this as sysadmin all that's needed or there's something else involved?

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

I'm asking before I can't make the query slow any more, unless I restart the SQL Server instance (which is of course painfully slow).

_{(1) Nothing subtle: 35 s to 51 s on first run, around 0.02 s afterwards}

Answer 1

Restarting Sql Server blows away the plan cache so the first time the query is executed, a new execution plan has to be built. Check out Eight Different Ways to Clear the SQL Server Plan Cache.

Alternatively, you could try altering the text of your query by adding (or removing) some extra spaces or blank lines which should force a new plan to be built.

However, I'd be quite surprised if it took 35 to 51 seconds to build a new plan.

You can verify whether building a new execution plan is part of your extended execution time by issuing set statistics time on in a query session before you execute your query against an empty plan cache (or where you've made a slight change to the query text such that a new execution plan is required). In the SSMS messages window, look for something like this:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

Additionally, have a look at Why is My Query Faster the Second Time it Runs?