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.
 |
|
| 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 … 
