11

Our vendor application database is very TempDB intensive.

The server is virtual (VMWare) with 40 cores and 768GB RAM, running SQL 2012 Enterprise SP3.

All databases including TempDB are on Tier 1 SSD in SAN. We have 10 tempdb data files, each pre-grown to 1GB and they never auto-grow. Same with 70GB log file. Trace Flags 1117 & 1118 are already set.

sys.dm_io_virtual_file_stats shows over 50 Terabytes read/written on tempdb data & log files in past month, with cumulative io_stall of 250 hours or 10 days.

We have already tuned the vendor's code and SPs over past 2 years.

Now, we're thinking of placing tempdb files on RAM Drive since we have a ton of memory. Since tempdb gets destroyed/recreated when server is rebooted, it is an ideal candidate to place on volatile memory which also gets flushed out when server is rebooted.

I've tested this on a lower environment and it has resulted in faster query times but increased CPU usage, because the CPU is doing more work instead of waiting on slow tempdb drive.

Has anyone else put their tempdb on RAM in high oltp production systems? Is there any major disadvantage? Are there any vendors to specifically choose or avoid?

  • Maybe you vendor is using too much tempDB? – paparazzo Jan 13 '17 at 3:56
  • @Max, that question only answers part of the issue 'whether tempdb writes go to disk'.. not reading from tempdb – d-_-b Jan 13 '17 at 4:15
  • 1
    Using SSD at the SAN level is not actually gaining you much advantage because of the network latency. Put an NVMe SSD into the SQL Server and run tempdb on it. – Max Vernon Jan 13 '17 at 4:15
  • i just googled 'nvme ssd vs ramdisk', and the first result is a reddit post claiming the latter is ~3 times faster. Also it's easier than driving to datacenter and installing it in the blade :) – d-_-b Jan 13 '17 at 4:17
  • 2
    Microsoft uses PCI-E FusionIO cards in some of their clusters (there's a minor workaround where you can still use tempdb with local disks they use). The PCI-E interface as Max pointed out is considerably faster. You'll want to measure CPU interrupts per second to measure if you're getting more requests per second. You should be as disk latency is one of the main causes of low interrupt requests (not SQL time). – Ali Razeghi Jan 17 '17 at 4:54
6

First, patch: make sure you're on 2012 Service Pack 1 Cumulative Update 10 or newer. In SQL 2014, Microsoft changed TempDB to be less eager to write to disk, and they awesomely backported it to 2012 SP1 CU10, so that can alleviate a lot of TempDB write pressure.

Second, get exact numbers on your latency. Check sys.dm_io_virtual_file_stats to see the average write stall for your TempDB files. My favorite way to do this is either:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30 /* Checks for 30 seconds */
sp_BlitzFirst @SinceStartup = 1 /* Shows data since startup, but includes overnights */

Look at the file stats section, and focus on the physical writes. The SinceStartup data can be a little misleading since it also includes times when CHECKDB is running, and that can really hammer your TempDB.

If your average write latency is over 3ms, then yes, you might have solid state storage in your SAN, but it's still not fast.

Consider local SSDs for TempDB first. Good local SSDs (like Intel's PCIe NVMe cards, which are under $2k USD especially at the sizes you're describing) have extremely low latency, lower than you can achieve with shared storage. However, under virtualization, this comes with a drawback: you can't vMotion the guest from one host to another to react to load or to hardware issues.

Consider a RAM drive last. There are two big gotchas with this approach:

First, if you really do have heavy TempDB write activity, the change rate on memory may be so high that you won't be able to vMotion the guest from one host to another without everyone noticing. During vMotion, you have to copy the contents of RAM from one host to another. If it's really changing that fast, faster than you can copy it over your vMotion network, you can run into issues (especially if this box is involved with mirroring, AGs, or a failover cluster.)

Second, RAM drives are software. In the load testing that I've done, I haven't been all that impressed with their speed under really heavy TempDB activity. If it's so heavy that an enterprise-grade SSD can't keep up, then you're going to be taxing the RAM drive software, too. You'll really want to load test this heavily before going live - try things like lots of simultaneous index rebuilds on different indexes, all using sort-in-tempdb.

1

Creating a RAM drive should be trivial. Many bootable Linux thumb drives and optical drives create a RAM drive and store OS files there. The root file system is then in memory. In windows, back in the day, a RAM drive was loaded as a device driver in config.sys. Usually the driver was loaded in high memory. In my opinion, this is a very good and simple solution. If have created your solution using a RAM drive I would like to hear of it. I would like to do something similar but would like to do writes to permanent storage and store a db in RAM. In my case we have machines that can install more RAM than the OS can use. Creating a RAM disk before the OS loads will allow utilization of RAM the OS would not see otherwise.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.