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 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,
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:

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
    sys.dm_db_task_space_usage
    where session_id = 94

    select user_objects_alloc_page_count, user_objects_dealloc_page_count from
    sys.dm_db_session_space_usage
    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>

css.php