November 28, 2001 at 6:40 am
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?
November 28, 2001 at 9:09 am
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
November 28, 2001 at 2:39 pm
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.
Andy
November 30, 2001 at 8:59 am
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.
November 30, 2001 at 9:56 am
What about creating permanent tables instead of temp tables and clearing them every night? Might save some work.
Steve Jones
December 3, 2001 at 9:48 am
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.
Cheers.
December 3, 2001 at 10:26 am
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
December 3, 2001 at 4:23 pm
Replication is often a good solution for reporting. It's pretty solid these days and relatively low overhead.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply