sql tempdb

We are running a SQL 2012 std. instance on a 2008 R2 platform.  It is a VM on ESXi 5.5.  Drives are on a hybrid SAN with plenty of bandwidth.  To further speed things up I am running Dataram ramdisk to create a 30GB ramdisk for tempdb.  Moving tempdb to the ramdisk is trivial, but I am wanting to ensure that SQL does not run out of space for tempDB should something go off in the weeds.  I think it is fairly straightforward to add another location to tempdb file locations, but is there a way to prioritize them?  Of course we want to use all the ramdrive before going to the virtual hard drive.  And is there a way to trigger a shrink back to the ramdrive should it exceed?
Bill HerdeOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
No, there really isn't.

I'm not sure the RAM disk is a good idea anyway.  SQL will use most of its memory for buffers, and tempdb will use that space when it needs it just like any other db.  I'd just leave all extra RAM for SQL itself to manage rather than forcing it to be dedicated to tempdb.
Bill HerdeOwnerAuthor Commented:
I did some testbed benchmarks with SQL using a ramdrive for tempdb and performance increased over 30%.  This and the desire to keep from burning out the SSD drives in the hybrid array are pushing this plan.  The server has 128GB ram, and even after a long run it never takes more than about 60GB for SQL.  The server is set to allow SQL to take up to 100GB ram, but as it never does, it either does not need it, or we need to upgrade to enterprise.

So still looking for secret knowledge.
Scott PletcherSenior DBACommented:
Standard Edition is limited to 64GB.
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Bill HerdeOwnerAuthor Commented:
That's what I thought also, but had not verified.  So without moving to enterprise, the only thing left to speed it up is to get the data faster, or at least tempDB.  With what you are telling me about there not being any way to assign a preference for location usage, it looks like it will have to be make a big enough ramdisk to be sure I wont run out of room.  I know that if tempdb attempts to grow and does not have disk space it throws a system error and falls down.  Does it do the same if SQL is told to limit the size of tempdb?  It would be nice if SQL would be smart enough to handle a maximum size.
Scott PletcherSenior DBACommented:
SQL can't always limit tempdb to a certain size.  Tempdb is used for a *lot* of things, most of them ad-hoc.  If, for example, you have a large GROUPing and ORDER BY -- for which tempdb is used -- SQL will need space in tempdb -- it can't just refuse to GROUP and sort the output, because then the query results won't be accurate.

About the best you can do is put a larger tempdb data file on RAM disk, then a smaller one on physical disk, as an "overflow" area.  Because of its allocation algorithm, I think it's likely SQL will use more of the RAM disk.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill HerdeOwnerAuthor Commented:
I will try that on a test bed.  I want to see what happens when the ramdisk fills up.
Bill HerdeOwnerAuthor Commented:
On test bed, I created a 8MB ramdisk and a second PRIMARY file location on a new vmdk. also 8MB. Initially both grew at about the same rate. When the ramdisk ran out, the second location began to grow faster.  This at least means I would not run into a out of disk problem.  When creating a larger initial disk on the ramdisk, the second location does grow until the ramdisk also has to grow.

Put this idea into production with a 30GB ramdisk.  Initial tempdb files sized not to exceed the drive, but at maximum capacity.  Second tempdb location on a vmdk set at minimum size but allowed to grow unlimited.  TempDB used to run about 15GB in size before, but now has yet to use 3GB of allocated space in ramdisk location. Space available indicates that about 2.5 GB of allocated file is in use now, so it is not trying to round robin between the two locations since the second location has remained 4MB through this mornings billing cycle.  Also, vcenter is showing lower disk latency to the SAN.  Not a whole lot, but enough to be noticeable. (those milliseconds add up!)

So it appears SQL will pseudo prioritize if it can use a file that does not need to grow. When it has to grow the file, it does so equally.  Good plan if using spindle disks all the same speed, but does not help here.  At this time, it appears SQL is using the ramdisk for just about anything it needs to be doing, and won't jump to the second file until the file reaches it's assigned limit.

Performance increase for our systems is following testbed indicators and (for our DBs) is yielding about 25% faster completion of large queries.  A reindex that runs daily also completed last night about 25% faster than the night before.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server Apps

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.