Forcing tempdb in ram...

  • Prior to version 7 you could specify that tempdb resides in ram. This feature has been taken away with version 7.

    We make heavy use of tempdb (temp tables, sorts, etc.) and would like to get better performance from our tempdb. Our system is flush with memory (4GB) and SQL server usually only consumes less than 2 GB.

    I'd like to get back to tempdb forced into ram since I have the extra memory.

    Any thoughts?

  • You could setup a RAM disk and run it that way. Sunbelt Software used to carry a product for NT that did this, not sure about 2000.

    Steve Jones

  • Or look at ways to reduce tempdb usage - I think you'd be better off in the long run. Maybe leverage the table valued functions available in SQL2K.


  • Thanks for the information. I agree that less tempdb would be beneficial, however, the schema that has been dealt to us does not lend itself to good / performance oriented techniques.

    The vendor has created a multi-use table, that holds many different kinds of data.

    The table grows by a million rows per day (purged at night), and so we create a number of temporary tables to emulate a more normalized schema prior to running reports. Yeah, it sounds weird and is weird. Nothing like a self referencing table that you need to join to six times in order to get business information out.

    Thanks again. I think I'll look into trying a RAM drive.

  • What about creating permanent tables instead of temp tables and clearing them every night? Might save some work.

    Steve Jones

  • Steve - Good suggestion on the permanent table suggestion.

    We have been kicking that idea around.

    The permanent tables would get populated using update/insert triggers and then the reports could run from them.

    We need to put through our stress test environment and see how it performs. The system is an OLTP system that needs to process 15 thousand images an hour.

    The other option on the table is to use replication for the reporting side of the house.

    If we find anything out about the performance of the real tables I'll post it back.


  • Good luck and you are welcome. Hope it helps. Personally, I'd avoid triggers if the system is loaded. Actually, I'd rather have something run every minute and snag the latest data, but that's my opinion.

    Steve Jones

  • Replication is often a good solution for reporting. It's pretty solid these days and relatively low overhead.


Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply