SQL Server with in-memory TempDB

Today, I did some experiment with SQL Server. I have been using an awesome tool called RamDisk now and then for experiments. It allows to use RAM as a HDD !! So if you have enough RAM you can throw some RAM to this tool (free version has 4Gb max limit) and you will get nice 4Gb chunk of storage. And then you can use this chunk just as any regular drive but downside is, it is volatile since though it looks like normal HDD, it is in fact still RAM and thus it posses all good and bad properties of RAM. Bad properties like highly volatile and unreliable storage but Good property like super fast read and write capability and near instantaneous response.

As we already know that most common bottleneck for SQL Server (or generally any application) is HDD. Since they are the slowest moving part of whole architecture. Yes it is true that with introduction to newer breed of storage devices,called SSDs don’t have any moving part and so they provide super fast read and write capability. But SSDs are still relatively new technologies, so we actually don’t know how they will perform at long run and they are still expensive as hell.

And RamDisk on the other hand, is cheaper solution because you actually don’t buy new storage but you add more memory and use it to store data temporarily. It could be very well possible solution to explore for TempDB, since TempDB doesn’t require to store any data and it is mostly used as a scratch pad for SQL Server.

Before proceeding further, I would like to mention that TempDB for SQL Server was using same physical drive as of Windows and I used SQL Server 2008r2 Dev on Windows 7.

The experiment,

First I need to test how long it takes to execute some statement with normal configuration (with TempDB located at C:\TempDB\). So I created some long running SQL Query,

USE tempdb
set nocount on
CREATE TABLE #t1 (id INT,serial VARCHAR(100))
DECLARE @i INT,@firstrun DATETIME,@lastrun DATETIME
SET @i = 1
SET @firstrun = GETDATE()
PRINT @firstrun
WHILE @i < 10000000
BEGIN

INSERT INTO #t1 VALUES (@i,NEWID())
SET @i = @i + 1

END
SET @lastrun= GETDATE()
PRINT @lastrun

PRINT DATEDIFF (ss,@firstrun,@lastrun)

DROP TABLE #t1

-- RESULT
-- Oct 20 2011 8:00PM
-- Oct 20 2011 8:09PM
-- 517

Then I created HDD using RamDisk utility, since I have 16Gb RAM in my new machine I created 4Gb HDD using RamDisk.

RamDisk_Setup

RamDisk_Setup2

RamDisk_Setup3

Fire up RamDisk configuration utility and keep default options except use “unformatted” disk type  and click on Start, it will create a new drive. but since created drive  is un-formatted you will need to go to disk mgmt in there add and format drive to use it.

Once I have HDD, next step was to change location of TempDB to newer drive.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'H:\templog.ldf');
GO
-- Will need to Restart SQL Server instance
-- Check location of TempDB
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

After restarting instance of SQL Server, it was using newer drive for TempDB. And all I needed to do is to run that query again,

USE tempdb
set nocount on
CREATE TABLE #t1 (id INT,serial VARCHAR(100))
DECLARE @i INT,@firstrun DATETIME,@lastrun DATETIME
SET @i = 1
SET @firstrun = GETDATE()
PRINT @firstrun
WHILE @i < 10000000
BEGIN

INSERT INTO #t1 VALUES (@i,NEWID())
SET @i = @i + 1

END
SET @lastrun= GETDATE()
PRINT @lastrun

PRINT DATEDIFF (ss,@firstrun,@lastrun)

DROP TABLE #t1

-- RESULT
-- Oct 20 2011 8:15PM
-- Oct 20 2011 8:17PM
-- 149

But this time I got different runtime. It was smaller than what I got when I used regular disk.

This experiment is a good indication that it is possible to improve performance of database (if TempDB is used more) by use of Ram disks.

It’s Just A Thought … Peace

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *