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!