Solved

sql tempdb

Posted on 2014-09-08
7
250 Views
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?
0
Comment
Question by:billherde
  • 4
  • 3
7 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 
LVL 3

Author Comment

by:billherde
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Standard Edition is limited to 64GB.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:billherde
Comment Utility
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.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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.
0
 
LVL 3

Author Comment

by:billherde
Comment Utility
I will try that on a test bed.  I want to see what happens when the ramdisk fills up.
0
 
LVL 3

Author Closing Comment

by:billherde
Comment Utility
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.

Cool!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction: Sometimes when I receive a call from my users to solve their problems it is very difficult for me to found their computer IP address. Even finding their computer Host to provide remote support can be a problem.  So I resorted to Goo…
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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

10 Experts available now in Live!

Get 1:1 Help Now