CODEWITHSUNDEEP
sql-serversql-server-2005caching
lomaxx
lomaxx

Reputation: 115773

Load readonly database tables into memory

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

Upvotes: 1

Views: 3777

Answers (4)

Portman
Portman

Reputation: 31975

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.

Upvotes: 5

Goran
Goran

Reputation: 6846

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.

Upvotes: 1

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66612

DBCC PINTABLE will explicitly pin a table in core if you want to make sure it remains cached.

Upvotes: -1

matt b
matt b

Reputation: 139921

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

http://www.danga.com/memcached/

http://www.socialtext.net/memcached/

Upvotes: 2

Related Questions