[MSSQL] TempDB 를 사용하는 쿼리 찾기

TempDB 를 사용하는 경우

---

1. 임시 테이블, 테이블 변수와 같은 임시 사용자 객체

2. 커서

3. 스풀이나 정렬을 위한 작업 테이블

4. 스냅샷에서 isolation 을 위한 행 버전 관리

5. 온라인 인덱스 재 구축 작업

6. MARS ( Multiple Active ResultSets, 다수의 활성 결과 집합 )

7. 트리거 등

TempDB 와 연관된 DMV 뷰

---

dm_db_file_space_usage : tempdb 의 각 파일에 대한 공간 사용량 정보를 반환합니다.

dm_db_session_space_usage : 각 세션에 의해 할당 및 할당 해제 된 페이지 수를 반환합니다.

dm_db_task_space_usage : 작업 별 페이지 할당 및 할당 해제를 반환합니다.

이러한 뷰를 dm_exec_session 이나 dm_exec_requests 와 같은 다른 뷰와 결합하여 실제 트랜잭션에서 tempdb 를 사용하고 있는지를 확인할 수 있습니다.

TempDB 를 사용하는 쿼리를 찾는 쿼리

---

이 쿼리는 현재 TempDB 를 사용하고 있는 쿼리만 출력합니다.

SELECT
  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.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.open_transaction_count,
  dmv_er.reads,
  dmv_er.writes,
  dmv_er.logical_reads
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