Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The tempdb of an server (SQL Server 2008) increases to 500+G several times every month. Is it possilbe to find out which SQL statements cause the problems? (The problem is usually not caused by create table #temp...; insert into #temp... or select ... into #temp... but complex joins).

BTW, the Initial Size of some of the tempdb files are also automatically set up much bigger values every time. How to prevent it?

Sometime the cached plans prevent resizing/shrinking the files. How to find which one hold the tempdb?

share|improve this question
1  
Sorry it's almost 2 AM and I'm out of gas to answer this question completely, however these URLs may come in handy while you wait for other answers - mssqltips.com/sqlservertip/1432/… and google.com/search?q=which+queries+are+using+tempdb –  Aaron Bertrand Feb 25 '12 at 6:39
add comment

2 Answers

up vote 18 down vote accepted

There are three DMVs you can use to track tempdb usage:

The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.

The following example query will give you allocations per session:

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM 
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

If you want to track usage over a period of time, consider collecting data with sp_whoisactive, as demonstrated by Kendra Little.

share|improve this answer
1  
Thanks. Is the difference of [SPACE Allocated FOR USER Objects (in KB)] and [SPACE Deallocated FOR USER Objects (in KB)] the actual occupied space of the session? –  u23432534 Feb 26 '12 at 2:22
add comment

There can be various sources of a problem:

  • usage of table variables or temporary tables
  • sql server created intermediate resultsets as worktables in tempdb - usually for sorting purposes (usually is a sign of absent indexes/out-of-date statistics)
  • sql server decided to pre-evaluate the resultset of table valued function and in this case it stores the data in tempdb
  • recreating indexes with option SORT_IN_TEMPDB = ON
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.