SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tracking Down tempdb usage

Determining what is causing your tempdb to grow can be difficult at times and troublesome during production issues.  Luckily SQL Server provides some views that produce some helpful information in regards to session usage and query allocations for tempdb.

The main views that detail tempdb usage are:

dm_db_file_space_usage – details space usage information for each file in each database.

dm_db_session_space_usage – details the number of pages allocated and de-allocated by each connected session for each database

dm_db_task_space_usage – details the page allocation of each task running by session for each database.

Using some joins and other informational views you can create a query to display usage for tempdb so you can track down the usage and fix what needs to be fixed.  Credit to Pinal Dave for the query:

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END – dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) DESC

 

 

The post Tracking Down tempdb usage appeared first on VitaminDBA.

vitamindba

VitaminDBA.com is a source of SQL Server News and Tips coming from a SQL Server sr. database analyst with 10 years of IT experience ranging from Windows Server, Network, and SQL Server administration. I upload articles on the blog on a fairly consistent basis with the intention of at least 1 new article a week.

Comments

Leave a comment on the original post [vitamindba.com, opens in a new window]

Loading comments...