3

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.

Improve this question
2
  • To clarify, in this question I consider only one server environment where if I take some RAM for RAM Disk there will be less available RAM for SQL Server. I don't consider environment where RAM Disk is shared disk on another server available through network.
    – alpav
    Apr 24, 2012 at 16:43
  • If you do this you store a lot of data twice - on the RAM disk and in buffer pool. Wasteful.
    – usr
    Nov 30, 2014 at 16:56

2 Answers 2

Reset to default
4

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?

Improve this answer
3
  • I think there are queries generating too many rows of intemediate data that have to be spilled into tempdb if there is not enough RAM. I may be able to optimize them to generate less intermediate data, but cost of RAM could be less than cost of labor for optimization. I am afraid that if I give all RAM to SQL Server it will use it for something else other than storing intermediate data - and this is the essense of my question.
    – alpav
    Apr 24, 2012 at 16:53
  • I think SQL Server is pretty effective at prioritizing memory grants, and if it chooses to use RAM for buffer pool instead of query grants, leading to spills to tempdb, it probably chose so wisely (even if you might not agree). I don't believe you'll get very far trying to outsmart SQL Server - and as I mentioned above, if you allocate n GB to some RAM Disk, what good does that do SQL Server when there is no tempdb activity? I suggest you'll get more bank for your buck overall by throwing a commodity SSD into your server, putting tempdb there, and leaving SQL Server all of the RAM.
    – Aaron Bertrand
    Apr 25, 2012 at 18:55
  • I posted similar question here. Our server is a VM, and all databases including tempdb are already on Tier 1 enterprise SSD SAN. the application is very tempdb intensive and we have ton of RAM to include all databases and have plenty left over. I see lot of waits on tempdb from virtual_file_stats, so we're looking for MS certified (WHQL) RAM Disk vendor but so far haven't found many..
    – d-_-b
    Jan 18, 2017 at 17:30
2

Allocating a RAMDisk isn't the same as allocating RAM: you're putting the data in memory, sure, but as far as SqlServer is concerned, this is still just a disk. A fast disk, but just a disk.

So, doing this doesn't actually force anything on Sql server, but it could conceivably improve disk access speeds.

That said, Sql is very well optimised, and I'd suggest you're better off configuring within Sql, rather than trying to "force" some alternative.

Improve this answer
1
  • Sorry I didn't mention that that I consider only one server environment where if I take some RAM for RAM Disk there will be less available RAM for SQL Server, which means that I force SQL Server to use less RAM for other things and use RAM for things that are in tempdb (of course SQL Server does not know about it).
    – alpav
    Apr 24, 2012 at 16:42

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.