SQL SERVER – TempDB in RAM for Performance

SQL SERVER - TempDB in RAM for Performance tempdb Performance Tuning is always the most interesting subject when we talk about a 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?”

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQLAuthority News – Win Surprise Gift at TechED 2011 Sessions – Wait Types and Extended Events
Next Post
SQL SERVER – Cartoon Challenge – 140 Character Winner is Here

Related Posts

12 Comments. Leave new

  • sounds like https://en.wikipedia.org/wiki/TimesTen
    I think it’s usable if you have enaugh RAM

    but I suppose there will be a problem with clustering

    Reply
  • I think I’ll try it on my DWH server with 24Gb RAM
    =)

    Reply
  • 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

    Reply
  • 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

    Reply
  • Sorry Sir…
    I’m using MS SQL 2008 R2 with Windows 2008

    Reply
  • 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. .

    Reply
  • I tried this with Superspeed’s RAMDISK. I found the results staggering for OLTP work, when there’s lots of complex sprocs.

    Reply
  • 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.

    Reply
  • In 2014 It may make a much larger difference. Compared with a high performance RAID IO subsystem, a RAMDisk actually showed little improvement because tempdb is accessed via a single core up through sql server 2012.

    Reply
  • Hi Shay…
    May be your friend, modified the temp dB location and then he restarted the SQL server or services so that it’s showing new temp dB(multiple MDF nd ldf,previous MDF ldf and new created temp dB files)..
    You can delete your​ previous MDF ldf files to reduce the space on drive(if u dnt want to use Ur old tempdb).

    Note:-Tempdb created every time when u restart SQL server or SQL server service.

    Thanks,
    “Chota pinal Dave”
    Big fan of pinal :)

    Reply
  • Hello Pinal ,
    How can I check that all tempdb files are used properly and parallely

    Reply

Leave a Reply

Menu