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?


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

  • Just to make sure: you're doing this on a dev box, right? Those aren't the kind of commands you want to be running in prod to troubleshoot slow queries, because they'll likely make other stuff slow. Commented Sep 13, 2017 at 11:42
  • @sp_BlitzErik Correct. I want to try different approaches (indexes, query rewrites...) and find out whether they work. Commented Sep 13, 2017 at 14:55

1 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?

  • 2
    I have seen excessively slow compile times in some cases with the new CE and a query with many joins but that doesn't apply to SQL 2008R2.
    – Dan Guzman
    Commented Sep 13, 2017 at 12:31
  • May not actually be compile-time but buffering the tables to memory.
    – Daniel
    Commented Sep 13, 2017 at 13:30
  • The initial slowdown becomes 100% reproducible if I add DBCC FREEPROCCACHE to the cocktail. I also get SQL Server parse and compile time: CPU time = 37671 ms, elapsed time = 37920 ms. (otherwise, that info doesn't show up for the first time). Commented Sep 19, 2017 at 15:36
  • @DanGuzman Funny, I have that problem as well (tons of joins here) with newer versions. Is there a "simple" fix apart from setting database compatibility to SQL Server 2008 (100)? I have the 43-page Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator document here but I can't grasp any meaning from it. Commented Sep 19, 2017 at 15:42
  • @ÁlvaroGonzález, SQL 2016 introduces ALTER DATABASE SCOPED CONFIGURATION so you can set the CE independently of the database compatibility level and without trace flags. A lot of joins suggests a complex query, not necessarily a CE bug. A specific repro script with DDL and query will help us help you.
    – Dan Guzman
    Commented Sep 19, 2017 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.