TempDB in RAM on SQL2k?
-
Hello World!
In SQLServer 6.5 there was this option of placing 'tempdb in ram'. I recently switched to SQL2k and I can't find such an option. Am I looking in vain? Why this option was removed?
TIA,
ELIonescu
-
There is no such option in SQL Server 2000. MS feels tempDB is optimized enough, so that there is no need for it anymore.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
As a general rule of thumb for TempDB I like to create one MDF file for each processor on the server and split the locations of the MDF and LDF files onto different arrays. Then again I'm looking after fairly large DB's which utilize a lot of temp tables, this might be overkill with a smaller system.
TempDB may be fairly well optimised but there's nothing wrong with giving it a helping hand where needed
-
Like Frank says, MS feels that SQL Server is better at deciding how to work with tempdb and optimize it's own memory. You can still help by optimizing tempdb though, and Mike's advice is very good. Also note that preferably the multiple MDFs (one for each CPU) should be of equal size and not set to grow at all.
Also see this KB article if you are having problems with contention in tempdb (or read it anyway, it is interesting
): FIX: Concurrency enhancements for the tempdb database -
Thank you all for sharing your expertise!
I investigate the topic since I have few sp returning record sets, procedures that use up to 3 temp tables to perform some statistics. On SqlServer 6.5 I 'pinned' the 'tempdb in ram' for speed and for easing the life on hdd.
Yours,
Emanuel Ionescu
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply