TempDB in RAM needs a review of capacity, support and memory pressure. I compare those costs with the bottleneck it would address.
A friend asked whether putting TempDB on a RAM-backed drive would make it faster. We had been discussing Cindy Gross’s TempDB I/O guidance and the importance of reducing storage contention. The attraction of fast memory was understandable.
Account for the whole machine
Memory reserved for a RAM disk is unavailable to the operating system, SQL Server buffer pool, and other consumers. Its size also limits the temporary workload it can hold. A fast small volume can still become a capacity problem.
I previously recalled a facility in SQL Server 6.5. That historical recollection doesn’t establish a supported current configuration. I check the actual storage product and engine requirements.
Investigate the measured bottleneck
I inspect TempDB capacity, I/O, allocation contention and workload. Newer memory-optimized TempDB metadata addresses metadata contention. It doesn’t place every TempDB file or object exclusively in RAM.
I’d examine supported storage and workload changes before reserving memory for a third-party RAM disk. Have you used one in production? What measurements and support arrangements informed that decision?
A RAM disk is not free memory, it is memory reserved away from other consumers.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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
I think I’ll try it on my DWH server with 24Gb RAM
=)
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
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
Sorry Sir…
I’m using MS SQL 2008 R2 with Windows 2008
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. .
I tried this with Superspeed’s RAMDISK. I found the results staggering for OLTP work, when there’s lots of complex sprocs.
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.
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.
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 :)
Thanks for your reply to a old comment. It would definitely help someone in future.
Hello Pinal ,
How can I check that all tempdb files are used properly and parallely