Running Microsoft SQL Server 2005 on Windows 2003.
We are running some fairly complex queries that only return 5 to 100 results.
The first time the query is executed it takes 3-6 seconds (depending). The next time the same query is executed it only takes 1-1.5 seconds.
After a few minutes the cache seems to be cleared and the same query takes 3-6 seconds again.
How can we extend the lifetime of the query cache so we get the 1-1.5 second execution times for more than a few minutes?