Solved

sql tempdb

Posted on 2014-09-08
7
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
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.
0
 
LVL 3

Author Comment

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

Expert Comment

by:Scott Pletcher
ID: 40310893
Standard Edition is limited to 64GB.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 3

Author Comment

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

Accepted Solution

by:
Scott Pletcher 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.
0
 
LVL 3

Author Comment

by:billherde
ID: 40312824
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
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.

Cool!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

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…
I have put this article together as i needed to get all the information that might be available already into one general document that could be referenced once without searching the Internet for the different pieces. I have had a few issues where…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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