Blog

TempDB is an important part of a healthy database. To keep your database healthy, you can’t just monitor the user databases, you have to pay attention the whole system. Join Jeremiah Peschka and the Brent Ozar PLF team as they take a look at how to monitor an overlooked cause of database health problems:

Queries used:

Monitoring file space used in TempDB:

SELECT  SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
        SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
        SUM (version_store_reserved_page_count)*8 as version_store_kb,
        SUM (unallocated_extent_page_count)*8 as freespace_kb,
        SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM    sys.dm_db_file_space_usage;

Historical information about TempDB usage:

SELECT  top 5 *
FROM    sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC;

Determine which queries are using large amounts of TempDB:

SELECT t1.session_id, t1.request_id, t1.task_alloc,
  t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (SELECT session_id, request_id,
             SUM(internal_objects_alloc_page_count) AS task_alloc,
             SUM(internal_objects_dealloc_page_count) AS task_dealloc
       FROM   sys.dm_db_task_space_usage
       GROUP BY session_id, request_id) AS t1
       JOIN sys.dm_exec_requests AS t2 ON t1.session_id = t2.session_id
                                     AND t1.request_id = t2.request_id
ORDER BY t1.task_alloc DESC;

Perfmon Counters:

• SQLServer:Databases \ Log File(s) Size (KB)
• SQLServer:Databases \ Log File(s) Used (KB)
• SQLServer:Transactions \ Free Space in tempdb (KB)
• SQLServer:Transactions \ Version Store Size (KB)
• SQLServer:Transactions \ Version Generation Rate (KB/s)
• SQLServer:Transactions \ Version Cleanup Rate (KB/s)

TempDB DMV Resources:

• sys.dm_db_file_space_usage
• sys.dm_db_task_space_usage.

Microsoft’s white paper, Working with TempDB, is an additional resource on the TempDB performance.