Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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?
0
billherde
Asked:
billherde
  • 4
  • 3
1 Solution
 
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.
0
 
billherdeAuthor 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.
0
 
Scott PletcherSenior DBACommented:
Standard Edition is limited to 64GB.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
billherdeAuthor 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.
0
 
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.
0
 
billherdeAuthor Commented:
I will try that on a test bed.  I want to see what happens when the ramdisk fills up.
0
 
billherdeAuthor 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.

Cool!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now