Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 228

CREATE TABLE #tmpTable
(
c1 INT UNIQUE CLUSTERED,
c2 INT,
c3 CHAR(5000)
) ;
--CREATE UNIQUE CLUSTERED INDEX cix_c1 ON #tmptable ( c1 ) ;
DECLARE @i INT = 0 ;
WHILE ( @i < 10 )
BEGIN
INSERT INTO #tmpTable ( c1, c2, c3 )
VALUES ( @i, @i + 100, 'coeo' ) ;
SET @i += 1 ;
END ;
GO
Here, a unique clustered constraint has been added to the c1 column, which SQL Server will enforce
internally by using a clustered index, so you can keep exactly the same functionality.
Testing the new stored procedure using the temporary table creation test now returns the following
result:
Temp tables created during the test: 1
The stored procedure has been successfully optimized for temporary object reuse, but what effect
will it have on the allocation page contention example earlier in this chapter? Run through the
workload again and see for yourself.
This example has shown you two ways to tackle a tempdb page contention issue: adding more data
i les and temporary object reuse. Taking advantage of temporary object reuse doesn't remove the
issue because of the large number of concurrent connections trying to use the object name, so adding
additional tempdb data i les is still required to balance the allocation requests.
Trace Flag 1118
This trace l ag was introduced in SQL Server 2000 to help alleviate contention on the SGAM
page (2:1:3) by disabling mixed extent allocations in all databases.
You might remember from earlier in the chapter that SGAM pages track mixed extents that have
free space available. Every time you create a new table that's not big enough to i ll an extent (which
happens a lot in tempdb), the SGAM page is read to i nd a mixed extent with enough free space to
allocate to your table.
The effect of enabling this trace l ag is that every object you create will be allocated its own extent
(a uniform extent). The only downside to this is the extra disk space that's needed because every
table needs at least 64KB; although that's unlikely to be an issue on most systems.
SQL Server 2008 introduced an improved algorithm for allocating space in mixed extents, so you'll
be unlikely to encounter this issue often if at all with SQL Server 2012.
Even though you're unlikely to i nd SGAM contention in SQL Server 2012, trace l ag 1118 still
works exactly the same: It disables mixed extent allocations.