THE SQL Server Blog Spot on the Web

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

Linchi Shea

Checking out SQL Server via empirical data points

SQL Server challenge: show me trace flag 1118 is significant -- a quick update

I posted the following SQL Server challenge yesterday:


Describe a reproducible workload that would see significant throughput improvement when trace flag 1118 is enabled.


In response, Konstantin Korobkov wanted to know whether the number of data files in tempdb would make a difference.


I did test that scenario and did not see any difference. But I didn’t record the results. So I didn’t have any specific numbers to show. To be absolutely sure, I thought it would be better to reconfirm that, and I decided to run the same test: (1) with spT1118_v2 (see the previous post for the code), (2) with a single data file in tempdb, and (3) with everything else being the same.


The following chart summaries the test results with tempdb having a single data file:



Just the same as the results from the other scenarios in the previous posts ([1], [2], and [3]), there was no significant difference whether or not trace flag 1118 was enabled.


So the challenge stands.

Published Friday, October 2, 2009 12:42 PM by Linchi Shea

Attachment(s): T1118_SQL2008_single.GIF



Simon Rapier said:

Linchi, I don't believe T1118 will make much, if any difference, if you are not encountering SGAM contention.

October 5, 2009 10:41 AM

Simon Rapier said:

in other words, you need a workload that generates huge allocation/deallocation of mixed extents as this is when the SGAM page is changed. Each SGAM cover 63904 extents and of course each extent is 8 pages, so ~4GB of data. If you could contrive a workload that generated a lot of SGAM modification, you should see benefit from trace flag 1118 as this effectively forces uniform extent allocation only.

October 6, 2009 5:21 PM

Everest said:

I'm finding that it's a good idea to look at tempdb trans/sec as a % of total trans/sec to know whether tempdb even needs to be fooled with at all, given the particular application (ie adding files, switches, et. al.).

Nice read, thanks.


October 19, 2009 10:32 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement