I don’t have a SAN, so how many tempdb files do I need? Part 2 — PFS, GAM, and SGAM contention

In the previous article I don’t have a SAN, so how many tempdb files do I need? Part 1 — When splitting tempdb into to many files can be counterproductive to performance we saw that splitting tempdb into multiple files can be problematic. Now, it is time to have a closer look at the most common tempdb problem, the one that leads to the recommendation about dividing this database into multiple files.

tempdb allocation

When a temporary object is created, SQL Server has to allocate space in tempdb for it. By default, for small objects a mixed extent is used (an extent which store pages allocated to many objects). Doing this requires:

1. Searching SGAM (Shared Global Allocation Map) pages for mixed extent with available space.

2. Searching and updating PFS (Page Free Space) pages — the ones that contain information about available space in allocated pages.

3. Updating IAM (Index Allocation Map) pages — the ones that store a bitmap where the bit is set if the extent is allocated to the objects’ IAM chain).

When an object which is being created is larger than 8 pages, the algorithm changes slightly:

1. SQL Server searches and updates GAM (Global Allocation Map) pages to allocate uniform extent.

2. Then, PFS pages are searched and updated.

3. Finally, IAM pages are updated.

As you can see, tempdb allocation heavily uses the same pages over and over again. And there are only one GAM and SGAM pages for 4 GB file and only one PFS page for approximately 64 MB. Because all those pages have to be latched and locked when many temporary object are being created at the same time (don’t forget about internal objects), high wait times occur. What makes things even worse, those objects are probably destroyed as well. And this require de-allocation:

1. Any page de-allocation requires PFS update.

2. A page de-allocation may lead to extent de-allocation, so GAM pages (for de-allocating uniform extents) and SGAM and GAM pages (for de-allocating mixed extents) also have to be updated.

The solutions

There are only three solutions that can solve tempdb allocation contention:

1. You should use temporary object caching (for more information, have a look at this post: The Meaning of Stored Procedures. Part 2 — Avoiding recompilations due to plan stability-related reasons).

2. You can set trace flag 1118 and force uniform extent allocations instead of mixed page allocations. There is some confusion about this trace flag due to KB article 936185 which states “You do not have to have trace flag 1118 after you install this hotfix. However, you can still use trace flag 1118.”. To clarify — trace flag 1118 has not been deprecated and still can be used for solving tempdb allocation contention problems.

3. You can also split tempdb into multiple files. This is the only way to add additional PFS, GAM and SGAM pages to tempdb.

Let me compare those three solutions.

The test

The best way to asses and compare those solutions is to put them under test. I am going to execute multiple times those two stored procedures borrowed from SQL Server 2008 Internals and Troubleshooting book: with and without trace flag 1118 and with one and four tempdb files.

CREATE PROCEDURE uspAllocTestv1
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 PROCEDURE uspAllocTestv2
AS 
    CREATE TABLE #tmpTable
        (
          c1 INT PRIMARY KEY,
          c2 INT,
          c3 CHAR(5000)
        ) ;
    DECLARE @i INT = 0 ;
    WHILE ( @i < 10 ) 
        BEGIN
            INSERT  INTO #tmpTable ( c1, c2, c3 )
            VALUES  ( @i, @i + 100, ''coeo'' ) ;
            SET  @i += 1 ;
        END ;
GO

 

In addition, I am going to use one, ten and hundred simultaneous connections. What will be measured is the throughput (number of iteration per second) and wait time for latching PFS, GAM and SGAM pages — those latches show up as PAGELATCH_EX, PAGELATCH_UP and PAGELATCH_SH wait types in sys.dm_os_wait_stats dynamic view.

Note, those wait types are PAGELATCH, not PAGEIOLATCH, which means they have nothing to do with I/O subsystem performance — SQL Server is not waiting for reading or writing pages, only for accessing pages that are already in memory. You can also see those waits, but only for the currently waiting tasks, with the following query (as allocation contention is evenly distributed between all PFS, GAM and SGAM pages, most of the time you can safely check only the first pages):

SELECT * 
FROM sys.dm_os_waiting_tasks 
WHERE resource_description = ''2:1:1'' 
    or resource_description = ''2:1:2'' 
    or resource_description = ''2:1:3'';

 

The test server was equipped with Intel i7-3770K 3.5 GHz processor, 16 GB of RAM, and the tempdb was placed on a SSD drive running 64-bit Windows 7 and SQL Server 2012.

The first test is done with one tempdb file and without trace flag 1118. Before each execution wait stats were cleared (to clear wait stats, execute following command):

DBCC SQLPERF(''sys.dm_os_wait_stats'',clear);

 

The following chart shows the results:

image

Then the 1118 trace flag was set, and the test was repeated:

DBCC TRACEON (1118,-1);
DBCC TRACESTATUS(-1);

 

image

Finally, it’s time to split tempdb into multiple files — my server has 4 cores, so I’m going to use 4 files. Note, that I don’t have to explicitly switch off trace flag 1118, because SQL Server will be restarted anyway:

USE [master]
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N''tempdev'', FILENAME = N''c:sqltempdb.mdf'' , 
SIZE = 125MB , FILEGROWTH = 10MB )
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N''tempdev2'', FILENAME = N''c:sqltempdb2.ndf'' , 
SIZE = 125MB , FILEGROWTH = 10MB )
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N''tempdev3'', FILENAME = N''c:sqltempdb3.ndf'' ,
SIZE = 125MB , FILEGROWTH = 10MB )
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N''tempdev4'', FILENAME = N''c:sqltempdb4.ndf'' , 
SIZE = 125MB , FILEGROWTH = 10MB );
GO

 

This time the same stored procedures performed even better:

image

Conclusions

Let me summarize what we have seen:

1. The best results in all configuration (in terms of scalability) are achieved by using temporary object caching mechanism available in SQL Server 2005 and onwards. The performance gain for 100 simultaneously executed queries varies from 60% when trace flag 1118 was used, to 75% when tempdb was split into multiple files (remember, all files were on a SSD drive).

2. Enabling trace flag 1118 gave from 10% (for 10 simultaneously executed queries) to 25% (for 100 simultaneously executed queries) performance boost, regardless of using or not the temporary object caching mechanism.

3. Splitting tempdb into multiple files when all of them are equally sized and placed on a fast SSD drive, provided from 30% (for 10 simultaneously executed queries) to almost 40% (for 100 simultaneously executed queries) performance boost, regardless of using or not the temporary object caching mechanism.

Seems like:

1. If your SQL Server is used by very few concurrent users, the default configuration doesn’t have to be changed at all. In other words, splitting tempdb into multiple file is a troubleshooting technique and there is no point of doing this if you are not experiencing tempdb allocation contention problems.

2. If you are experiencing tempdb allocation problems, first force your programmers to follow best practices described in this blog series: The Meaning of Stored Procedures. You will not only solve the original problem, but also speed up the overall stored procedures execution time and potentially free up a lot of wasting memory.

3. Being short of budget, turn on trace flag 1118. Splitting tempdb into multiple files when you don’t have a fast enough drive may not be the best solution.

4. Heavily used SQL Servers should use multiple tempdb files. And if your server serves hundreds of concurrent users, there is no excuse — you have to have a dedicated fast drive for tempdb.

Hope that helps

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, SQL Server, tempdb and tagged , , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

One thought on “I don’t have a SAN, so how many tempdb files do I need? Part 2 — PFS, GAM, and SGAM contention

  1. Pingback: yiyi''s blog - Just another Journalius site