In one of my applications I have a 1gb database table that is used for reference data. It has a huge amounts of reads coming off that table but there are no writes ever. I was wondering if there's any way that data could be loaded into RAM so that it doesn't have to be accessed from disk?
I'm using SQL Server 2005
You're going to want to take a look at memcached. It's what a lot of huge (and well-scaled) sites used to handle problems just like this. If you have a few spare servers, you can easily set them up to keep most of your data in memory.
http://en.wikipedia.org/wiki/Memcached
I'm no SQL guru, but wouldn't SQLServer notice all those reads, and cache the table in ram itself?
As Blorgbeard said, if you have enough RAM, SQL will do an outstanding job determining what to load into RAM and what to seek on disk.
This question is asked a lot and it reminds me of people trying to manually set which "core" their process will run on -- let the OS (or in this case the DB) do what it was designed for.
If you want to verify that SQL is in fact reading your look-up data out of cache, then you can initiate a load test and use Sysinternals FileMon, Process Explorer and Process Monitor to verify that the 1GB table is not being read from. For this reason, we sometimes put our "lookup" data onto a separate filegroup so that it is very easy to monitor when it is being accessed on disk.
Hope this helps.
DBCC PINTABLE will explicitly pin a table in core if you want to make sure it remains cached.
Just to clarify the issue for the sql2005 and up:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.