SQL Server – How to Do a SELECT Query Without Optimization

database-designsql server

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?

Best Answer

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

Related Question