Tempdb allocation contention

| 0 comments | 14,059 views
Categories:

VN:F [1.9.17_1161]
Rating: 3.6/5 (5 votes cast)

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:%';

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

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

Enter the T-SQL query that will collect data:
SELECT  COUNT(*) AS cnt
FROM    sys.dm_os_waiting_tasks a
WHERE   a.wait_type LIKE 'PAGE%LATCH_%'
        AND a.resource_description LIKE '2:%';
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
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 thresholds
Default threshold values:
Set the thresholds as follows:
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:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 3.6/5 (5 votes cast)