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: Take 2

My earlier attempt to see what's going on with trace flag 1118 on SQL Server 2005 SP2 (9.00.3042) didn't take me too far because the results were not conclusive. I was motivated by seemingly conflicting suggestions from KB article Q936185 and whitepaper Working with tempdb in SQL Server 2005.

The KB article lists the following as conditions that may cause performance problem:

You enable trace flag 1118.
The temporary table creation workload is high.
Multiple data files exist for the tempdb database.

I took the second condition above to literally mean temp table creation workload instead of page allocation workload inside a temp table, even though T1118 was introduced to reduce the contention caused by page allocation--more precisely single page allocation.

It was suggested that to see the impact of T1118, I should increase the page allocation workload in the test procedure (thanks, Adam Machanic and Kalen Delaney). So I changed the test stored procedure to the following:

CREATE PROC spStockLevel    
AS
SET NOCOUNT ON

create table #tmp1 (i char(8000) NOT NULL)
create table #tmp2 (i char(8000) NOT NULL)

declare @i int
set @i = 1
while @i < 20
begin
insert #tmp1 values('abc')
set @i = @i + 1
end

set @i = 1
while @i < 20
begin
insert #tmp2 values('abc')
set @i = @i + 1
end
drop table #tmp1
drop table #tmp2

select	49
go

I also decided to limit the load levels to just low and high. For the low load level, I used 20 concurrent users, and for the high load level, I used 400 concurrent users. My revised test matrix was as follows:

               Trace Flag T1118
  Users 20 Users with T1118 20 Users without T1118
400 Users with T1118 400 Users without T1118

So there are four test scenarios. For each test scenario, I ran the same test at least 6 times on the same server. Note that the test server had four single-core processors with 16GB of memory, and tempdb had four 1GB data files.

The results came out very consistent (See Fig.1 and Fig. 2 below):

The test results in Fig.1 and Fig. 2 show that:

  • With lower load level (i.e. 20 concurrent users), the transaction throughput was higher--about 10% higher--without enabling T1118 than it was with T1118 enabled (see Fig. 1),
  • With higher load level (i.e. 400 concurrent users), the impact was the opposite. The transaction throughput was higher when T1118 was enabled than it was when T118 was not enabled, again about 10% higher (see Fig. 2).

As mentioned, this pattern was consistently reproducible with the revised test stored procedure.

The change in waitstats was also interesting. Checking master..sysprocesses during test runs for each test scenario revealed that most waits were on PAGELATCH_UP, and the resources PAGELATCH_UP was waiting on were either the SGAM pages or the PFS pages. The details are as follows:

  • With 20 users and without T1118, the waits were predominantly on SGAM (e.g. page 2:3:3),
  • With 20 users and with T1118, the waits were predominantly on PFS (e.g. page 2:3:1),
  • With 400 users and without T1118, the waits were mostly on SGAM, and
  • With 400 users and with T118, the waits were on PFS (e.g. 2:5:16176 and 2:3:88968)

Okay, have we learned anything useful and practical from these tests? Yes.

If you read KB article Q936185, you may be tempted to just disable T1118 on your SQL Server 2005 instances. Well, the test results suggest that things are not that clear cut. A blanket recommendation to either enable or disable T1118 may be too simplistic. 

On the other hand, even though the test results may appear to suggest that you should disable T1118 if the tempdb allocation load level is low and enable it if the load level is high, it may be difficult to map the threshold of the test workload to that of a real application. The safest approach is to test the trace flag with your application workload, and then decide to turn it on or off.

Published Friday, August 10, 2007 9:12 AM by Linchi Shea

Attachment(s): T1118_2.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

 

JohnW said:

I know this is an old post at this point...

I'm not sure I drew the same conclusion as you did.  I can be up to ~18% faster w/o 1118, iff i have a lower load level.  But if i have a lower load level, I'm probably not that worried about performance.  When I do have a higher load level, I am worried about performance, and ~13% slower would catch my eye.

June 5, 2008 9:40 AM
 

Linchi Shea said:

JohnW;

Points taken. Of course, it's more critical when the load is high.

September 25, 2009 6:23 PM
 

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

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