sql tempdb

Posted on 2014-09-08
Last Modified: 2014-09-10
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?
Question by:billherde
  • 4
  • 3
LVL 69

Expert Comment

ID: 40310800
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.

Author Comment

ID: 40310884
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.
LVL 69

Expert Comment

ID: 40310893
Standard Edition is limited to 64GB.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Comment

ID: 40311312
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.
LVL 69

Accepted Solution

ScottPletcher earned 500 total points
ID: 40312186
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.

Author Comment

ID: 40312824
I will try that on a test bed.  I want to see what happens when the ramdisk fills up.

Author Closing Comment

ID: 40315468
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.


Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 80
Retention Policy for Backups 1 24
SQL Update Query - What's wrong with this. 18 20
Ssis not sending failure message 2 2
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now