[MSSQL] Tempdb 사용량 확인

*Step1. temp메모리 사용량 확인

-- tempdb의 전체 공간 확인

DECLARE @max_temp_size FLOAT

SELECT

     @max_temp_size = SUM(size)*1.0/128

FROM tempdb.sys.database_files with(nolock)

SELECT  @max_temp_size AS [Max size in MB]

-- tempdb에서 사용 가능한 전체 빈 공간 및 사용된 메모리 비율 확인

SELECT

     (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],

     1 - (SUM(unallocated_extent_page_count)*1.0/128)/ @max_temp_size AS[Used Percent]

FROM tempdb.sys.dm_db_file_space_usage with(nolock)

*Step2. Session 별 메모리 사용량 확인

SELECT

     es.session_id AS [SESSION ID],

     DB_NAME(es.database_id) AS [DATABASE Name],

     es.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)],

     datediff(mi, es.login_time, getdate()) AS [Session Running Time (in minite)],

     (memory_usage * 8)      AS[Memory USAGE (in KB)],

     ((user_objects_alloc_page_count + task_user_objects_alloc_page_count) * 1.0/128 ) AS [SPACE Allocated FOR USER Objects (in MB)],

     row_count AS[ROW COUNT]

FROM sys.dm_db_session_space_usageas ssu with(nolock)

join (

     SELECT

          session_id,

          SUM(user_objects_alloc_page_count) AStask_user_objects_alloc_page_count,

          SUM(user_objects_dealloc_page_count) AStask_user_objects_dealloc_page_count

     FROMsys.dm_db_task_space_usage with(nolock)

     GROUPBY session_id

) AS fsu

     ONssu.session_id =fsu.session_id

join sys.dm_exec_sessions  as es with(nolock)

     ONssu.session_id =es.session_id

where ssu.session_id > 50

order by  ((user_objects_alloc_page_count + task_user_objects_alloc_page_count) )  desc

*Step3. 특정 세션 쿼리 확인

dbcc inputbuffer(session_id )​ 

* 관련 정보

https://technet.microsoft.com/ko-kr/library/ms176029.aspx

* 관련 DBV *

sys.database_files

sys.dm_db_file_space_usage

sys.dm_db_session_space_usage

sys.dm_exec_sessions

sys.sysprocesses

sys.dm_exec_sql_text