Worth moving tempdb to a RAM drive?
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?
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?
Aneesh
http://www.superspeed.com/servers/ms-sql-performance.php
ASKER
I've read all the adverts, I was looking for reports of real-world experience from DBAs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if u can alse elaborate the RAID settings, it woiuld be quite hepful for all of us to be more objective in our reply.
ASKER
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!
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!
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.