Day 5–Trace Flag 1118–Allocate Uniform extent to tempdb objects

Have you ever faced allocation contention problems with tempdb database in SQL Server 2000? If yes then you might be ended up with the recommendation to enable trace flag 1118** as per http://support.microsoft.com/kb/328551 or http://support.microsoft.com/kb/936185. By default when you create a temporary object it will allocate a single page to that object searching SGAM however in a large environment if you create temp tables a lot then you might ending up with allocation contention. However if you enable trace flag 1118 then it will check GAM to allocate a uniform extent to the object so that there by reducing the look ups on SGAM page.

That’s all for now on 1118, for more on 1118 trace flag check out Paul’s Misconception article on this trace flag. Check http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx. From SQL Server 2005 this behavior is changed as we have caching of temp tables from SQL 2005 however this trace flag still exists till Denali, incase if you want you can use it. Paul has explained it better so check his article.

Let me enable and show you how it works.

I’m going to enable it as session scoped and not globally. Lets create a temp table without this trace flag and check how many pages is allocated to the temp table

CREATE TABLE #object (name CHAR(4000))
GO
INSERT #object VALUES ('SQL-Articles.com')
GO
EXEC tempdb..sp_spaceused #object

From the image below you can see that only single page is allocated (reserved is 16Kb in which 8 KB is allocated for index) to the object. This is fine for normal environment , consider a highly used tempdb database where objects are created very frequently , at that time this will lead into contention so to avoid this we are going to enable trace flag 1118 as shown below

DBCC TRACEON(1118)
GO
CREATE TABLE #object (name CHAR(4000))
GO
INSERT #object VALUES ('SQL-Articles.com')
GO
EXEC tempdb..sp_spaceused #object

From the image below you can find that a uniform extent is allocated (reserved is 72 KB in which 64 KB is allocated to data and 8 KB for index) during the object creation itself.

Thus you can utilize this trace flag when you face allocation contention in tempdb database. Before enabling just go through the KB article mentioned above to get a clear picture on it.

** THIS IS APPLICABLE TO ALL DATABASES SO CREATING TABLES IN OTHER DATABASES WILL ALSO ALLOCATE UNIFORM EXTENT ONLY