SQL SERVER – TempDB in RAM for Performance

Performance Tuning is always the most interesting subject when we talk about software application. While I was recently discussing performance tuning with my friend, we started to talk about the best practices for TempDb. I also pointed my friend to the excellent blog post written by Cindy Gross on the subject: Compilation of SQL Server TempDB IO Best Practices. One of the discussion points was that we should put TempDB on the drive which is always giving better performance.

But my friend suddenly asked, “what if we can put TempDB on RAM, as RAM is the fasted drive?”

Good question! This was supported in earlier versions of the SQL Server (I think in 6.5). In later version of the SQL Server, the whole algorithm was written more efficiently and it is not that much of an absolute requirement. However, I still prefer putting TempDB on the drive which has lesser IO pressure.

Afterwards, he introduced to me a tool which can create drive from RAM. Well, that was an interesting thought. But then again, I will not go for this solution as it is not natively provided with SQL Server. For me, SQL Server Engine knows the right thing to do and how to maximize the usage of the RAM. Taking away RAM from OS and from other applications may not be a good idea. There are more optimization tricks that exist for TempDB than going for this option.

I would like to ask my readers who among you use this method in the production environment. What is your experience?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – TempDB in RAM for Performance

  1. Consider using SSDs rather than memory.

    A friend has recently tried putting the temp db on his powerful server on an SSD. This is not quite the same as putting it in RAM but is a better option in my opinion as you can can raid the drives for optimimum resilience. Two relatively small SSDs are not that expensive and they are solid state and ridiculously fast (as they are basically persistent just memory).

    He tried two identical 12 core servers (2 x 6 core) with really good disk specs (all SAS). On one machine he has tempDB on a RAID10 SAS (bit of overkill I think RAID 1 would suffice) but on the other he used RAID 1 SSDs.

    He has found reindexing to be 4 times faster on the SSD machine.

    Dave

    Like

  2. Sir,
    Recently,I take a leave,then don’t know what my friend doing..his deleted the log file in msssql folder,because we have 2 log file..one is .ldf and anoter one is _1.ldf.Now i found that in msssql folder have a new DB name “tempdb.mdf”.May I know how this DB created,what is the function?can i delete it cause the file size reach almost 177GB.Another case , I not found this tempdb in SSMS.
    Can you help me with explaination and idea to solve it.

    TQ
    /Shah

    Like

  3. Shah: i think you friend when deleted the file after restarting the server
    alter database tempdb remove file . in this case metadata updated.

    after restarting the server or before he altered the file name also.so this is not of concerened.

    it’s not possible to drop System database and start the SSMS. .

    Like

  4. We are developing stored procedires that make heavy use of #temp tables. We located tempdb in a ramdisk (Superspeed Ramdisk) and our SPs took about 1/3 of time, compared to tempdb stored in common drives. So, in terms of speed I recommend it, but there may be considerations I’m missing.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s