DMV to track the temp file usage for SQL Server

Source: Internet
Author: User

There is three DMVs you can use for track tempdb usage:

Sys.dm_db_task_space_usage
Sys.dm_db_session_space_usage
Sys.dm_db_file_space_usage

The first and then 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 would 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 joins sys.dm_exec_sessions s on su.session_id = s.session_id

Below is the situation when SQL Server would 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 are a sign of AB Sent Indexes/out-of-date statistics)
    3. SQL Server decided to pre-evaluate the resultset of the table valued function and in this case it stores the data in tempdb
    4. Recreating indexes with option SORT_IN_TEMPDB = On

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.