One good article on temp and variable: http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
Answer from Microsoft on where (RAM vs disk) temp tables and table variables are created: http://support.microsoft.com/kb/305977
Relevant quote (from Q4):
If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Hence my point that leaving the RAM available for the cache is probably a better use than a RAM-disk.
Puting the log files for tempdb on a RAM disk might have some performance improvements, but it is again unlikely because of how caching works. It would definitely render the data non-ACID, but tempdb pretty much already does that anyway, so no loss there.
If you decide to go ahead and do that, I'd like to see before and after performance numbers for the server. Total wait time would be the one to look at.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon