DMV to track the temp file usage for SQLServer

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

sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_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
s.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],
s.row_count AS [ROW COUNT]
FROM sys.dm_db_session_space_usage su INNER join sys.dm_exec_sessions s ON su.session_id = s.session_id

Below are the situation when SQL Server will use the temp file.

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

 

DMV to track the temp file usage for SQLServer,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。