TempDB on RAM Drive ?

Question

TempDB on RAM Drive ?

sqld-_-ba

SSCarpal Tunnel

Points: 4365
More actions
January 12, 2017 at 8:49 pm

#320423

Our vendor application database is very tempdb intensive. TempDB (and all databases) are on Tier 1 SSD in SAN.
sys.dm_io_virtual_file_stats shows over 50 Terabytes was read/written on tempdb data & log files in past month, with cumulative io_stall of 250 hours.
We have already tuned the SPs
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 increased CPU usage but reduced query duration, 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 ?
January 12, 2017 at 10:29 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

sqld-_-ba - Thursday, January 12, 2017 8:49 PM
Our vendor application database is very tempdb intensive. TempDB (and all databases) are on Tier 1 SSD in SAN.
sys.dm_io_virtual_file_stats shows over 50 Terabytes was read/written on tempdb data & log files in past month, with cumulative io_stall of 250 hours.
We have already tuned the SPs
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 increased CPU usage but reduced query duration, 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 ?

I did a couple of multi-million row experiments with TempDB on RAM but it didn't really help at all. YMMV.

The best thing to do, though, is to continue to fix the SPs. If they're still using a whole lot of TempDB, then there's still something wrong with them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 2

I would say your mileage may vary. This might help, it might not. It really depends on the IOPS and latency you are getting from your tempdb drive.

Lots of people have had good luck with SSDs, but go with an enterprise class SSD as you'll be reading and writing a lot here.

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 3

@jason, I've been monitoring the size for past couple years - 8 data files are pre-grown to 1GB each - they never grow more. and a 65 GB log file.. 100GB RAM drive should be enough. and I can also spill over to regular disk if needed

@jeff - I did a stress test of the vendor code on SAN and RAM Disk, and found ~50% query duration improvement, but at increased CPU % usage
Their code does lot of change-tracking on ~300 server tables and 400 client devices to sync them every 5 minutes, so the tempdb usage in that process is not going away anytime soon.

@steve-2, we have low latency ~1-10 ms on Tempdb drive. It is already an enterprise level HP SAN Tier1 SSD. But RAM is order of magnitudes faster.

Just wanted to know if any consultants out there have successfully implemented it and seen drastic improvements for large-scale customers.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 4

sqld-_-ba - Friday, January 13, 2017 8:41 AM
@jeff - I did a stress test of the vendor code on SAN and RAM Disk, and found ~50% query duration improvement, but at increased CPU % usage
Their code does lot of change-tracking on ~300 server tables and 400 client devices to sync them every 5 minutes, so the tempdb usage in that process is not going away anytime soon.

50% improvement is only 2X faster. Depending, of course, on the nature of any give query, you can usually get anywhere from 5X to 1000X (not a misprint) by doing some code tweeking. And, it's not like you have to fix all the code in the system. Normally fixing the top 5 or 10 queries listed in the instance reports for CPU or IO do the trick.

If their change tracking is accomplished by an SQLCLR trigger, that would be the biggest problem. It's one of the first big things I worked on getting rid of when I got here 5 years ago. Replaced all the audit triggers with hard coded triggers. And, no... I didn't write the code for the triggers. I wrote code to write the code so that it was easy to gen the necessary hard coded triggers for all the tables that needed to be audited.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125523 More actions · Answer 5

I'm no expert on tempdb, but I have a couple of thoughts, like what's your initial size and auto-growth setting on tempdb?

You can use the following query against default trace to determine averaging frequency and duration of file growth events per hour. Ideally tempdb size is initialized so that there is none. It will kill your performance if auto-growth is set too small. For a tempdb that can potentially reach 1 TB, you may want to simply initialize and leave it at that size.

DECLARE @trcfilename VARCHAR(1000) = (SELECT path FROM sys.traces WHERE is_default = 1); SELECT CONVERT(CHAR(13),StartTime,121) AS growth_yyymmddhh , COUNT(*)AS no_of_autogrowths , SUM(duration/(1000*60)) AS time_in_min FROM ::fn_trace_gettable(@trcfilename, DEFAULT) WHERE EventClass IN (92/*Data File Auto Grow*/, 93/*Log File Auto Grow*/) GROUP BY CONVERT(CHAR(13),StartTime,121), Filename WITH ROLLUP ORDER BY CONVERT(CHAR(13),StartTime,121), Filename;

Also, this script can provide details about what objects and spids are allocating space in tempdb.

http://www.sqlservercentral.com/scripts/tempdb/151252/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 6

@jeff, yeah we fixed the top 5 query SPs after a 2 year back-n-forth with vendor.. we've reached the end of tuning efforts
it's not CLR, just regular temp tables and mega-joins and sorts

@eric, we have 10 data files pre-grown to 1 GB each, and they never grow. and a 65GB pre-grown log file that also never grows.

What concerns me is the lack of popularity of WHQL certified RAM disks being used in industry.. thought it would be more prevalent for tempdb atleast

EdVassie SSC Guru Points: 60445 More actions · Answer 7

We use SoftPerfect RAM drive to host tempdb. So far we have not seen tempdb filling up but it is a risk whenever you use a ram drive to host a DB.
You need to write a simple routine (we use a .bat file) to create the folder structure needed for tempdb when the server boots, and add this as a boot-time application within SoftPerfect.
It does help performance compared to tempdb on a disk, we see 50% to 80% boost on operations using tempdb, so it is far from being a magic bullet to fix all performance issues. Alongside this is work to improve SPs (particularly removing cursors), and improving indexing. These other activities are the ones that sometimes give a 10X or 100X improvement in performance, but every little helps.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara