Solved

Worth moving tempdb to a RAM drive?

Posted on 2006-07-04
7
945 Views
Last Modified: 2008-01-09
Server performance is always going to be one of my primary issues. We have a big web-based application with lots of back-end processing that leans heavily on temp tables, table variables and temp tables.

Is there a performance increase to be gained by creating a RAM drive and relocating the tempdb on to it? As I understand it, this gets recreated every time the server starts, so I'm not remotely worried about losing the contents if it crashes.

I'm running SQL Server 2000 on a Win2003 box, currently with 2GB installed. The tempdb data/log files never exceed 50MB/10MB respectively.

I'm sure there are technical difficulties to be overcome (like ensuring the RAM drive exists before SQL starts!), but is this worth following through?
0
Comment
Question by:kenpem
[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
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17038251
0
 
LVL 4

Author Comment

by:kenpem
ID: 17038294
I've read all the adverts, I was looking for reports of real-world experience from DBAs.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 17038372
subscribing...

i'd have thought that giving the memory to the DBMS engine to manage directly would be the best performance wise..

and using Table variables rather than temp tables (@xxx vs #xxx)  would assist that...

but i suppose if you've got "heaps" of memory then a Ram disk .. could assist..

what size of physical database are you envisaging?
how many (concurrent) users  ?
and what level of intermediate result sets, sorting overhead doo you envisage in most of your queries...?


interesting
Lowfatspread
   
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 8

Expert Comment

by:raj_
ID: 17038469
if u can alse elaborate the RAID settings, it woiuld be quite hepful for all of us to be more objective in our reply.
0
 
LVL 4

Author Comment

by:kenpem
ID: 17039330
A simple RAID 0 configuration, nothing fancy, everything on the same drive. Table variables used where possible, queries optimised pretty well. User connection counts vary..... from 5 to 500 at a time - hopefully some more as the business grows, but by then we'll have added some hardware.

There's a lot of data overhead.... as users move around the web site, we do major on-the-fly lookups and calculations to figure out what to present to them - a little like Amazon does ("you may also like...."), but a lot more of it. This is all qualified, sorted & ranked. It's actually pretty cool and not as slow as I had feared, but as I said, I'm always chasing better performance. Some temp tables have been unavoidable, so I was just looking for ways to eke out another 1%.

If it's not worth the bother, at least I'll know that!
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 17041029
It all depends to what extent your tempdb is a performance bottleneck as opposed to your other databases.  You need to audit your system before making any recommendation of such nature.  
One tip though...tempdb clearly is more utilized in 2005 than 2000 therefore the performance leverage putting the tempdb on RAM drive would be more interesting.

Hope this helps...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 17045227
Also, keep in mind that variables of type table also use tempdb.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

740 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