Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: Can sufficient increase of available RAM eliminate usage of tempdb when querying read-only database in SQL Server?, but didn't get desired and specific response, so I am asking it differently now.

share|improve this question

migrated from stackoverflow.com Apr 25 '12 at 16:08

This question came from our site for professional and enthusiast programmers.

    
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 '12 at 16:43

2 Answers 2

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?

share|improve this answer
    
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 '12 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 '12 at 18:55

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.

share|improve this answer
    
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 '12 at 16:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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