Using SQL Server 2016 Enterprise, having just recently upgraded from 2008 R2.

The server has 192GB of memory, 24 logical codes, and is running around 30 databases with a total size of around 500GB. Most databases are heavy OLTP load.

Storage is 3 RAID1 arrays of SSDs, and one RAID1 array of HDDs. All the heavily used databases have their data and log files on SSDs.

Previously we used a 15GB RAMDisk for tempdb, however during some queries this would run out of space, and the query would fail.

Most of the advice I could find online seemed to suggest that SQL Server would know best how to use the memory and that I should give all the memory to SQL rather than trying to "outsmart" it by putting tempdb on RAMDisk which SQL wouldn't know about, and risk having pages in memory twice (once in SQL's buffer and again on the RAMDisk.

So when we upgraded to SQL Server 2016, I ditched the RAMDisk, and created 8 10GB data files for the tempdb and stuck these on one of the SSD arrays.

Now I'm seeing near constant baseline of 0.5MB/s writes to each of these 8 files, and sometimes pekaing much higher. That's 500GB/day+ of writes to these SSDs, which has me concerned about endurance.

Further, sys.dm_io_virtual_file_stats shows average_write_stall_ms of 150ms+.

Would you recommend putting tempdb back onto a RAMDisk to both aid performance and protect the SSD's from heavy constant writes?

If I did that, ideally I'd like some sort of "oversplill" so that if an odd query here or there needed a large amount of space in tempdb it could use a second file on the SSD instead of the RAMDisk. The issue here is that, then SQL would start splitting all writes based on free space between the tempdb data file on RAMDisk and the tempdb data file on SSD. Would auto-shrink maybe be an option here, for encourage it back to exclusively using the tempdb data file(s) on the RAMDisk after a rare query that needed lots of tempdb space?

Any other recommendations or thoughts welcomed!

  • Also, if it helps: BufferCacheHitRatio: 99.9997915221800 Page life expectancy 5578 65792 – William Luke Jul 6 '16 at 11:49
  • 150ms write stall for SSD seems much too high. Is the SSD SAN-based?For maximum performance, use PCIe SSD rather than SATA or SAN. – Dan Guzman Jul 6 '16 at 12:22
  • It's local SATA, though a RAID controller. – William Luke Jul 6 '16 at 12:34
  • @WilliamLuke Do you really need 10 Temdb data files. Have you given thoughts while configuring 10 tempdb data files. The access of data files can also cause issue – Shanky Jul 6 '16 at 14:30
  • It's 8 files @ 10GB each. From reading online that seemed to be the recommendation. I assume the volume of IO would be the same regardless of the number of files though, and it's that IO that I'm concerned about. – William Luke Jul 6 '16 at 14:33

Your Answer

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

Browse other questions tagged or ask your own question.