7

Anyone with experience of using SQL 2008, but all in RAM? Trying to make certain things faster, and might pursue this, thanks

3 Answers 3

10

It depends what you mean by "all in RAM".

If you are wanting to run your database from a RAM drive you will find that this is unlikely to be possible (unless you have some sort of RAM drive card that looks to the OS like a normal disk, i.e. presents a SATA or PATA interface) as SQL Server will refuse. It assumes any data it writes is important enough to keep and will not risk it by writing it to a volatile storage medium. You might be able to fool it by running it in a VM and running that VM from a RAM drive, but I would not recommend trying this both for the same reason (you loose all the data if a power-loss or reboot situation occurs) and due to the fact there would be a collection of other performance hits from the virtualisation solution to worry about.

If your problem is slow read activity (i.e. a lot of operations that result in reading from disk, then just increase the amount of RAM in the machine to be larger than the usual working set or better still larger than the whole database. This way, unless you have an edition of SQL Server that imposes artificial RAM limits (IIRC Express Edition will not use more than 1Gb), it will use the abundance of memory to keep anything it reads cached in RAM where it can be read quickly next time it is needed. If you wish to pre-load the data into RAM any time after it is cleared (i.e. after a reboot due to installing updates) then you can write a script that forces all index and data pages to be scanned and therefor read into memory.

If the problem is due to many write operations, then optimising your disk layout may help. Without knowing more about your database we can't give specific hints, but the standard rules of thumb (like try keeping your data files, transaction logs and tempdb on separate physical drives) might help.

8

By default SQL Server will use all the available RAM in the server. Best practice is to configure the SQL Server is have 2-4 Gigs less RAM available than the server actually has (depending on what else is installed, how much SQL CLR you are using, etc). The SQL Server will make use of the memory in the server as the buffer cache so that the data needs to be read from disk as infrequently as possible. The more RAM the SQL Server has available to it the more data that the SQL Server can load into memory. In a perfect work the SQL Server will read from the disk almost never (except on start up as the data has to be read from disk as some point) so that the only disk IO which is needed is write IO.

2
  • Agree. Also, from my (erm, sorry) playing around experience: caching the results of the most time intense queries into a separate (therefore smaller) table and pointing the following identical queries to it can help too (e.g. if you have a 200GB DB and not that much RAM, e.g. 20GB). The chance that the cache-table is still contained in the RAM is much higher then. I admit that this works imho only if you have a very limited amount of users working against your database. Mar 18, 2011 at 23:24
  • You'll actually have the data in cache the same amount of time. The data will take less space in the cache so it may appear to be in cache longer as you don't have rows and columns that you aren't looking for in cache which will save space. If users are running the same query over and over the data will remain in cache as SQL uses some pretty smart algorithm to decide which pages to age out of the cache.
    – mrdenny
    Mar 19, 2011 at 3:24
5

Running SQL Server on a RAM disk is just like configuring a machine's pagefile to be in RAM: it will break the expectations the system has.

SQL Server is actually a lot like an operating system, it has its own version of processes, threads, has its own memory manager, and other things. So what would happen if we run SQL Server on a RAM disk?

  1. We lose database consistency. If the server were to fail, we would lose data. SQL Server in its standard configuration will not lose committed data.
  2. We will end up using memory inefficiently, because SQL Server will use its buffer pool and various caches for data that is in RAM already. In the worse case scenario, you will limit SQL Server to less than 50% of the memory your system has. The majority would end up being used to store the database and/or log files, and SQL Server would still attempt to put things into its internal, optimized buffer pool and lack sufficient memory to buffer things.

The first is the most important. SQL Server on RAM disk will no longer have transactional consistency in the event of a failure. Nothing else should matter to you, but I can say with certainty that there are no technical benefits to running SQL Server's database or log files on a RAM disk that can possibly outweigh the risks.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .