THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Reduce the Contention on tempdb with Trace Flag 1118: To Enable, or Not to Enable?

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    
AS
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

select	49
go

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:

Wait Type Wait Distribution
PageLatch_Ex 79.20%
PageLatch_Sh 20.70%
PageLatch_Up 0.10%
All other waits ~0%

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.

Published Tuesday, August 07, 2007 7:34 AM by Linchi Shea

Attachment(s): Trace1118.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

My earlier attempt to see what's going on with trace flag 1118 on SQL Server 2005 SP2 (9.00.3042) didn't

August 10, 2007 8:42 AM
 

Rabia Mansour said:

Maybe the 1118 trace flag has the same performance behaviour as without it, but it helped us once solving blocking issues due to temporary small tables usage.

December 1, 2008 6:01 AM
 

Linchi Shea said:

Here is a SQL Server challenge as I have failed so far: Describe a reproducible workload that would see

October 1, 2009 12:22 PM
 

Linchi Shea said:

I posted the following SQL Server challenge yesterday: Describe a reproducible workload that would see

October 2, 2009 4:42 PM
 

nenea nelu said:

http://support.microsoft.com/kb/328551

"Note Trace flag -T1118 is also available and supported in Microsoft SQL Server 2005 and SQL Server 2008. However, if you are running SQL Server 2005 or SQL Server 2008, you DO NOT have to apply any hotfix."

November 7, 2011 10:51 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement