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 Unlimited® 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,<br />        SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,<br />        SUM (version_store_reserved_page_count)*8 as version_store_kb,<br />        SUM (unallocated_extent_page_count)*8 as freespace_kb,<br />        SUM (mixed_extent_page_count)*8 as mixedextent_kb<br />FROM    sys.dm_db_file_space_usage;<br />

Historical information about TempDB usage:

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

Determine which queries are using large amounts of TempDB:

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

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:

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

↑ Back to top
  1. Shouldn’t we also track “Data File(s) Size (KB)” for tempdb in perfmon? Tempdb keeps allocating more and more space and fills our data drive … I’m trying to figure out the best preventive action to stop/limit this.

  2. When I compare these two dmv I see “0” for task_space_usage, but for session_space_usage I’m seeing what appears to be the total allocated, deallocated. Why are the different ? I read msdn site regarding both.

    select user_objects_alloc_page_count, user_objects_dealloc_page_count from
    where session_id = 94

    select user_objects_alloc_page_count, user_objects_dealloc_page_count from
    where session_id = 94

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>