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.
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.
;
WITH cteDBCPUStats
AS (SELECT Database_id
, db_name(Database_id) AS [DatabaseName]
, sum(total_worker_time) AS [CPU_Time_Ms]
FROM
sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT convert(INT, value) AS [Database_id]
FROM
sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE
attribute = 'dbid') AS css
GROUP BY
Database_id
)
SELECT dcpu.DatabaseName
, cast(dcpu.[CPU_Time_Ms] * 1.0 /
sum(dcpu.[CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM
cteDBCPUStats dcpu
WHERE
(Database_id <> 32767)
ORDER BY
dCPU.CPU_Time_Ms DESC
(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
FROM
sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON (r.session_id = s.session_id)
WHERE
r.wait_type IS NOT NULL
AND s.is_user_process = 1
GROUP BY
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
'PFS_PAGE'
WHEN page_id = 2 OR page_id % 511232 = 0 THEN
'GAM_PAGE'
WHEN page_id = 3 OR (page_id - 1) % 511232 = 0 THEN
'SGAM_PAGE'
ELSE
'Other page'
END) page_type
FROM
ctePage
WHERE
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.
Take a look here: http://en.wikipedia.org/wiki/Sargable
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 (http://www.microsoft.com/en-us/download/details.aspx?id=8161).
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”
CREATE DATABASE TestDB
GO
USE TestDB
GO
2. Create a table “tblLarge” and populate it with 100,000 dummy records
-- create table
IF OBJECT_ID('tblLarge') IS NOT NULL
DROP TABLE tblLarge
GO
CREATE TABLE tblLarge
(
xID INT IDENTITY(1, 1) ,
sName1 VARCHAR(100) ,
sName2 VARCHAR(1000) ,
sName3 VARCHAR(400) ,
sIdentifier CHAR(10) ,
dDOB DATETIME ,
nWage NUMERIC(20, 2) ,
sLicense VARCHAR(25)
)
GO
-- insert 100,000 dummy records
SET NOCOUNT ON
INSERT INTO tblLarge
( sName1 ,
sName2 ,
sName3 ,
sIdentifier ,
dDOB ,
nWage ,
sLicense
)
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
ALTER TABLE dbo.tblLarge ADD CONSTRAINT PK_tblLarge PRIMARY KEY CLUSTERED (xID)
GO
3. Create a table valued function “myTVF”
-- create TVF
CREATE FUNCTION dbo.fn_myTVF
(
@sIdentifier VARCHAR(10)
)
RETURNS @myTbl TABLE
(
xID INT NOT NULL ,
sName1 VARCHAR(100) ,
sName2 VARCHAR(1000) ,
sName3 VARCHAR(400)
)
AS
BEGIN
INSERT INTO @myTbl
SELECT xID ,
sName1 ,
sName2 ,
sName3
FROM tblLarge
WHERE sIdentifier = @sIdentifier
RETURN
END
GO
4. Create a Stored procedure “usp_mysp”.
--- craete StoredProcedure
CREATE PROCEDURE usp_runtvf
AS
BEGIN
SET NOCOUNT ON
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)
)
BEGIN
INSERT INTO @tblLarge
( xID ,
sName1 ,
sName2 ,
sName3 ,
sIdentifier ,
dDOB ,
nWage ,
sLicense
)
SELECT a.*
FROM tblLarge a
INNER JOIN dbo.fn_myTVF('AA') b ON a.xID = b.xID
WHERE a.nWage>=100
-- OPTION ( RECOMPILE )
END
END
GO
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:
Summary:
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
Nicely explained....Very good article reagridng tempdb contection.
ReplyDelete