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

  3. I gather from the video that the recommendation is to set up a job to run these queries regularly, and to capture the data to a table. How frequently should the queries be run? Thanks!

    • You should run those queries as frequently as you’re actually going to review them.

      • I think this is another way of asking my question:
        The opening frame of the video says:
        1. “Establish History” = run them today.
        2. “Record History On a Regular Basis” = ?
        3. “Review Baselines to Generate Performance Trends” = combination of 1 & 2

        How often do you recommend I run/review them?

        • If you think you’re going to need to run and review this information a lot, I recommend that you buy a monitoring tool. They don’t cost much in the grand scheme of things and they let you decide how finely you need to aggregate data.

          Otherwise, there is no good answer apart from “as frequently as you’re actually going to review them”. I don’t know your application’s requirements or activity patterns, so I can’t give any prescriptive advice.

  4. You had mentioned some scripts from Louis Davidson that you used to monitor “churn” in tempdb (and other files?). Would it be possible to add a link to those scripts?

Leave a Reply

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

css.php