MSSQLWIKI

Karthick P.K on SQL Server

Monitoring Tempdb usage

Posted by Karthick P.K on January 13, 2010

Monitoring Tempdb space usage and identifying the session and query which Consumes Tempdb

The total space used by Tempdb consists of 

1. User Objects 

2.  Internal Objects

3. Version Store

4. Free Space.

Use  Below Query to Track which objects (above) is consuming pace in TempDb. 
 

SELECT

SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB],SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in [MB],SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

go

Once you have identified the objects identify the query and session which is consuming tempdb using the query listed below

–Use below query to identify which Query and Session is consuming the space in TempDB

 
SELECT R1.session_id, R1.request_id, R1.Task_request_internal_objects_alloc_page_count, R1.Task_request_internal_objects_dealloc_page_count,

R1.Task_request_user_objects_alloc_page_count,R1.Task_request_user_objects_dealloc_page_count,R3.Session_request_internal_objects_alloc_page_count ,

R3.Session_request_internal_objects_dealloc_page_count,R3.Session_request_user_objects_alloc_page_count,R3.Session_request_user_objects_dealloc_page_count,

R2.sql_handle, RL2.text as SQLText, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle FROM (SELECT session_id, request_id, 

SUM(internal_objects_alloc_page_count) AS Task_request_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count)AS 

Task_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Task_request_user_objects_alloc_page_count,

SUM(user_objects_dealloc_page_count)AS Task_request_user_objects_dealloc_page_count FROM sys.dm_db_task_space_usage 

GROUP BY session_id, request_id) R1 INNER JOIN (SELECT session_id, SUM(internal_objects_alloc_page_count) AS Session_request_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count)AS Session_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Session_request_user_objects_alloc_page_count,

SUM(user_objects_dealloc_page_count)AS Session_request_user_objects_dealloc_page_count FROM sys.dm_db_Session_space_usage 

GROUP BY session_id) R3 on R1.session_id = R3.session_id 

left outer JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id

OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS RL2
Where 
Task_request_internal_objects_alloc_page_count >0 or  
Task_request_internal_objects_dealloc_page_count>0 or 
Task_request_user_objects_alloc_page_count >0 or 
Task_request_user_objects_dealloc_page_count >0 or 
Session_request_internal_objects_alloc_page_count >0 or 
Session_request_internal_objects_dealloc_page_count >0 or 
Session_request_user_objects_alloc_page_count >0 or 
Session_request_user_objects_dealloc_page_count >0 

Known issues related to TEMPDB Shrink

FIX: The used space in the tempdb database increases continuously when you run a query that creates internal objects in the tempdb database in SQL Server 2005
 

Thank you,

Karthick P.K | My Facebook Page |My Site| Blog space| Twitter

About these ads

4 Responses to “Monitoring Tempdb usage”

  1. Hildegard said

    Hello there! This is kind of off topic but I need some help from an established blog.
    Is it difficult to set up your own blog? I’m not very techincal but I can figure things out pretty fast. I’m thinking about setting up my own but I’m not sure where to start. Do you have any tips or suggestions? Appreciate it

  2. Hello There. I found your blog using msn. This is an extremely well written
    article. I’ll make sure to bookmark it and come back to read more of your useful info. Thanks for the post. I will certainly comeback.

  3. Today, I went to the beachfront with my children.

    I found a sea shell and gave it to my 4 year old daughter
    and said “You can hear the ocean if you put this to your ear.” She placed the shell to her ear
    and screamed. There was a hermit crab inside and it pinched her ear.
    She never wants to go back! LoL I know this is totally off topic but I had to tell
    someone!

  4. […] How to monitor the Session and query which Consumes Tempdb […]

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

 
Follow

Get every new post delivered to your Inbox.

Join 2,275 other followers

%d bloggers like this: