Total Pageviews

Monday, March 4, 2013

tempdb contention - be on God's side

What is it?
When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages in it. This may cause queries and requests that involve tempdb to be unresponsive for a short period of time. If the page allocation issue creates a chain of blocking, the entire server may become unresponsive for longer periods of time and any further connection to the SQL Server instance may be aborted (time-out). This situation can be observed where workload and concurrency is high.

What happens when contention starts?
Contention generally does not happen for a long period of time, but if the number of blockings is large, then the end-user will experience time-outs repeatedly or queries will take a longer time to finish. Based on the degree of contention, in a worst case scenario, SQL Server Service may need to restart.

When does this happen?
Generally un-optimal coding practices, a lack of understanding about SQL Server Engine’s behavior and being unfamiliar with T-SQL techniques are key triggers for tempdb contention.

At the beginning of application deployment, contention does not show up, but as the client-base (concurrency) increases, application response time will gradually suffer. Once in a while, users start receiving time-out messages and observe infrequent poor application response. This symptom surges as the volume of data and concurrency increases over time.

Symptom Detection:
“tempdb contention” can be observed from Activity Monitor in SSMS or  by examining the Dynamic Management Views (DMV) “sys.dm_exec_request” or “sys.dm_os_waiting_tasks” or “sys.sysprocesses”. During contention, we will see that these requests or tasks are waiting for tempdb resources. The wait type and wait resource point to “PAGELATCH_*” waits on pages in tempdb. These pages might be of the format as "2:1:1" (PFS Page) or "2:1:3" (SGAM Page), etc.

Reasons for tempdb contention:
Following are some well-known and proven reasons why tempdb contention can occur:

1.       Global (##) and local (#) temporary table, indexes on temporary table.
2.       Temporary stored procedures.
3.       Table variable (@), single and multi-statement Table Valued Function (TVF).
4.       Using CURSOR.
5.       Snapshot isolation for row-versioning.
6.       Index creation using SORT_IN_TEMPDB.
7.       Online index operation.
8.       DISTINCT, GROUP BY, ORDER BY, or UNION queries results in work table creation in tempdb.
9.       Tempdb spilling from excessive HASH JOIN, HASH AGRGREEGATE.
10.   Memory pressure and smaller amount of grant memory for query execution.
11.   Work tables creation from DBCC CHECKDB.
12.   Using large object, (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) TEXT, NTEXT, IMAGE, XML) data type variables and parameters.
13.   MARS (multiple active result sets)
14.   Database Engine spool operations such as Lazy Spool, Eager Spool, Index Spool, Row Count Spool, Table Spool, Window Spool, etc.

What to monitor?
There are a number of things that should be monitored to understand tempdb usage over time. Following are the most interest ones and might be helpful.

(a)    PerfMon:
Access Methods\Workfiles Created/sec
Access Methods\Worktables Created/sec
General Statistics\Temp Tables Creation Rate
Latches\Latch Waits/sec

(b)   CPU usage by tempdb: CPU usage of tempdb over time. Following query will provide CPU usage per database.

AS (SELECT Database_id
         , db_name(Database_id) AS [DatabaseName]
         , sum(total_worker_time) AS [CPU_Time_Ms]
      sys.dm_exec_query_stats AS qs WITH (NOLOCK)
      CROSS APPLY (SELECT convert(INT, value) AS [Database_id]
                     attribute = 'dbid') AS css
SELECT dcpu.DatabaseName
     , cast(dcpu.[CPU_Time_Ms] * 1.0 /
         sum(dcpu.[CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
  cteDBCPUStats dcpu
  (Database_id <> 32767)

(c)    Tempdb space usage: A number of tools have been provided in the following MSDN articles.
Troubleshooting Insufficient Disk Space in tempdb:

(d)   In SQL Server: PAGELATCH_*, “Sort Warning”, “Hash Warning”, “Missing Statistics”, Memory Grant per query.

Identify tempdb contention:
Following is the DMV query which can be used to identify any page allocation issues occurring in tempdb.

;WITH ctePage
AS (SELECT r.session_id
         , r.status
         , r.command
         , r.database_id
         , r.blocking_session_id
         , r.wait_type
         , avg(r.wait_time) AS [wait_time]
         , r.wait_resource
         , cast(right(r.wait_resource, len(r.wait_resource) -
                   charindex(':', r.wait_resource, 3)) AS INT) AS page_id

      sys.dm_exec_requests AS r
      INNER JOIN sys.dm_exec_sessions AS s
        ON (r.session_id = s.session_id)

      r.wait_type IS NOT NULL
      AND s.is_user_process = 1

      GROUPING SETS ((r.session_id, r.status, r.command,
                                  r.database_id, r.blocking_session_id,
                                  r.wait_type, r.wait_time, r.wait_resource), ())

SELECT session_id
     , status
     , command
     , database_id
     , blocking_session_id
     , wait_type
     , wait_time
     , wait_resource
     , (CASE
         WHEN page_id = 1 OR page_id % 8088 = 0 THEN
         WHEN page_id = 2 OR page_id % 511232 = 0 THEN
         WHEN page_id = 3 OR (page_id - 1) % 511232 = 0 THEN
           'Other page'
       END) page_type

  session_id IS NOT NULL

How to reduce “tempdb contention”?
Generally there are two ways to reduce contention in tempdb.
(a)    SQL Server configuration
(b)   Optimizing workload

SQL Server configuration:
(a)    Data files in tempdb: Increasing the number of data files based on number of cores.  Based on Microsoft KB2154845, “as a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.”

Many SQL Experts prefer to increase the number of data files based on NUMA node detected by SQL Server Engine. So, if we have 2 NUMA nodes, then we may add two data files at a time. If we have 4 NUMA nodes then, we can add 4 data files at a time. Please note that all data files must be equal in size, this allows for optimal proportional-fill performance and the Log file does not need to be changed.

If we have one file per CPU then SQL Server will create a logical scheduler for each of the CPUs presented to it. The reason for the multi-file TEMPDB recommendation is to allow each of the logical schedulers to loosely align with a file.   Since we can only have one active worker per scheduler this allows each worker to have its own tempdb file and avoid allocation contention. 

(b)   Using Trace Flag -T1118 as startup parameter: This trace flag forces to create uniform extent allocations and removes almost all single page allocations on the server for all databases. The disadvantage of this trace flag is that it creates 8 pages (64KB) and increases database size.
(c)    Memory: Adding more physical memory and increasing “Minimum memory per query (KB)” may help to reduce tempdb spilling and hence some tempdb contention.

Optimizing workload:
Optimizing queries in such a way that it does not require any expensive operation to perform in tempdb is the key to reduce tempdb contention. There are many alternates and T-SQL techniques out there those we can easily adopt in our query to improve OLTP performance dramatically.
(a)    Try to use CTE (Common Table Expression) or In-line table (Derived Table).
(b)   Instead of Table Variable (@), use temporary table (#) when intermediate result processing can’t be avoided.
(c)    Avoid using TVF and put that functionality in the Stored Procedure or in the SQL batch.
(d)   Try to avoid using CURSOR, rather use set based theory.
(e)   Re-evaluate query which are using GROUP BY, ORDER BY, DISTINCT and UNION.
(f)     Make query sargable; create appropriate covering indexes and multi-column statistics.

Producing tempdb contention:
In this exercise we will be producing artificial contention by using TVF and Table Variable. Later, we will try to optimize the same code to avoid tempdb contention.

To create a tempdb contention we need to execute one or more queries in a number of concurrent threads. We will be using “ostress.exe”, which can be downloaded from the following Microsoft site (

This demonstration can be executed in SQL 2005, SQL 2008 and SQL 2012.
Steps to follow:
1.       Create a database “TestDB”.
2.       Create a table and populate it with some dummy data.
3.       Create a TVF and a Stored Procedure.
4.       Call the Stored Procedure using OStress.exe from command line.
5.       Review tempdb contention from SSMS (Activity Monitor) and using the DMV query.

Let’s get started:

1.       Create a brand new database “TestDB”


2.       Create a table “tblLarge” and populate it with 100,000 dummy records
-- create table
    DROP TABLE tblLarge

      xID INT IDENTITY(1, 1) ,
      sName1 VARCHAR(100) ,
      sName2 VARCHAR(1000) ,
      sName3 VARCHAR(400) ,
      sIdentifier CHAR(10) ,
      nWage NUMERIC(20, 2) ,
      sLicense VARCHAR(25)

-- insert 100,000 dummy records
        ( sName1 ,
          sName2 ,
          sName3 ,
          sIdentifier ,
          dDOB ,
          nWage ,
VALUES  ( LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*50) ,     -- sName1
          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*60) ,     -- sName2
          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*70) ,     -- sName2
          LEFT(CAST(NEWID() AS VARCHAR(36)), 2) ,             -- sIdentifier    
          DATEADD(dd, -RAND() * 20000, GETDATE()) ,           -- dDOB
          ( RAND() * 1000 ) ,                                 -- nWage
          SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 6, 7)       -- sLicense
GO 100000

-- create a clustered index

3.       Create a table valued function “myTVF”
-- create TVF
      @sIdentifier VARCHAR(10)
      xID INT NOT NULL ,
      sName1 VARCHAR(100) ,
      sName2 VARCHAR(1000) ,
      sName3 VARCHAR(400)
        INSERT  INTO @myTbl
                SELECT  xID ,
                        sName1 ,
                        sName2 ,
                FROM    tblLarge
                WHERE   sIdentifier = @sIdentifier

4.       Create a Stored procedure “usp_mysp”.
--- craete StoredProcedure
        DECLARE @tblLarge TABLE
              xID INT ,
              sName1 VARCHAR(100) ,
              sName2 VARCHAR(1000) ,
              sName3 VARCHAR(400) ,
              sIdentifier CHAR(10) ,
              dDOB DATETIME ,
              nWage NUMERIC(20, 2) ,
              sLicense VARCHAR(25)

            INSERT  INTO @tblLarge
                    ( xID ,
                      sName1 ,
                      sName2 ,
                      sName3 ,
                      sIdentifier ,
                      dDOB ,
                      nWage ,
                    SELECT  a.*
                    FROM    tblLarge a
                            INNER JOIN dbo.fn_myTVF('AA') b ON a.xID = b.xID
                                  WHERE a.nWage>=100
                    -- OPTION  ( RECOMPILE )

5.       Use the OStress.exe from the command line to simulate 100 users and 100 iterations per user.

ostress -SSHB\SQL2008 -E -dTestDB -Q"exec usp_runtvf" –n100 -r100

“tempdb contention” reported in SSMS:

“tempdb contention” examine using DMV query:

While implementing practices associated with tempdb contention might help, it is equally important to optimize query to eliminate the root cause of this contention. Please keep in mind that slicing, dicing tempdb has limitations which may introduce extra I/O operations.

Placing Tempdb in a faster drive such as RAID10 or RAID1 is a good practice, but splitting tempdb into multiple files according to CPU is a workaround. Workaround works for some time, while optimizing workload will always work.  

Thus, “sir, my concern is not whether God is on our side; my greatest concern is to be on God's side”. SQL Server performance tuning is all about understanding how SQL Server and all relevant parts work together, satisfying its requirements, not going against it. So, try to tune the query as much as possible.

Read More:
Capacity planning for tempdb

Optimizing tempdb Performance

Troubleshooting Insufficient Disk Space in tempdb

Concurrency enhancements for the tempdb database

Recommendations to reduce allocation contention in SQL Server tempdb database

1 comment:

  1. Nicely explained....Very good article reagridng tempdb contection.