current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
    
Any select query with proper index would always return result faster. You should focus on proper indexes –  Shanky Aug 12 at 8:26
    
Why do you want the un-optimized speed of a query? What are you trying to figure out? (and Why?) –  RBarryYoung Aug 12 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. –  Damien Golding 2 days ago
    
@Shanky Yes, indexing is always a big help, thanks. –  Damien Golding 2 days ago

2 Answers 2

up vote 10 down vote accepted

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

OPTION (RECOMPILE);

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:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

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).

share|improve this answer
    
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? –  Damien Golding 2 days ago
    
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 2 days ago
    
@Damien the vibe is because 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... 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 even cheaper to just add more memory. –  Aaron Bertrand♦ 2 days ago
    
@Damien And as an aside, might I suggest SQL Sentry Plan Explorer for helping optimize queries? It makes pain points much more obvious, collects statistics time and I/O for you automatically, and the PRO version even collects the wait stats your session generated. Disclaimer: I work for SQL Sentry. –  Aaron Bertrand♦ 2 days ago
up vote 3 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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