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 ?