SQL Server 2008 – Force Index to Stay in Memory

buffer-poolindexperformancesql serversql-server-2008

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 ?

Best Answer

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:

http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

Related Question