THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

tempdb in RAM

I’m watching the FedEx page as my shiny new Dell E4300 travels across country – and bummed that I won’t have it in time for the MVP Summit. The first thing I’m going to do is install a Crucial 256Gb SSD drive. I‘ve heard good things about SSD drives, but some have reported that they can get burned out from repeated constant writes. One forum post noted that an SSD drive on a notebook with insufficient RAM can cause lots of swap file paging to disk which might explain some of the dead drive issues (so I'll turn off the page file.) Which got me to thinking, how can I reduce the SQL Server writes to the disk, which got me to thinking, tempdb is a hot spot, which got me to thinking, why not put tempdb in RAM? It’s not a new idea, but it’s the first time I’ve done it, so here’s how I placed tempdb in RAM ( on my current old Dell Studio noteook). The E4300 will have 8Gb RAM so I'm planning on usign a .5Gb RAM drive for tempdb as a starting point.

 

Disclaimer: I’m doing this in my development notebook. If you consider this for a production machine ensure that you have enough extra RAM to hold tempdb without risking running out of RAM or disk space.

 

This article benchmarks several Windows RAMdisks:

http://www.raymond.cc/blog/archives/2009/12/08/12-ram-disk-software-benchmarked-for-fastest-read-and-write-speed/

 

I chose the DataRam RAM Disk, once configured it boots with an E drive in RAM:

http://memory.dataram.com/products-and-services/software/ramdisk

 

Moving tempdb:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev',  FILENAME = 'e: \tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog',  FILENAME = ‘e:\tempdb.ldf')

 

Stop and restart SQL Server to move tempdb.

 

There’s a noticable delay the first time it’s accessed, but then SQL screams!

 

The RAMDrive is configured prior to SQL Server service starting and it’s worked fine with reboots.

 

If you have other ideas on how to improve the durability of an SSD drive, please comment...

 

-Paul

Published Monday, February 15, 2010 5:55 PM by Paul Nielsen
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

bak said:

When you said noticeable delay, did you mean one second or was it worse?

February 15, 2010 7:15 PM
 

Paul Nielsen said:

about 1 second delay, only the first time accessed on booting.

February 15, 2010 7:46 PM
 

AaronBertrand said:

Eventually, for this to work in a high availability production environment, clustering needs to be able to support local storage (since using tempdb the way you are would not work using shared storage). There is a similar request on Connect that I was already planning to cover in my next digest:

https://connect.microsoft.com/SQLServer/feedback/details/532759

February 15, 2010 11:29 PM
 

Aaron Bertrand said:

Simon Sabin has asked for better usability in the SQL Server Agent log viewer: specifically, to allow

February 15, 2010 11:52 PM
 

GrumpyOldDBA said:

I have an elderly lap top I use for presentations, so uses IDE drive, I replaced this with a SSD and split my databases out to an external 5.4k usb disk for the data drive - this took a previous 45 min query down to 6 mins, sadly my laptop doesn't support more than 1GB ram but I will look into this on my other laptop and my workstation - very interesting, thanks.

February 16, 2010 5:04 AM
 

Tony Rogerson said:

I wrote about this a while back (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx); 2006 - wow - it was a while back.

Be interested if you can rerun some of the tests/check the conclusions.

I found tempdb in RAM can dramatically improve performance in certain situations; there is also a get out clause when using tempdb - set no autogrow on the file in ram; but have other files on disk that can grow (and shrink) - its "a way" but not ideal

February 16, 2010 6:21 AM
 

Wes Brown said:

I wouldn't worry about the page file and burning out a modern SSD. If you are running windows 7 and get a drive that supports trim I doubt you have any issues at all. SSD's don't "fail" exactly when they reach their write cycle limit. You can still read from it just not write any new data to a cell that has been flagged. If you have a drive that fails all together it is more than likely some kind of manufacturing defect, firmware issue or a failure outside the actual NVRam chips. I've been running SSD's in my laptop for over a year now and have several in my machines at home. Not one has failed due to to much write activity. I have had failures though, they aren't bullet proof!

February 16, 2010 10:53 AM
 

RichB said:

Re: Clustering.

Not sure who wrote it or when but this article is interesting:

http://www.superspeed.com/servers/ms-sql-performance.php#5

Anyone have any comments on that?

Rich

February 17, 2010 6:38 AM
 

McSQL said:

February 21, 2010 5:34 PM
 

Dave said:

We've recently implemented this in a production environment.  Check out http://spivey24.blogspot.com/2011/05/sql-tempdb-in-ram-drive.html

May 18, 2011 3:47 PM
 

Maurice Pelchat said:

We did some testing with an SSD and we found that for some queries improvment is really important.  For example query execution may fall from 20 minutes to 3 minutes. With tempdb in RAM you may expect for such query greater improvments, since this is faster than a SSD.  With multi-core processor, CPU may not be an issue. But don't expect to have this improvement everywhere.  Some queries don't use tempdb, and this is not all T-SQL code that use temp tables.

May 19, 2011 9:54 AM
 

John said:

I know this is somewhat dated, but I've recently hit on this solution to increase performance on our cluster. I combined this idea with another, and voila.

1) Create and configure a RAMdisk on all cluster nodes using the same drive letter. 2) Create a "directory junction" on your SQL cluster shared storage (under DATA is a good place) that points back to a folder on the local RAMdisk. 3) Move the location of tempdb to the directory junction located on the shared storage (which points to the local RAMdisk). 4) Restart SQL Server. 6) Marvel in amazement as your cluster flies.

btw...SQL 2012 clustering now supports placing tempdb on local storage, so the directory junction is unnecessary in that case.

October 24, 2012 2:09 PM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement