First published on MSDN on Jun 04, 2009
I continue to answer questions about the number of TEMPDB files and trace flag –T1118. It seems there are plenty of advice blogs, wikis, articles and other resources. To help clarify this I am going to post my latest e-mail exchange.
From:
Robert Dorr
Sent:
Thursday, June 04, 2009 9:08 AM
Subject:
RE: TempDB / data files per processor
It is not just SGAM in SQL 2000 that encountered contention. SQL Server can still heat up the PFS and other allocation pages on SQL 2005 and 2008 under very heavy use conditions. The temp table caching added to SQL 2005 has helped but on larger systems we can still get issues when the limits are pushed.
-T1118 forces uniform extent allocations so the answer is yes, it does help. Each data files is tracked with a file control block (FCB). The FCB contains members for the last uniform extent allocation and last mixed page allocation. So what happens during a new allocation is a call to the file group manager. The file group manager determines the next file to allocate from and from there the file control block reports the targeted starting point for free space searching based on the request type (mixed or uniform).
By adding a file per CPU what you are doing is allowing the file group manager to pick the next file (there are some optimizations here for tempdb to avoid contention and skip to the next file when under contention) and then use the target location to start locating free space.
-T1118 avoids all the logic to determine if a mixed extent is available or needs to be allocated and avoids the SGAM allocation patterns. So when the same file does have a collision a uniform extent is used and any mixed extent allocation contention on the SGAM associated allocation pages can be avoided.
Sent:
Thursday, June 04, 2009 8:57 AM
To:
Robert Dorr
Subject:
RE: TempDB / data files per processor
Hello Bob,
I understand that this recommendation comes from the SGAM contention in SQL 2000.
· Does multiple file help the performance even without TF1118?
· Since SQL2005 has had improvements towards page allocation caching, do you believe it is still a valid recommendation to use TF1118?
From:
Robert Dorr
Sent:
Thursday, June 04, 2009 10:52 AM
Subject:
RE: TempDB / data files per processor
One file per CPU that SQL Server sees. SQL Server creates a logical scheduler for each of the CPUs presented to it (logical or physical). The reason for the multi-file TEMPDB recommendation is to allow each of the logical schedulers to loosely align with a file. Since you can only have 1 active worker per scheduler this allows each worker to have its own tempdb file (at that instant) and avoid allocation contention.
8 is not a limit for TEMPDB we have many deployments with more than 8 TEMPDB files. 8 is the general cap for MAXDOP setting.
Sent:
Thursday, June 04, 2009 8:48 AM
Subject:
TempDB / data files per processor
Hi ,
I have had a conversation with a colleague. We were talking about optimization of TempDB for SQL Server 2005.
The general rule I know is: 1 data file per logical or physical processor (i.e. a SQL Server 2005 running on 4 quad-core cpus -> 16 cores should have 16 data files for TempDB)
The colleague says there is a limit to this rule. We should not use more than 8 data files for TempDB. This information shall be from the product group.
My problem. I do not see any information in the world wide web where this limit is mentioned. Maybe someone can give me a hint???
Bob Dorr – Principle SQL Server Escalation Engineer
