SQL Server 2005 TempDB Distilled

A lot of us have a general idea of when and what uses TempDB but most of us only “google” when your TempDB is full and we have no other recourse. This blog takes very little time to read to get you up to speed and do what you need to do to understand the problem you have at hand.

The TempDB database as you know gets cleared upon startup with the recreation based on the Model database.  With the limitation of only being to allocate exactly one filegroup and one log filegroup for TempDB, your options are limited but you still need to understand how it is used. The filegroup can in turn have multiple files spread across different folders (the recommended number of files being equal to your CPU count). The following picture shows how the TempDB is categorized into three areas and what is contained in each

Contents

Gene rates Log?

In sys. all_ objects?

Temp DB

Internal Objects

Sort results

No

No

Intermediate Hash-join/aggregate results

LOB data – XML, VARCHAR(MAX), VARBINARY(MAX), text, image, ntext

Partial cursor data

Service Broker messages in transit

Version Store

Online Index Rebuild

Used for online index rebuilds

No

No

Common

Versions generated by transactions like snapshot isolation, MARS, online index rebuild

User Objects

User Tables & IDX’s

#, ##, @ tables and online clustered index build with SORT_IN_TEMPDB option

Yes

Yes

Sys Catalog Tables & IDX’s

Only visible via system catalog views and not modifiable via T-SQL but only DDL’s.

No

To look at space usage in tempdb

Use tempdb

sp_spaceused

To look at space usage in any database

Use <MyDatabase>

sp_spaceused

Space usage by three categories of TempDB information from above – User, Version Store and Internal:

select * from Sys.dm_db_file_space_usage

 

An extent is made of 8 pages, so multiply each value by 8

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)*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

TempDB Space Usage by Sessions that completed Execution – Ordered DESC by hog

This is for TempDB usage so, the explicit join to sys.databases is to state the obvious but do note that this is the TempDB usage by sessions that are ACTIVE for actions that have already completed (as in historic, by-gone, past) andnot what is currently happening at the moment (ie., not the current picture ;-)). This will still give you a good indication of sessions that have consumed a lot of temp space.

SELECT

      d.name AS database_name ,

      dmdbssu.session_id,

      dmdbssu.user_objects_alloc_page_count * 8 AS user_obj_alloc_kb,

      dmdbssu.user_Objects_dealloc_page_count * 8 AS user_obj_dealloc_kb,

      dmdbssu.internal_objects_alloc_page_count * 8 AS internal_obj_alloc_kb,

      dmdbssu.internal_objects_dealloc_page_count * 8 AS internal_obj_dealloc_kb,

      (dmdbssu.user_objects_alloc_page_count * 8) + (dmdbssu.internal_objects_alloc_page_count * 8) AS overall_alloc_kb,

      (dmdbssu.internal_objects_alloc_page_count * 8) + (dmdbssu.internal_objects_dealloc_page_count * 8) AS overall_dealloc_kb

FROM

      sys.dm_db_session_space_usage dmdbssu,

      sys.databases d

WHERE

      dmdbssu.database_id = d.database_id

ORDER BY

      (dmdbssu.user_objects_alloc_page_count + dmdbssu.internal_objects_alloc_page_count) DESC

Real-time TempDB usage picture

The whole reason you probably started reading this is to find out about what is happening  to your TempDB now!  Well, I hope I got there without wasting too much of your time.  This is the space consumed by currently executing sessions ordered again by the greediest one first. Have fun killing them!!

SELECT

      session_id,

      request_id,

      exec_context_id,

      database_id,

      user_objects_alloc_page_count * 8 AS user_obj_alloc_kb,

      user_objects_dealloc_page_count * 8 AS user_obj_dealloc_kb,

      internal_objects_alloc_page_count * 8 AS internal_obj_alloc_kb,

      internal_objects_dealloc_page_count * 8 AS internal_obj_dealloc_kb

FROM sys.dm_db_task_space_usage

ORDER BY user_objects_alloc_page_count+internal_objects_alloc_page_count DESC

Find the offending SQL

Using the dm_db_task_space_usage, you can get to the offending SQL so that you can look at the explain plan and tune it so that it does not use so much TempDB space. Most likely, it is lack of statistics or too much sorting or something else but you know what is causing the problem from knowing the category of TempDB usage from above!!

(Copied from reference noted at the bottom)

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,

  sys.dm_exec_requests AS t2

WHERE t1.session_id = t2.session_id

  AND (t1.request_id = t2.request_id)

ORDER BY t1.task_alloc DESC

TempDB Usage by Active Transactions

We never talked about the version store category of TempDB. In short, it keeps the versions of data as data changes AKA transactions. Eg: Updating a column’s value to something else preserves the old and new value (hence the name version). More updates result in more versions. Inserts and Updates result in new “versions”. You get the idea. This query lists transactions that have versions in the version store ordered by the longest running transactions first.

SELECT *

FROM sys.dm_tran_active_snapshot_database_transactions

ORDER BY elapsed_time_seconds DESC

Use the sys.dm_tran_active_transactions DMV in conjunction with the above to find the long running transactions.

There is a lot more you can do to be pro-active or act upon. For all that you can do, read the reference link below which talks about performance counters that you can track.

 

Shrink TempDB Without Restarting:

Shrinking tempdb without restarting SQL Server

Reference:

If you found this information useful, you will definitely find the information from this source very useful because my blog was based on this very article.

http://technet.microsoft.com/en-us/library/cc966545.aspx

Advertisements

One thought on “SQL Server 2005 TempDB Distilled

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s