Misconceptions around TF 1118

[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer required and has no effect.]

There’s a lot of confusion and misconceptions about trace flag 1118. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.

There are multiple points of confusion, which I’ll address in turn. Then I’ll prove that the trace flag still works in all versions of SQL Server up to and including SQL Server 2014.

1) Why was the trace flag usually required in 2000? In SQL 2000, whenever a temp table is created in tempdb and a row inserted, an IAM page must be allocated and a single data page must be allocated. These two pages are both ‘single-page’ allocations, from a mixed extent (see Inside The Storage Engine: Anatomy of an extent for more info). This means that an SGAM allocation bitmap page must be accessed, and a PFS page must be accessed (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps for more info).

With lots of very small temp tables being created, this means the very first SGAM page and the very first PFS page in the data file are accessed/changed by all the threads, leading to latch contention problems on these two pages. When the temp tables are deleted again, the various pages are deallocated, which again needs to access and change the PFS page, and potentially the SGAM page.

There are two ways to alleviate this problem. Firstly, create multiple data files in tempdb – which splits the latch contention over multiple allocation bitmaps (from having allocations come from multiple files) and thus reduces the contention. The general rule of thumb was one tempdb data file for each processor core. Secondly, turn on TF1118, which makes the first 8 data pages in the temp table come from a dedicated extent. This means one extent is allocated from the GAM page, rather than 8 single pages (and potentially 8 accesses to the SGAM page). The pages within the extent are reserved and allocated singly from this extent, as needed. This also cuts down on contention and is documented in KB 328551.

2) What does reserved vs. allocated mean? When an extent is allocated to a table, the 8 pages in the extent are not immediately allocated as well. Allocating an extent means those 8 pages are reserved exclusively for subsequent allocation to that table. The pages are allocated individually as needed, but no other table can allocate them. This is why such extents are called ‘dedicated’ extents (see my blog post link above for more details). You can see the counters of reserved pages vs. allocated pages in the output from sp_spaceused.

3) Why is tempdb allocation contention maybe not so much in 2005-2014? In SQL Server 2005, my team changed the allocation system for tempdb to reduce the possibility of contention. There is now a cache of temp tables. When a new temp table is created on a cold system (just after startup) it uses the same mechanism as for SQL 2000. When it is dropped though, instead of all the pages being deallocated completely, one IAM page and one data page are left allocated, and the temp table is put into a special cache. Subsequent temp table creations will look in the cache to see if they can just grab a pre-created temp table ‘off the shelf’. If so, this avoids accessing the allocation bitmaps completely. This can lead to a big drop in contention, as long as the temp tables can be cached, and the query plan being dropped from memory doesn’t cause the cached temp table to be removed. Paul White (blog|twitter) blogged a really in-depth explanation of the cache – see here. 

4) Does the trace flag still exist in 2005-2014? Yes it does – KB 328551 clearly states:

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.

Just to make extra-sure (as I’m always paranoid about saying absolutes), I checked with my good friend Ryan Stonecipher, who’s the dev lead for the team that owns allocation (and a bunch of other stuff, including DBCC). He confirmed the code is exactly the same in 2008 as it was in 2005. And I prove it to you below too. There have been no changes in 2008R2, 2012, or 2014 that would negate the need for trace flag 1118.

[Edit 2012:] 4a) What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.

5) And why is it still there in 2005-2014? It does the same thing in 2005-2014 as it did in 2000. If the temp table creation/deletion workload is high enough, you can still see latch contention, as the temp table cache won’t be enough to completely alleviate the need for creating actual new temp tables, rather than just being able to grab one ‘off the shelf’. In that case, using the trace flag to change to extent-based allocation (in *exactly* the same way as for 2000) can help, as can creating more tempdb data files.

As far as data files go though, the number has changed. Instead of a 1-1 mapping between processor cores and tempdb data files (*IF* there’s latch contention), now you don’t need so many. [Edit 2012:] The advice I like to use now is that which Bob Ward from CSS gave out at PASS in 2011: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, start with 8 files and if there’s still latch contention, add in groups of 4 more files. This is documented Microsoft advice in KB article 2154845.

6) Why does DBCC IND still show two pages, even with the trace flag on? I’ve heard of some people being confused by the output of DBCC IND when the trace flag is turned on. Creating a single row temp table will only show two pages allocated in the DBCC output – one IAM page and one data page. Yes, that’s completely correct – as only two pages are allocated, but the data page comes from a dedicated extent, not a mixed extent. (IAM pages are *always* single-page allocations from mixed-extents).

And now the proof, on SQL 2014.

SELECT @@VERSION;
GO

Microsoft SQL Server 2014 – 12.0.4422.0 (X64)

First off, I’ll create a temp table without the trace flag enabled, and see what pages the table has allocated, by looking at the first IAM. I’ll use a temp table with an 8000+ byte row size, and insert two rows – so we have two data pages for clarity.

DBCC TRACEOFF (1118, -1);
GO

USE tempdb;
GO

CREATE TABLE #temp (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT ‘a’);
GO
INSERT INTO #temp DEFAULT VALUES;
GO 2

Now I’ll figure out what is the first IAM page, using my sp_AllocationMetadata script (see here for the script and details), and dump it with DBCC PAGE to see the single-page allocations it’s tracking, and which dedicated extents are allocated to the table:

EXEC sp_AllocationMetadata ‘#temp’;
GO

Object Name    Index ID  Alloc Unit ID        Alloc Unit Type  First Page  Root Page  First IAM Page
————– ——— ——————– —————- ———– ———- —————
#temp__<snip>  0         1441151881544663040  IN_ROW_DATA      (1:289)     (0:0)      (9:10)

DBCC TRACEON (3604);
GO
DBCC PAGE (‘tempdb’, 9, 10, 3);
GO

<snip>

IAM: Single Page Allocations @0x000000001447A08E

Slot 0 = (1:289)                     Slot 1 = (8:16)                     Slot 2 = (0:0)
Slot 3 = (0:0)                       Slot 4 = (0:0)                       Slot 5 = (0:0)
Slot 6 = (0:0)                       Slot 7 = (0:0)                      
IAM: Extent Alloc Status Slot 1 @0x000000001447A0C2

(1:0)        – (1:3192)     = NOT ALLOCATED   

As you can clearly see from the partial output of the dump of the IAM page, there are two single-page allocations and no extents allocated to the temp table. This is what should happen when the trace flag is not enabled.

Now I’ll do the same thing with the trace flag 1118 enabled.

USE tempdb;
GO

DROP TABLE #temp;
GO

DBCC TRACEON (1118, -1);
GO

CREATE TABLE #temp (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT ‘a’);
GO
INSERT INTO #temp DEFAULT VALUES;
GO 2

EXEC sp_AllocationMetadata ‘#temp’;
GO

Object Name    Index ID  Alloc Unit ID        Alloc Unit Type  First Page  Root Page  First IAM Page
————– ——— ——————– —————- ———– ———- —————
#temp__<snip>  0         1513209475623550976  IN_ROW_DATA      (8:96)     (0:0)      (7:11)

DBCC TRACEON (3604);
GO
DBCC PAGE (‘tempdb’, 7, 11, 3);
GO

<snip>

IAM: Single Page Allocations @0x000000001A78A08E

Slot 0 = (0:0)                       Slot 1 = (0:0)                       Slot 2 = (0:0)
Slot 3 = (0:0)                       Slot 4 = (0:0)                       Slot 5 = (0:0)
Slot 6 = (0:0)                       Slot 7 = (0:0)                      
IAM: Extent Alloc Status Slot 1 @0x000000001A78A0C2

(8:0)        – (8:88)      = NOT ALLOCATED
(8:96)      –              =     ALLOCATED
(8:104)      – (8:3192)     = NOT ALLOCATED                              

Now as you can clearly see, there are no single-page allocations, and there’s a single extent allocated to the table. Proof that trace flag 1118 still does exactly what it should in SQL Server 2014.

Now for a DBCC IND on the table: 

DBCC IND (‘tempdb’, ‘#temp’, -1);
GO

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
——- ———– —— ———– ———– ———–
7       11          NULL   NULL        -1354579385 0
8       96          7      11          -1354579385 0
8       97          7      11          -1354579385 0

(I’ve removed some of the trailing columns for clarity.) We see that it still only lists the two data pages (8:96, 8:97) and the IAM page (7:11) – although an entire extent was allocated to the temp table, only two pages from the extent were actually allocated and used – the rest are reserved for use by that table, but remain unallocated. 

Hopefully this post has cleared up a lot of the confusion around this trace flag and what it does.