I am trying to test the speed of SQL server queries. However, once I do the query once, it becomes a lot faster. The database is live so I can not make any changes to it, only to the SELECT query being performed.

Is there any way to do a SELECT query without SQL server optimizing it?

  • Any select query with proper index would always return result faster. You should focus on proper indexes
    – Shanky
    Aug 12, 2014 at 8:26
  • 1
    Why do you want the un-optimized speed of a query? What are you trying to figure out? (and Why?) Aug 12, 2014 at 13:18
  • @RBarryYoung I was testing datetime comparison queries to see which is faster, but this is not about one kind of query, it is a general question. I have also done a lot of testing recently where I work, which only involved searching in a web application. The data used to test how fast a query is, is only based on the server side script before and after SQL execution, and the testing is repeated for each condition multiple times. However after the first time queries get faster. Aug 13, 2014 at 5:49
  • @Shanky Yes, indexing is always a big help, thanks. Aug 13, 2014 at 5:51

2 Answers 2


For the query plan itself, you can force compilation every time using the following option on your query:


However I suspect what is happening is that the second and subsequent executions of the query are pulling the data from memory instead of disk, and of course memory is faster than disk.

If you are trying to test the scenario where this data always comes from disk, there's not really a good way to do that on your production instance without affecting anything else, because you can only drop clean buffers for the entire instance at a time, not for a single database, never mind table. So what you could do perhaps is set up an instance on the same hardware, with just this table, and run the following before every run of the query:


However, this does not make a lot of sense, as ideally you will be querying data that is in memory, and you should be optimizing for the best case scenario. You should have some idea of what the worst case scenario is like, but again, this should not be the normal situation, unless you have a very big database and a very small amount of memory (in which case, open the wallet and buy more memory - it's much cheaper than optimizing for the lack of it).

  • Thanks. I seem to be getting the vibe from people that why I am asking isn't necessary, but then how would you confirm that a query is better? Aug 13, 2014 at 5:55
  • Damien, do a google search for query execution plans, that is where you fine tune queries. However, this is not a quick 30 min read and then you got it. Performance tuning is massive and specialised, which is why you have companies doing that only. However there are a few basic things you can do, like adding the right indexes and structuring your queries right etc...
    – Ryk
    Aug 13, 2014 at 11:43
  • @Damien in an ideal scenario you won't be pulling the data from disk every time you run the query - it will be in memory, especially if it's called often; if it's not called often, does it matter? In my experience most people test performance with a primed cache, not an empty one; if they want to test the effects of data moving in and out of the buffer pool (in cases where database size > memory), it is much more useful to test an actual realistic workload than it is to isolate one query and try to force it to come from disk all the time. It's cheaper and easier to just add more memory. Aug 13, 2014 at 13:30
  • @Damien And as an aside, might I suggest SentryOne Plan Explorer for helping optimize queries? It makes pain points much more obvious, collects statistics time and I/O for you automatically, collects the wait stats your session experienced, and index statistics / scoring sandbox. Disclaimer: I work for SentryOne. Aug 13, 2014 at 13:35

If you are trying to measure this by, say, running it in SSMS and looking at the elapsed time in the status bar, be aware that is also measuring the network time to transfer the results from the database engine to the client and can include a significant amount of network variability.

You could set statistics io on and set statistics time on. This will show how much work the DB engine itself is doing. Minimising these numbers, and analysing the query plan, should give faster-running queries.

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.