Using RAM Disk for SQL Server tempDB: possible issues

Started by Gary_D

Using RAM Disk for SQL Server tempDB: possible issues   26 August 2017, 00:57

I've been using 12GB RamDisk installed on a Hyper-V VM (90GB RAM) for use as the tempDb for SQL Server. In tests it's done a great job in speeding up large queries. However, in recent more intensive tests we've noticed queries taking a very long time that we end up aborting. We're trying to discover where the issue is - it could be anything.

The RamDisk option to emulate a hard disk is disabled because of a 40% performance penalty, but the trade off is it prevents Windows from providing any performance statistics on the RAM disk.

My question is, has anyone noticed issues when used with big SQL queries? And from a technical perspective, how does Windows Server 2016 see a RamDisk differently without hard disk emulation? Other than being unable to provide stats via Resource Monitor.

Thanks.
Gary.
SoftPerfect Support forum - Andrew avatar image

Re: Using RAM Disk for SQL Server tempDB: possible issues   28 August 2017, 14:05

Hmm... We haven't received any reports about stalled queries, but yes, like you mentioned, it could be virtually anything.
My questions would be:
  1. Are there any errors or warnings in the MSSQL logs?
  2. Does the problem persist when the HDD emulation option is on?
I'd say 40% performance penalty is worst-case scenario with a lot of small block I/O. Sequential I/O and larger blocks penalty shouldn't exceed 10% or so. I have attached a test screen shot.

Regarding the technical implementation, the RAM Disk app uses two drivers, we call them "volume" and "disk". For a regular RAM disk, only the volume driver is used. It emulates a standalone volume, similar to a plugged USB flash drive. When you enable HDD emulation, the disk driver starts operating on top of the volume driver. The disk driver emulates a complete hard disk with heads, cylinders, etc, making it indistinguishable from a standard hard disk drive. At the same time, I/O requests are forwarded down to the volume driver. This is why there is a larger performance penalty for smaller blocks - those generate more inter-driver I/O.
Attachments:
open | download – hdd-io.png (39.7 KB)

Re: Using RAM Disk for SQL Server tempDB: possible issues   28 August 2017, 22:42

Thanks for your reply, Andrew. I think the 4K DiskMark test better reflects SQL reads/writes than sequential. Records are stored all over the place and are rarely sequential unless you're selecting them in the identical order they were written - assuming they were written sequentially to begin with. Even with HDD emulation the RAM disk is still several times faster than an SSD drive. In fact it's nearly twice as fast as a top-end NVMe drive. So I'll keep HDD emulation enabled for the benefit of the Windows disk stats.

It's too easy to become obsessive about speed when an excess doesn't actually make much difference to the application's end result, particularly if the disk can read/write data faster than the CPU can process it.

With further analysis we're seeing a burst of activity from tempDb on the RAM Disk and then many seconds of no activity while a CPU core is busy before another burst from tempDb. I'm feeling more confident that there is no issue in using RAM Disk with SQL Server. We're taking it into production in 2 weeks' time where it will be hit hard. smile

Sometimes you can get the answer faster if you try the forum search and/or have a look at the software user manual to see if your question has already been answered.

Our forum rules are simple:

  • Be polite.
  • Do not spam.
  • If possible, check your spelling and grammar.

Author:

Subject

A brief and informative title for your message, approximately 4–8 words:

     

Spam prevention: please enter the following code in the input field below.

 ********   **     **  **    **   ******    *******  
 **     **  **     **  **   **   **    **  **     ** 
 **     **  **     **  **  **    **               ** 
 **     **  *********  *****     **         *******  
 **     **  **     **  **  **    **               ** 
 **     **  **     **  **   **   **    **  **     ** 
 ********   **     **  **    **   ******    *******  

Message: