1

I asked this question over at dba.stackexchange

Since we have 800 GB of memory and databases are only ~250 GB, we're planning to put SQL server's tempdb files on RAMDisk by allocating 100GB to its data and log files, instead of current SSDs (SAN Tier 1), since our vendor application is very tempdb intensive.

Is it better than PCI-E cards ? We care about speed only, not about persistence, since tempdb gets dropped and recreated everytime SQL is rebooted.

And we prefer WQHL certified vendors. Are there any to specifically choose or avoid ?

Community
  • 1
d-_-b
  • 191
  • 2
  • 7

1 Answers1

1

TempDB doesn't get dropped/created by SQL Server. The data inside it is not preserved, but the files are. SQL Server will expect them to be clean at startup, just like a standard database's set of data and log files.

If you create the TempDB files in a RAM disk, they will be destroyed on restart, which will crash SQL Server, as it requires that DB to exist in a clean state.

I guess, in theory, you could build out some logic

  1. that takes a "clean" set of baseline TempDB .mdb files copied while SQL Server is shut down
  2. and restores them to the RAM drive before SQL Server starts up

But that would be kludgy and prone to errors, since you'd need to make sure the file copy completed before SQL Server engine service started.

I use and recommend the "SQL Server Setup Checklist" from the Brent Ozar first responder kit when building SQL Server. It walks through best practices for SQL Server builds.

CaM
  • 131
  • 6