A recently published KB article Q936185 seems to contradict the recommendation of using trace flag 1118 and multiple tempdb data files with equal sizing to reduce the contention on tempdb object allocation in SQL Server 2005. The article states the following:
In Microsoft SQL Server 2005, you may notice blocking and performance-related problems. You experience this problem if the following conditions are true:
||You enable trace flag 1118.|
||The temporary table creation workload is high. |
||Multiple data files exist for the tempdb database.|
In the sysprocesses system table, the value in the waittype column of the tempdb database is set to PAGELATCH.
And in the workaround section, the article suggests to disable trace flag 1118.
Either I'm reading it wrong, or something is not right. Let me explain. For SQL Server 2000, KB article Q328551 recommends multiple tempdb data files with equal sizing and to enable trace flag 1118. This recommendation is also made for SQL Server 2005, (see whitepaper Working with tempdb in SQL Server 2005), although Microsoft blog posts such as this do mention that there should be less need of implementing the trace flag 1118 in SQL Server 2005 because of its scalability improvement in tempdb.
So, should we use trace flag 1118 or not? Does it introduce contention problem or not?
I decided to run some tests to see how a workload that creates and drops temp tables in high frequency and in massive number would react to trace flag 1118. The test server had four AMD Opteron single-core processors, and tempdb had four equally-sized data files.
I created the following artificial stored procedure which did nothing but to create and drop temp tables. Not a realistic stored procedure, but it served the purpose of creating serious contention on tempdb object allocation when called by sufficient number of concurrent users.
CREATE PROC spStockLevel
SET NOCOUNT ON
create table #tmp1 (i int, j varchar(200), k datetime)
insert #tmp1 values(1, 'abc', '20070812')
drop table #tmp1
create table #tmp2 (i int, j varchar(200), k datetime)
insert #tmp2 values(1, 'abc', '20070812')
drop table #tmp2
create table #tmp3 (i int, j varchar(200), k datetime)
insert #tmp3 values(1, 'abc', '20070812')
drop table #tmp3
I ran this stored procedures in two test scenarios: (1) with trace flag 1118 enabled, and (2) with trace flag 1118 disabled. For each of these two test scenarios, the stored procedure was called by 10, 20, 50, 100, and 200 concurrent users, respectively. And for each configured number of concurrent users, I ran test for 150 seconds. Each of these 150-second test runs was repeated three times.
Note that the workload conformed to what's described in the KB article Q936185 in that (1) the temporary table creation workload was high, and (2) SQL Server was predominantly waiting for PAGELATCH. In fact, the wait stats observed in both test scenarios were almost identical to the following:
|All other waits
During each test run, the four processors on the test server were constantly at ~70%. The values of the perfmon counter SQLServer:Wait Statistics\Page latch wits started/sec were in the 170,000 ~ 185,000 range for 50 concurrent users, for instance.
The following chart summarizes the average transaction throughput in terms of the number of times this stored procedure was executed per second:
This chart shows little to no systematic difference whether or not trace flag 1118 was enabled.
With these test results, I guess I'm not much better off than I was before I read the KB article Q936185--I'm not sure what to do with trace flag 1118. I understand that not every problem of this nature can be easily reproduced in a test. In my test design, though, all the planets seem to have been aligned perfectly for me to expect the described impact.
With a different workload, trace flag 1118 may introduce a detectable problem. But in the tests I have conducted and reported here, trace flag 1118 had no impact in SQL Server 2005. It would help if Microsoft explained why enabling trace flag 1118 in SQL Server 2005 would cause problem when the other Microsoft documents seem to point to a different direction.