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

The issue itself manifests as a PAGELATCH wait, with 2:1:1 or 2:1:3 as the resource description.
Figure 8-11 shows contention on the allocation pages because multiple users are trying to allocate
many objects at the same time.

TEMPDB

PFS PFS

64MB

!

SGAM SGAM

!

4GB

FIGURE 8-11

Allocation Page Contention: An Example
All the code in this section uses the Ch8_3TempdbContention.sql code i le.

In order to demonstrate page contention I've created a couple of stored procedures and a table in an
empty database called tempdbdemo . If you want to step through the example yourself, we have pro-
vided all the necessary steps and scripts in the associated code i le.

-- Create stored procedure that creates a temp table, a clustered index and
populates with 10 rows
-- The script expects a database called tempdbdemo to exist
USE [tempdbdemo] ;
GO
CREATE PROCEDURE [dbo].[usp_temp_table]
AS
CREATE TABLE #tmpTable
(
c1 INT,
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
-- Create stored procedure that runs usp_temp_table 50 times
CREATE PROCEDURE [dbo].[usp_loop_temp_table]
AS