We have a beefy Windows 2008 x64 server (4 x 4 core CPU, 32GB RAM) running SQL Server 2005 64-bit. We have a small (6GB) but very important database that is somewhat slow to access until the pages are cached in memory (the usage is very much random I/O so the odds are very low a given page is in memory and the end users complain about the initial slowness). The disks are fast enough (local 15K SAS) but I guess the app is somewhat clumsily written (it's a COTS solution) so I am wondering if there's a way to "force" a database in memory in SQL Server 2005 (2008 is not supported by the vendor, so we shouldn't upgrade to that yet) to help avoid the initial cache-filling blues?
My current method is that I run a SELECT * from each table in a script to get data pages in memory but some objects (indexes, Full text search, etc.) are not cached by this method (and modifying the script to interrogate indexes and write appropriate WHERE clauses to cache is boil-the-ocean complex).
Best Answer
No, there's not a way to force a database into cache, unfortunately. Your brute force method is probably the most straightforward. You might be able to get closer by using index defrag scripts with a very low threshold setting, like saying rebuild the index if it's 1% fragmented, like this:
http://sqlserverpedia.com/wiki/Index_Maintenance
It'll take longer and involve more writes to the disk, but it'll have the side effect of defragging your indexes and updating statistics, which is a good idea anyway.