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.

  • 2
    So if two different queries that require the same data are running, you want one of them to read from memory, and one of them to force the data to disk first? Typically, this is NOT how these queries will run in real life - if they are run often enough, the data will already be in memory; if they aren't run often enough, who cares? – Aaron Bertrand Oct 20 '15 at 19:22
  • @AaronBertrand - Unfortunately the manager complaining about the query cares, so I'm trying to improve the execution time when it hits disc also. – MAW74656 Oct 20 '15 at 19:31
  • I only want to force it to hit disk for testing, not forever. I guess I didn't explain that very well. – MAW74656 Oct 20 '15 at 19:33
  • For tuning how many pages will be accessed, using "set statistics io on" is usually a better measurement than time – James Z Oct 20 '15 at 19:51
  • @JamesZ - I'm not concerned with how many pages, just whether or not they're in memory. I ran the query again this morning, back to super slow the first time, then quicker. That means when my manager runs it it will likely be dog slow. – MAW74656 Oct 21 '15 at 18:48

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


To not use cached plans you could add option(recompile) to the query. I dont think there is a similar option for pages in memory. You could use DBCC DROPCLEANBUFFERS but this will clear all pages in buffer not just for your query. Do not run this in production.

Ideally you would want all the data in memory to tune the query because that is the most likely scenario unless the query is reading rarely used data.

  • I think so too, but is there some way for me to confirm that these particular pages will still be in memory? This is likely a once a week query on demand, so I'm thinking I need to tune it when hitting disk, then if its in memory I'm even better. – MAW74656 Oct 20 '15 at 19:29
  • 2
    @MAW74656 The first time you run you query, all the data pages access will be loaded into memory. They will stay in memory until they are pushed out by other data pages, drop cleanbuffers, or restart. If you are having the data pages pushed out of memory very quickly during your testing then i would suspect you have a memory shortage. you can verify what is in the buffer using sys.dm_os_buffer_desrciptors. – Bob Klimes Oct 20 '15 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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