Tempdb allocation contention

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 SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL 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:

[sql]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:%’;[/sql]

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/.

Tempdb allocation contention, 3.6 out of 5 based on 5 ratings

Metric definition

Name

Tempdb allocation contention

Description

This metric can help identify whether you have contention on tempdb allocation by measuring pagelatch waits. 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 graph shows the number 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 of 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:%';

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

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

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

60

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. If your code uses temporary tables, variable tables or dynamic cursors, an increase in pagelatch waits 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. 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:%';

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

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

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