I’ve faced pretty common situation recently – you need to work with huge data during development/testing. It’s good if you have option how to access large data volumes XXX% faster on your laptop. I can imagine many other scenarios when you need to work with hundreds thousands of records on your development machine. Great solution for that is to use RAM disk and store your database on it.

According to wikipedia RAM Disk “is a block of RAM (primary storage or volatile memory) that a computer’s software is treating as if the memory were a disk drive (secondary storage).”. It does what it does so you can slice part of your memory and create logical disk drive which is treated as drive on system level (by low-level drivers). SQL Server doesn’t care that this “disk” was created as part of your memory.

I used this tool to create RAM disk. It’s commercial but more stable than some free alternatives (I am using it also Visual Studio development).

This code performs simple 100k insert, I ran it on database stored on SSD disk at first, than on database stored on RAM disk (see results below):

-- create database on physical drive c:\ with default setting
CREATE DATABASE [TestPerformance] ON  PRIMARY
( NAME = N'TestPerformance', FILENAME = N'C:\Sql Databases\TestPerformance.mdf' , SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestPerformance_log', FILENAME = N'C:\Sql Databases\TestPerformance_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- create database on ramdisk (drive L:\) with default setting
CREATE DATABASE [TestPerformanceRamdisk] ON  PRIMARY
( NAME = N'TestPerformanceRamdisk', FILENAME = N'L:\TestPerformanceRamdisk.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestPerformanceRamdisk_log', FILENAME = N'L:\TestPerformanceRamdisk_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- create simple test tables in both databases
CREATE TABLE TestPerformance..TestInsert (testColumn UNIQUEIDENTIFIER)
GO
CREATE TABLE TestPerformanceRamDisk..TestInsert (testColumn UNIQUEIDENTIFIER)
GO
-- run this script on both databases and compare time
DECLARE @i INT = 100000
DECLARE @start DATETIME = GETDATE()
DECLARE @end DATETIME 
WHILE @i > 0
BEGIN
    INSERT INTO TestInsert
        VALUES (NEWID())
    SET @i -= 1
END
SET @end = GETDATE()
SELECT CONVERT (TIME, @end - @start)

Results are nice.

For SSD version, it was (~20s):

… for RAM disk version, it was (~7s):

RAM disk version is ~300% faster which is not negligible. Using RAM disk needn’t to be an option only for development machines. You can use RAM disk even for production to hold TEMPDB database (or any other treated as non-critical for persistence). Tony Rogerson’s wrote about this here.

Jakub Dvorak @ www.sqltreeo.com