Introduction

This question came up in a recent twitter conversation and Paul Randal clarified it at that time. I will make an attempt to attest to that theory as a supportive evidence using a demo.

Most of the readers should already be familiar with this Trace Flag and if NOT take a moment to read these excellent posts on this topic.

Misconceptions around TF 1118

SQL Server tempdb configuration

Concurrency enhancements for the tempdb database

Background

With frequent creating and dropping of several temp tables + work tables (created by SQL Server internally to support GROUP BY, ORDER BY, CURSORS, HASH PLANS etc) concurrently, there will be allocation bitmap contention in tempdb i.e look at the SGAM page to figure out which mixed extents have at-least one free page and look at the PFS page to figure out which pages in the mixed extent are free. To avoid this contention, it is recommended to create multiple files of same size even on the same spindles and if necessary use TF 1118 also at the expense of some additional disk space. What this does is instead of allocation a single page from a mixed extent, a full extent is allocated for requests.

And the question is does this affect user databases as well or only the tempdb?

Demo

We will run a demo in user database + tempdb before and after the TF 1118 change and look at the evidence before making a conclusion. We will make sure there are no trace flags currently set in the instance by using the below code snippet. And it shouldn’t return any rows back and we made sure there is no interference for our demo.

DBCC TRACESTATUS ()
GO
--Results should be like below
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now, let’s go ahead and create a new database for this demo and create one table and add some data to setup the test.

--create database
CREATE DATABASE TF1118
GO
USE TF1118
GO
--drop if exists and create the table
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
DROP TABLE dbo.TestMe
GO
CREATE TABLE dbo.TestMe ( c1 BIGINT, c2 DATETIME)
GO
--add some data and checkpoint just to flush the data
SET NOCOUNT ON;
INSERT dbo.TestMe
	SELECT 1, getdate()
GO 10
CHECKPOINT
GO
SELECT * FROM dbo.TestMe
GO

Next we will explore the page_ids associated with the table using DBCC EXTENTINFO and look at some internals of the allocation status using DBCC PAGE.

--Look at the page_id numbers allocated for the table using EXTENTINFO
DECLARE
	@DBID INT
	, @ObjectID INT

SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestMe')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO
--Grab the page_id from the above results and use below for the DBCC PAGE
DECLARE @DBID INT

SELECT 	@DBID = DB_ID()
DBCC TRACEON (3604)
DBCC PAGE (@DBID, 1, 153, 3)

DBCC EXTENTINFO results of user database before TF 1118
Allocation status of user database before TF 1118

Internals

Before trying to understand the above data, I encourage the readers to look over the below posts to understand the information associated with the PFS bits.

Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

Managing Extent Allocations and Free Space

In our example below, we have 0×61 which means the page is allocated (40) + is coming from a mixed extent (20) + and is 50 percent full (1). i.e 40 + 20 + 1 = 61 which co-relates to 0×61 in the PFS bits.

I am NOT going to reiterate the details on how to understand these bits in detail here, so please go ahead and read Paul’s excellent post above. There is lot of information over there.

Back to demo

Armed with these details, let’s explore how it looks if we create the table in tempdb.

USE tempdb
GO
--drop if exists and create the table
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
CREATE TABLE dbo.TestMe ( c1 BIGINT, c2 DATETIME)
GO
--add some data and checkpoint just to flush the data
INSERT dbo.TestMe
	SELECT 1, getdate()
GO 10
CHECKPOINT
GO
SELECT * FROM dbo.TestMe
GO
--Look at the page_id numbers allocated for the table using EXTENTINFO
DECLARE
	@DBID INT
	, @ObjectID INT

SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestMe')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO
--Grab the page_id from the above results and use below for the DBCC PAGE
DECLARE @DBID INT

SELECT 	@DBID = DB_ID()
DBCC TRACEON (3604)
DBCC PAGE (@DBID, 1, 202, 3)

DBCC EXTENTINFO results of tempdb before TF 1118
Allocation status in tempdb before TF 1118

From the above, we noticed that the tables we created in both user database and tempdb are coming from Mixed extents as expected.

Now, let’s configure the trace flag TF 1118 using the below code so that it affects all sessions for this instance. And note that we don’t have to re-start the instance to use the trace flag but also this setting won’t survive a re-start either. Use it cautiously.

--set the trace flag TF 1118 using -1 flag to affect all sessions
DBCC TRACEON (1118, -1)

Now, let’s re-run the above code in tempdb again and look at the results. From the DBCC EXTENTINFO, it is clear that we are getting a uniform extent by looking at ext_size = 8 and the number of pages allocated is pg_alloc = 1. Further more if you look at the PFS bits, it is 0×41 where it shows it is allocated (40) + 50 percent full (1). Note the bit that shows if it is mixed extent (20) is missing, which is very good and what is expected from this Trace flag TF 1118.

DBCC EXTENTINFO after TF 1118 in tempdb

Allocation status in tempdb after TF 1118

For the last part of the demo, let’s look at the same information in the user database.

DBCC EXTENTINFO after TF 1118 in user database

Allocation status after TF 1118 in user database

Understanding the results

From the above demo, we have seen that this trace flag also changes the behavior in user databases as well as in tempdb. But should you be concerned about this behavior change in user databases? Absolutely NO, unless you are dropping and creating user created tables at the rate comparable to tempdb and worried about disk space. The performance benefits of enabling this trace flag TF 1118 is only applicable to tempdb.

And before we leave, let’s drop the database.

USE tempdb
GO
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
USE TF1118
GO
--drop if exists and create the table
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
USE master
IF DB_ID('TF1118') IS NOT NULL
	DROP DATABASE TF1118
GO