August 24, 2008 at 12:40 am
Does anybody have some specifics on the performance advantages when multiple files are used for TEMPDB... and it is on its own disk (san lun) with striped writes (versus fill and spill). Compared to having tempdb on the same disk as the rest of the database files.
A buddy of mine stated, "It is like a 5 second savings advantage per transaction." I was in a hurry, or I would have asked him if that is 5 seconds out of a normal transaction time of 2 hours... then I don't care. If it is five second savings out of a six second transaction time... well... interesting...
August 24, 2008 at 1:48 am
There are a couple different reasons for having multiple files in TempDB. I don't have specific numbers because they will be different for every system, depending how heavily it uses TempDB.
Firstly, the recomendation to have TempDB on it's own set of physical drives is because it can see heavy IO usage at times and that can interfear with the operation of the other database. That said, if you have a small system that doesn't see much TempDB usage (temp tables, table variables, sorts, hashes, snapshot isolation, online index rebuilds, etc), then you might be able to have TempDB on the same physical drive and the user databases
The first reason you may consider splitting TempDB into multiple files is if you are seeing contention on the allocation pages. It was a lot more common on SQL 2000 than on 2005, as the allocation algorithms improved significantly. The symptoms of this contention are requent page latch or page io latch waits on a resource 2:1:3. That's the first SGAM page in TempDB. The way to fix these contention problems is to add more files to TempDB, ensuring that they are all teh same size. The files don't have to be on separate drives. The usual recomendation is for a number of files equal to the number of CPUs allocated to SQL, or half the number of CPUs allocated to SQL. I usually start with half, and if there's still contention, add more files.
The second reason you may consider splitting TempDB is if there's IO contention on the TempDB drive. This is typically high disk queue lengths (sustained), high disk latencies (sec/read and sec/write) or a high disk % time. If there is high contention, then extra files should be added (in a ratio to the number of CPUs - 4 cores, 2 or 4 files; 16 cores, 2,4, 8 or 16 files) and those files should be on different physical arrays. Add files until the IO performance is acceptable.
Does that make sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2008 at 1:51 am
The source I used for this question is: http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
Another resource is: http://msdn.microsoft.com/en-us/library/ms175527.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 24, 2008 at 5:39 am
Thanks both... good info.
August 25, 2008 at 9:47 am
Do you still need to enable trace flag -T1118 to enable the uniform allocations feature in SQL 2005 as we did in SQL 2000?
August 25, 2008 at 10:02 am
Cliff Jones (8/25/2008)
Do you still need to enable trace flag -T1118 to enable the uniform allocations feature in SQL 2005 as we did in SQL 2000?
I don't believe so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2008 at 10:11 am
Thanks Gail, that was my understanding also.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply