Sql-server – Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008

performancesql serversql-server-2008tempdb

If I give all RAM to SQL Server, it will use it's own pattern to determine what RAM to allocate for what.

If I allocate portion of RAM as RAM Disk and put tempdb into that disk it will force RAM
allocation for tempdb. This could be helpful if there are usage scenarios where SQL Server refuses to allocate RAM for tempdb and uses it for something else.

So I wonder if those usage scenarios exist where it makes sense to force RAM allocation for tempdb by using RAM Disk, thus overriding SQL Server decision for RAM allocation.

I was kind of asking this here: https://stackoverflow.com/questions/10199349/can-sufficient-increase-of-available-ram-eliminate-usage-of-tempdb-when-querying, but didn't get desired and specific response, so I am asking it differently now.

Best Answer

I'll suggest that moving tempdb to SSDs (which you can do in a cluster, too, as of SQL Server 2012) will:

  • provide a roughly equivalent benefit (not quite the same, but leaps and bounds better than spinny disks);

  • be supported (I don't believe Ram Disks are officially supported, but I'll confess it's been a long time since I looked into it); and,

  • won't use up that valuable RAM for tempdb when tempdb is not using it - at those times your system would benefit more from being able to use it for buffer, query execution, etc.

In general I agree with @Dan - there are probably optimizations you can make within SQL Server that will have much more noticeable impact than moving tempdb into RAM. For example, have you investigated optimizing or even eliminating some of the things that are using tempdb in the first place?