SQL Server – Ignore Pages in Memory for a Single Query

sql serversql-server-2008-r2t-sql

Is there some way to ignore both cached query plans and any data pages in memory for a single query or batch? Something like a table hint or other option which can be turned on just for the current connection?

I want to force this query to hit the disk, because I'm trying to tune it and the execution times are all over the place. Trying to reduce variables in play.

Best Answer

Using the OPTION (RECOMPILE) query hint will be your friend with regards to forcing a new plan each time you execute the query.

Preventing data being read from the cache is impossible. However, you can clear the cache, assuming you're not using the production machine for these tests. Clearing the cache will force data to be read from disk; do this prior to running the query each time. DO NOT DO THIS IN PRODUCTION since you will cause a very quick and noticeable drop in performance for all queries running against the server.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. You may want to run the CHECKPOINT command first to ensure all dirty pages are written to disk first.

Clearly, you're going to want to look at the actual execution plans; do that by clicking "Query -> Include Actual Execution Plan" from the menu in SSMS (or press CTRL - M prior to running the query)

You may also want to turn STATISTICS IO, TIME ON at the start of your session to get better diagnostic info from the server. Turning on "Client Statistics" is also a great tool to see the impact of SSMS on your results. Hit the "Query -> Include Client Statistics" menu, or press Shift - Alt - s

Related Question