Share via

SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage

  • Article

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations. The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.

SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s). The logic was also added to SQL Server 2005 in a CU release, KB article 936185.

The MORE INFORMATION section of the article states: "The hotfix that this article describes affects only the tempdb database. Additionally, you do not have to have trace flag 1118 after you install this hotfix. However, you can still use trace flag 1118."

Some have interpreted this to mean the 1118 trace flag has been deprecated. This is NOT the case and I have asked for the text to be updated. Trace flag 1118 may still be needed along with the other suggestions in section "Troubleshooting contention caused by to DML operations" of https://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx.

For example, I am working with a customer on a TEMPDB PFS page contention. The stored procedures heavily use "select into ... #tmp where 1=2" This syntax is not moving rows because of the where clause limitation but is creating tables. This will place pressure on the allocation mechanisms. Use of trace flag 1118 may still be required to force uniform allocation behavior.

If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for further assistance in resolving the contention.

Bob Dorr
SQL Server Principal Escalation Engineer

Comments