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

Linchi Shea — Fri, 02 Oct 2009 15:42:00 GMT

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.

Search results matching tags 'Best Practices', 'Trace flags', 'tempdb', and 'Performance'

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