tempdb 사용량에 문제를 일으키는 쿼리 찾기

누군가 tempdb 가 이상해요 라고 연락이 왔다. 아래 문서와 쿼리를 이용해 문제를 해결한다.

workingwithtempdb.doc

tempdb 를 core 수만큼 만드는 것은 tempdb 유지관리 비용으로 인해 allocation contention 해결 비용보다 더 클 수 도 있다는 설명도 들어있는 좋은 문서이다. workload 에 따라서 튜닝방법은 달라져야 한다. 
 

Optimizing_tempdb_Performance_chicago.pdf

CREATE database perf_warehouse
GO
USE perf_warehouse
GO

CREATE TABLE tempdb_space_usage (
  -- This represents the time when the particular row was
  -- inserted
  dt datetime DEFAULT CURRENT_TIMESTAMP,
  -- session id of the sessions that were active at the time
  session_id int DEFAULT null,
  -- this represents the source DMV of information. It can be
  -- track instance, session or task based allocation information.
  scope varchar(10), 
  -- instance level unallocated extent pages in tempdb
  Instance_unallocated_extent_pages bigint,
  -- tempdb pages allocated to verstion store
  version_store_pages bigint,
  -- tempdb pages allocated to user objects in the instance
  Instance_userobj_alloc_pages bigint,   
  -- tempdb pages allocated to internal objects in the instance
  Instance_internalobj_alloc_pages bigint,
  -- tempdb pages allocated in mixed extents in the instance
  Instance_mixed_extent_alloc_pages bigint,
  -- tempdb pages allocated to user obejcts within this sesssion or task.
  Sess_task_userobj_alloc_pages bigint,   
  -- tempdb user object pages deallocated within this sesssion
  -- or task.
  Sess_task_userobj_deallocated_pages bigint,
  -- tempdb pages allocated to internal objects within this sesssion
  -- or task
  Sess_task_internalobj_alloc_pages bigint,
  -- tempdb internal object pages deallocated within this sesssion or
  -- task
  Sess_task_internalobj_deallocated_pages bigint,   
  -- query text for the active query for the task 
  query_text nvarchar(max) 
)
go

-- Create a clustered index on time column when the data was collected
CREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)
go

 

 

아래 프로시저를 주기적으로 돌려 tempdb 문제를 일으키는 쿼리를 찾는다.

CREATE PROC sp_sampleTempDbSpaceUsage AS
  -- Instance level tempdb File space usage for all files within
  -- tempdb
  INSERT tempdb_space_usage (
    scope,
    Instance_unallocated_extent_pages,
    version_store_pages,
    Instance_userobj_alloc_pages,
    Instance_internalobj_alloc_pages,
    Instance_mixed_extent_alloc_pages)
  SELECT
    'instance',
    SUM(unallocated_extent_page_count),
    SUM(version_store_reserved_page_count),
    SUM(user_object_reserved_page_count),
    SUM(internal_object_reserved_page_count),
    SUM(mixed_extent_page_count)
  FROM sys.dm_db_file_space_usage
   
    -- 2. tempdb space usage per session
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages)
  SELECT
    'session',
    session_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
  FROM sys.dm_db_session_space_usage
    WHERE session_id > 50

    -- 3. tempdb space usage per active task
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages,
    query_text)
  SELECT
    'task',
    R1.session_id,
    R1.user_objects_alloc_page_count,
    R1.user_objects_dealloc_page_count,
    R1.internal_objects_alloc_page_count,
    R1.internal_objects_dealloc_page_count,
    R3.text
  FROM sys.dm_db_task_space_usage AS R1
    LEFT OUTER JOIN
    sys.dm_exec_requests AS R2
    ON R1.session_id = R2.session_id
    OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
  WHERE R1.session_id > 50