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?