Admin::Tempdb 의 작업, 모니터링

1.tempdb의 사용

- 지역/전역 임시 테이블과 인덱스, 임시 저장프로시저, 테이블 변수, 커서

- 해시 조인, 집계, 커서, order by , group by , union 쿼리 등과 같이 다양한 작업을 수행하면서 발생되는 중간 결과를 저장하기 위한 임시 테이블

- after트리거, instead of 트리거를 이용할 때 사용되는 개체들 저장

- 대형 xml 개체 저장

- sql server 버전 저장소

- DBCC checkdb 명령을 사용하는 동안에 중간 결과를 정렬 작업을 저장

- 서비스 브로커의 임시 개체를 저장

2. tempdb의 공간 사용

- 내부 개체

- 버전 저장소

- 사용자 개체

3. 최적화 방법

- tempdb의 사용량이 증가할 경우 다른 데이터베이스들의 성능 저하

- tempdb의 사용의 최소화 ( 1번 내용에 해당되는 것들)

- tempdb의 공간을 미리 할당함으로써 자동 증가 옵션 사용을 피함

- 필요하지 않은 경우는 tempdb를 축소하지 말자,  현재 작게 사용하고 있다고 항상 작은 사이즈를 사용하고 있는 것은 아니다. batch 작업이 시작될때는 지금 사용 사이즈 보다 훨씬 많이 사용할 수도 있다. 

- tempdb 데이터 베이스를 물리적 파일로 분리하자, (전형적으로 cpu 코어수 만큼 분리)

- 고속 I/O 하위 시스템에 tempdb를 생성하자  ( RAID 5로는 설정해서는 안된다.: 속도가 느리다.  적어도 RAID 1 또는 RAID10 으로 설정된 I/O 하위 시스템에 생성하는 것을 권장한다.)

- tempdb는 별도의 디스크 드라이브를 가지고 있는 별도의 LUN (논리 단위 번호)에 생성하는 것이 좋다. 

- SQL 2008의 TDE(투명한 데이터 암호화) 사용시 고려해야 한다. tempdb역시 암호화 되기 때문에 디스크 사이를 이동할 때마다 암호화 하고 복호화 하는 작업이 일어난다. 

- 통계 자동 작성, 자동 업데이트 옵션은 활성화 된 상태로 두자

- SQL 2008에서는 CEECKSUM 옵션을 확인하자, SQL 2005에서 업그레이드 되었을 경우에는 비활성화 되어 있다. 꼭 활성화 시키자  - tempdb의 무결성을 보장 할 수 있다. 

4. 모니터링

--1. tempdb SizeSELECT name , db.log_reuse_wait_desc , ls.cntr_value  AS size_kb , lu.cntr_value AS used_kb , CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)    AS used_percent , CASE WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5 THEN    CASE     /* tempdb special monitoring */     WHEN db.name = 'tempdb'       AND log_reuse_wait_desc NOT IN ('CHECKPOINT', 'NOTHING') THEN 'WARNING'      /* all other databases, monitor foor the 50% fill case */     WHEN db.name <> 'tempdb' THEN 'WARNING'     ELSE 'OK'     END   ELSE 'OK' END   AS log_status FROM sys.databases db JOIN sys.dm_os_performance_counters lu  ON db.name = lu.instance_name JOIN sys.dm_os_performance_counters ls  ON db.name = ls.instance_name WHERE lu.counter_name LIKE  'Log File(s) Used Size (KB)%' AND ls.counter_name LIKE 'Log File(s) Size (KB)%' -- 2. temdb 사용 session,taskSELECT top 5 * FROM sys.dm_db_session_space_usage  ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESCSELECT top 5 * FROM sys.dm_db_task_space_usageORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESCSELECT t1.session_id, t1.request_id, t1.task_alloc,  t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,   t2.statement_end_offset, t2.plan_handleFROM (Select session_id, request_id,    SUM(internal_objects_alloc_page_count) AS task_alloc,    SUM (internal_objects_dealloc_page_count) AS task_dealloc   FROM sys.dm_db_task_space_usage   GROUP BY session_id, request_id) AS t1,   sys.dm_exec_requests AS t2WHERE t1.session_id = t2.session_id  AND (t1.request_id = t2.request_id)ORDER BY t1.task_alloc DESC

--3.  종합

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 char(7),      -- 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 collectedCREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)goCREATE 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 > 50go

--5. 결과 쿼리 -- This query reports the maximum allocated space in tempdb over all the data points collectedSELECT  CONVERT (float, (MAX(version_store_pages +      Instance_userobj_alloc_pages +      Instance_internalobj_alloc_pages +      Instance_mixed_extent_alloc_pages)))/ 128.0    AS max_tempdb_allocation_MBFROM     tempdb_space_usage WHERE scope = ‘instance’

--This query reports the average allocated space in tempdb over all the data points collectedSELECT       CONVERT (float, (AVG(version_store_pages +      Instance_userobj_alloc_pages +      Instance_internalobj_alloc_pages +      Instance_mixed_extent_alloc_pages)))/ 128.0    AS avg_tempdb_allocation_MBFROM tempdb_space_usage WHERE scope = ‘instance’

-- 버전 정보 저장 할당양SELECT       MAX(version_store_pages) AS max_version_store_pages_allocated,  MAX(version_store_pages/128.0) AS max_version_store_allocated_space_MBFROM tempdb_space_usage WHERE scope = ‘instance’ 

SELECT  AVG(version_store_pages) AS max_version_store_pages_allocated,  AVG(version_store_pages)/ 128.0 AS max_version_store_allocated_space_MBFROM tempdb_space_usage WHERE scope = ‘instance’

-- top 5 내부 개첵 사용하는 requestSELECT top 5 MAX ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))  AS Max_Sess_task_allocated_pages_delta,     query_textFROM tempdb_space_usage WHERE scope = 'task' and session_id > 50GROUP BY query_textORDER BY Max_Sess_task_allocated_pages_delta  DESCSELECT top 5 AVG ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))  AS Avg_Sess_task_allocated_pages_delta, query_textFROM tempdb_space_usage WHERE scope = 'task' and session_id > 50GROUP BY query_textORDER BY Avg_Sess_task_allocated_pages_delta  DESC