Reading over this question reminded me of a question I had a while ago.
We have a SQL Server that has 512GB of RAM, the main database is 450 GB. We see quite a lot of action in TempDB (ok, I think it's "quite a lot of action" – it may not be!). I installed a demo version of RamDisk Plus Server, created a 50GB ramdrive, pointed TempDB at it, and saw no improvement in performance at all.
Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache?
Is a ramdisk pointless in this scenario?
I know SQL Server 6.5 had support for TempDB-In-Ram, but I see that was discontinued a long time ago!
Best Answer
Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical mechanism. Reference here is What does checkpoint do for tempdb?.
In a "well behaved" system, writes to a user database file occur on checkpoint. In a badly behaved system, writes will also occur when the lazywriter needs to flush pages from the buffer pool to make room for other pages.
But there is no need to flush tempdb to disk, as crash recovery is never run on tempdb, it's always recreated on startup.
This remarkably (it was a surprise to me) is the only mechanism by which tempdb pages will be written to disk. If there is buffer pool pressure, tempdb pages may be flushed to disk. If there isn't, it should not occur.
Edit: Debatable whether "badly behaved" is an appropriate description for a user database that is writing pages outside of checkpoint. Unusual, atypical, or just not ideal maybe?
Additional edit (following comments/chat with @PaulWhite):
The glaring omission above is that temporary tables are not sole source of tempdb traffic. Quoting from Understanding Hash, Sort and Exchange Spill Events:
I had incorrectly assumed that the mechanism behind a physical write for a spill operation were exactly the same as described earlier i.e. the lazywriter forcing tempdb pages to disk as a result of pressure on the buffer pool (caused by the spill).
@PaulWhite explained where I was wrong (thanks Paul!):
Paul also pointed me to his blog post Advanced TSQL Tuning: Why Internals Knowledge Matters which includes example scripts for demonstrating spills, for those that want to delve deeper.