I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster (around 1s). After a few hours, a slow/then fast cycle starts again.

I've checked in my execution plan that all the needed index were present and appropriately used, and I assume the performance difference is due to the fact that the index are actually in memory for the subsequent queries (am I right, or are there other possible causes?)

I'm also running a lot of other queries using indexes as well, but those queries are less time-consuming and their performance is less critical, so I'm concerned those indexes are actually pushing my critical index out of the memory cache.

Apart from the obvious 'add more RAM' fix, I've been thinking about scripting dummy queries to run every hour to force the index back in memory.

Is there a more elegant way to do this? Like a way to hint SQLServer that if it only has enough memory to keep one single index cached, it should be that one ?

I know that usually the best thing is not to mess up wich SQLServer with regards to that kind of things, but the unusual nature of my query (runs very rarely, but time-critical) makes me believe it would make sense (if possible).

I'm also curious to know if there's a way to know which indexes are cached in memory at a given time ?


There used to be a DBCC PINTABLE command but I believe that stopped working in 6.5 or maybe 7.0. The statement will probably still suggest that it worked if you try it, but it just returns, it really is a no-op.

Unfortunately there's not really any way to control which indexes are kept in cache - the best workaround I know of for tables that are periodically hot is to keep them hot manually (which you've already described in your question).

For which indexes are in memory, you can get a rough idea from sys.sm_os_buffer_descriptors. I published a tip about this:


  • Hmm, according to that script, a 75 MB table is occupying 900 MB of buffer pool. Is that normal/possible?
    – db2
    Feb 15 '12 at 21:18
  • 1
    @db2 how many indexes do you have?
    – JNK
    Feb 15 '12 at 21:33
  • 2
    Also how fragmented is it... it is measuring pages, not data. Your pages may be relatively empty, and that can contribute to an inflated measure. Feb 15 '12 at 21:40

Try using KEEPPLAN and KEEPPLAN FIXED query hints .

KEEPPLAN forces the query optimizer to relax the estimated recompile threshold for a query.

KEEPFIXED PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.

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.