04 July 2012

Tempdb allocation contention

by Fabiano Amorim

22,031 0 Performance

If your code uses temporary tables, variable tables or dynamic cursors, this metric can help identify whether you have contention on tempdb allocation.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

If your code uses temporary tables, variable tables or dynamic cursors, this metric can help identify whether you have contention on tempdb allocation. Tempdb is shared by all databases and is used for many operations in a SQL Server instance, so it is common for tempdb to cause performance problems.

The T-SQL returns the current number of pagelatch waits on the tempdb database. Any tempdb page allocation/deallocation or any space changing operations (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update the space tracking information. Since most temporary objects are heaps and are less than 64KB, a large number of such operations can cause excessive waits when acquiring latches.

The following query can be used to help you in a further analysis on which type of page (PFS, GAM or SGAM) is causing allocation waits on tempdb:

SELECT a.session_id,
    a.wait_type,
    a.wait_duration_ms,
    a.blocking_session_id,
    a.resource_description,
    CASE
      WHEN CAST(RIGHT(a.resource_description,
                      LEN(a.resource_description)
                      - CHARINDEX(':', a.resource_description, 3)) AS INT)
           - 1 % 8088 = 0 THEN 'Is PFS Page'
      WHEN CAST(RIGHT(a.resource_description,
                      LEN(a.resource_description)
                      - CHARINDEX(':', a.resource_description, 3)) AS INT)
           - 2 % 511232 = 0 THEN 'Is GAM Page'
      WHEN CAST(RIGHT(a.resource_description,
                      LEN(a.resource_description)
                      - CHARINDEX(':', a.resource_description, 3)) AS INT)
           - 3 % 511232 = 0 THEN 'Is SGAM Page'
      ELSE 'Is Not PFS, GAM, or SGAM page'
    END resourcetype,
    c.text AS SQLText
  FROM sys.dm_os_waiting_tasks a
  INNER JOIN sys.sysprocesses b
  ON
    a.session_id = b.spid 
  OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c
  WHERE a.wait_type LIKE 'PAGE%LATCH_%'
    AND a.resource_description LIKE '2:%';

SELECT a.session_id,

a.wait_type,

a.wait_duration_ms,

a.blocking_session_id,

a.resource_description,

CASE

WHEN CAST(RIGHT(a.resource_description,

LEN(a.resource_description)

- CHARINDEX(':', a.resource_description, 3)) AS INT)

- 1 % 8088 = 0 THEN 'Is PFS Page'

WHEN CAST(RIGHT(a.resource_description,

LEN(a.resource_description)

- CHARINDEX(':', a.resource_description, 3)) AS INT)

- 2 % 511232 = 0 THEN 'Is GAM Page'

WHEN CAST(RIGHT(a.resource_description,

LEN(a.resource_description)

- CHARINDEX(':', a.resource_description, 3)) AS INT)

- 3 % 511232 = 0 THEN 'Is SGAM Page'

ELSE 'Is Not PFS, GAM, or SGAM page'

END resourcetype,

c.text AS SQLText

FROM sys.dm_os_waiting_tasks a

INNER JOIN sys.sysprocesses b

a.session_id = b.spid

OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c

WHERE a.wait_type LIKE 'PAGE%LATCH_%'

AND a.resource_description LIKE '2:%';

For more information about tempdb, see http://technet.microsoft.com/en-us/library/cc966545.aspx.

For more information about tempdb contention, see http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/.

Metric definition

Name

Tempdb allocation contention

Description

This metric can help identify whether you have contention on tempdb allocation by measuring pagelatch waits.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			SELECT  COUNT(*) AS cnt
FROM    sys.dm_os_waiting_tasks a
WHERE   a.wait_type LIKE 'PAGE%LATCH_%'
        AND a.resource_description LIKE '2:%';
			
				
					
				
					1
2
3
4
				
						SELECT  COUNT(*) AS cnt
FROM    sys.dm_os_waiting_tasks a
WHERE   a.wait_type LIKE 'PAGE%LATCH_%'
        AND a.resource_description LIKE '2:%';

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Tempdb allocation contention

Description

This alert is raised when the number of pagelatch waits on the tempdb database increases to match the alert thresholds.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	50
Medium:	10
Low:	0

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

22,031 Rate

Click to rate this post!

[Total: 5 Average: 3.6]

Tempdb allocation contention

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics