October 28, 2002 at 12:58 pm
Hi everyone, I was just reading that the recommended size for tempdb (MDF file) should be 40% of the total of all databases on a server and that the tempdb log file size should be 40% of the tempdb mdf file. This seems too high to me for any kind of OLTP or OLAP database. Just want your opinion on this. Thanks.
October 28, 2002 at 1:57 pm
My understanding on the last thing I read wahs that the tempdb should size itself just fine. But the idea is to make sure it is able to handle the largest table you have on your server, especially if you do ORDER BY operations. When an ORDER BY is used the data is stored in the tempdb while operation is ordered.
November 7, 2002 at 9:07 am
Given that TempDB is recreated every time SQL Server is restarted do you think there is any merit in moving it off a RAID onto a fast local disk.
I'm considering installing a disk in my db server specifically to house tempdb.
November 7, 2002 at 9:24 am
We have put TEMPDB on a RAID1 device. We did this for performance reasons. I'm not a performance person, but I have been told by some performance experts that RAID1 is mirrored, so basically for every write there are two writes, but the are on separate disks, and no parity information needs to be calculated so writes happen faster on RAID1 then RAID5. Secondly, since there are two mirrors, every disk read could be serviced by either one mirror or the other depending on which one was not busy. Therefore RAID1 gives you better performance than RAID0, and or RAID5.
I was also think all database devices should be on some fault tolerance devices. Hate to have your database crash because a single disk went bad.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 8, 2002 at 3:12 am
What i noticed was that when the tempdb was to small you get an performance drop when sql trys to increase the size. Now i have made the size large enough to hold our import tables. We use some temp tables during our import process. I also have set the increament a fixedsize so that one increment is enough.
November 8, 2002 at 3:15 am
Its a bit of a "how long is a piece of string" question but on your systems how big is your TempDB?
The largest I have seen is 2Gb on a data warehousing installation, which doesn't strike me as particularly large.
November 8, 2002 at 4:24 am
quote:
Given that TempDB is recreated every time SQL Server is restarted do you think there is any merit in moving it off a RAID onto a fast local disk.I'm considering installing a disk in my db server specifically to house tempdb.
I don't know about moving of a redudant drive as if you lose the drive you are out of luck, the server is dead until you get the drive replaced. At minimum use a mirrored set. For maximum concurrency use a RAID 10 array.
The merrit of moving to another drive seperate from data files and og files is that it does process a lot of data, so high read and write amounts almost constantly.
quote:
Its a bit of a "how long is a piece of string" question but on your systems how big is your TempDB?The largest I have seen is 2Gb on a data warehousing installation, which doesn't strike me as particularly large.
We do a lot of reporting ang one of our tables is 7GB in size. tempDb was 7 GB when this table was created but due to the fact I knew we would never pull 7GB of data at one time and the file never took more than 3.5Gb I shrunk it to 4GB. Keep in mind that not only do temp table go there, but derived tables, data output with use of ORDER BY, using IN may generate a table to boost performance, and othet things occur there.
November 11, 2002 at 10:38 am
Its my belief that the tempdb for a database should be of a size to accommodate the largest transaction that will hit your database. Currently, one of mine is 11 GB, and rightly so, as it imports 9-10 GB files nightly. Any smaller, and you suffer performance hits while it grows for a large transaction, which of course is also the single worst time to do it.
Antares686 is right, it sizes itself, so long as you don't shrink it.
If you need to reclaim the space and do shrink it, and know that only particular transactions will need more than you typically allocate, you can grow the file for your import or transaction, perform the work needed, and then shrink it back for normal production work.
I also believe that if you are able, it should have a drive to itself. If you are able, the drive should be a raid array.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply