>>Yes - as long as you are doing something with the table to make the server need to access it, it should remain cached (at least somewhat which will make the next access much better). IIRC I had my scheduled app running hourly after normal business hours.
>
>Do you also know if this can be related to a recent backup? The backups are done at 01h00 and last for about an hour. But, that situation occurs two hours and 20 minutes later. We did not get any hit on that table however during that timeframe. So, I am wondering if a SQL Server backup can reset the cache. Do you know if that could have been a factor?
AFAIK the cache is pretty dynamic.
If SQL Server thinks something needs to get cached now and memory is not available, it will attempt to free memory by dumping older cached data.
I'm not 100% sure that SQL would try caching data based on a backup job, but I wouldn't be suprised.
So, my guess is that the backup could indeed cause table data to become uncached.
____________________________________
Don't Tread on Me
Overthrow the federal government NOW!
____________________________________