아티클
09/27/2010

쿼리 작업 수집기 유형

쿼리 작업 수집기 유형은 미리 정의된 시스템 데이터 컬렉션 집합 중 하나인 쿼리 통계 컬렉션 집합에 사용되는 사용자 지정 수집기 유형입니다.

이 수집기 유형은 미리 정의된 조건에 부합하는 쿼리에 대한 쿼리 계획 및 쿼리 텍스트와 함께 쿼리 통계와 쿼리 작업 정보를 수집하는 데 사용됩니다. 이 수집기 유형을 사용하여 고유한 컬렉션 집합을 만들 경우 수집되는 정보는 쿼리 통계 컬렉션 집합과 동일합니다. 따라서 미리 정의된 쿼리 통계 컬렉션 집합을 사용하는 것이 좋습니다.

아키텍처 및 처리

쿼리 작업 수집기 유형은 다음과 같은 요소로 구성됩니다.

입력 매개 변수 스키마(내부)
데이터 수집을 위한 SSIS 패키지(QueryActivityCollect.dtsx)
데이터 업로드를 위한 SSIS 패키지(QueryActivityUpload.dtsx)

또한 이 수집기 유형은 사용자 지정 태스크 또는 변환을 사용하여 관리 데이터 웨어하우스에 저장하기 위한 데이터를 분석 및 선택합니다.

쿼리 작업 수집기 유형은 다음과 같은 작업을 수행합니다.

dm_exec_requests, dm_exec_sessions의 샘플과 기타 선택된 관련 동적 관리 뷰를 수집합니다. 이는 하나의 조인된 쿼리로 구현되며, 데이터 컬렉션은 컬렉션 항목에 대해 지정된 빈도로 수행됩니다.
컬렉션 집합 업로드 빈도와 동일한 빈도로 dm_exec_query_stats 동적 관리 뷰의 스냅숏을 수집합니다. 쿼리 통계 컬렉션 집합의 기본 업로드 빈도는 15분입니다.

컬렉션 단계

다음 표에서는 컬렉션 단계 중에 사용되는 쿼리를 보여 줍니다. 이 쿼리는 QueryActivityCollect.dtsx SSIS 패키지에 정의되어 있습니다.

컬렉션 빈도

10초

쿼리

SET NOCOUNT ON
-- Get the collection time as UTC time
DECLARE @collection_time datetime
SET @collection_time = GETDATE()
SELECT
CONVERT(int, ROW_NUMBER() OVER (ORDER BY sess.session_id, ISNULL (req.request_id, -1), ISNULL (tasks.exec_context_id, -1)) ) AS row_id,
-- IDs and Blocking IDs
sess.session_id, 
ISNULL (req.request_id, -1) AS request_id, 
ISNULL (tasks.exec_context_id, -1) AS exec_context_id, 
ISNULL (req.blocking_session_id, 0) AS blocking_session_id,
CONVERT (bit, CASE 
                WHEN EXISTS (SELECT TOP 1 session_id FROM sys.dm_exec_requests bl WHERE bl.blocking_session_id = req.session_id) THEN 1
                ELSE 0
              END) AS is_blocking,
ISNULL (waits.blocking_exec_context_id, 0) AS blocking_exec_context_id, 
tasks.scheduler_id, 
DB_NAME(req.database_id) as database_name, 
req.[user_id], 
-- State information
LEFT (tasks.task_state, 10) AS task_state, 
LEFT (req.status, 15) AS request_status, 
LEFT (sess.status, 15) AS session_status,
req.executing_managed_code, 
-- Session information
sess.login_time, 
sess.is_user_process, 
LEFT (ISNULL (sess.[host_name], ''), 20) AS [host_name], 
LEFT (ISNULL (sess.[program_name], ''), 50) AS [program_name], 
LEFT (ISNULL (sess.login_name, ''), 30) AS login_name, 
-- Waits information
LEFT (ISNULL (req.wait_type, ''), 45) AS wait_type, 
LEFT (ISNULL (req.last_wait_type, ''), 45) AS last_wait_type, 
ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, 
LEFT (ISNULL (req.wait_resource, ''), 50) AS wait_resource, 
LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description,
-- Transaction information
req.transaction_id, 
ISNULL(req.open_transaction_count, 0) AS open_transaction_count,
COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) AS transaction_isolation_level,
-- Request stats
req.cpu_time AS request_cpu_time, 
req.logical_reads AS request_logical_reads, 
req.reads AS request_reads, 
req.writes AS request_writes, 
req.total_elapsed_time AS request_total_elapsed_time, 
req.start_time AS request_start_time, 
-- Session stats
sess.memory_usage, 
sess.cpu_time AS session_cpu_time, 
sess.reads AS session_reads, 
sess.writes AS session_writes, 
sess.logical_reads AS session_logical_reads, 
sess.total_scheduled_time AS session_total_scheduled_time, 
sess.total_elapsed_time AS session_total_elapsed_time, 
sess.last_request_start_time, 
sess.last_request_end_time, 
req.open_resultset_count AS open_resultsets, 
sess.row_count AS session_row_count, 
sess.prev_error, 
tasks.pending_io_count, 
-- Text/Plan handles
ISNULL (req.command, 'AWAITING COMMAND') AS command,  
req.plan_handle, 
req.sql_handle, 
req.statement_start_offset, 
req.statement_end_offset,
@collection_time AS collection_time
FROM sys.dm_exec_sessions sess 
LEFT OUTER MERGE JOIN sys.dm_exec_requests req  ON sess.session_id = req.session_id
LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id AND tasks.task_address = req.task_address
LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = sess.session_id AND waits.waiting_task_address = req.task_address
WHERE 
    sess.session_id <> @@SPID
    AND
    (
        (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping')))-- active request
            OR 
        (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0))            -- not active, but head blocker
    )
OPTION (FORCE ORDER)

업로드 단계

업로드 단계 동안 수집된 데이터는 관리 데이터 웨어하우스에 저장될 데이터를 결정하기 위해 분석됩니다. 이 분석은 저장되어야 하는 쿼리 통계, 쿼리 계획 및 쿼리 텍스트 집합을 결정합니다.

핵심 요소는 데이터 웨어하우스에 저장할 쿼리 및 쿼리 계획을 선택하는 알고리즘입니다. 이 알고리즘은 다음과 같이 작동합니다.

sys.dm_exec_query_stats의 스냅숏을 수집합니다. 이 스냅숏은 컬렉션 집합 업로드 빈도와 동일한 빈도로 수집됩니다. 기본값은 15분입니다.
새 스냅숏과 비교하기 위해 최신 스냅숏(15분 이전)을 검색합니다. 최신 스냅숏은 로컬로 캐시되고 관리 데이터 웨어하우스에서 검색할 필요는 없습니다.
다음 메트릭을 사용하는 각 스냅숏에서 상위 3개의 쿼리를 선택합니다.
- 경과 시간
- 작업자 시간
- 논리적 읽기 수
- 논리적 쓰기 수
- 물리적 읽기 수
- 실행 횟수
이 프로세스에서는 6 x 3개의 sql_handles 및 plan_handles를 제공합니다.
고유한 sql_handles 및 plan_handles를 식별합니다.
이 결과와 데이터 웨어하우스에 저장된 sql_handles 및 plan_handles의 교집합을 구합니다.

새 sql_handles 및 plan_handles의 경우 서버에서 계획과 텍스트를 가져옵니다. 계획 또는 텍스트를 찾을 수 없는 경우(로컬 캐시에서 이미 제거되었을 수 있음) 해당 핸들을 관리 데이터 웨어하우스에 저장합니다.
수집한 각 sql_handle 텍스트의 경우 텍스트를 정규화하고(예: 매개 변수 및 리터럴 제거) 정규화된 텍스트의 고유한 해시 값을 계산합니다. 정규화된 텍스트, 해시 값 및 원본 sql_handle에 대한 매핑을 관리 데이터 웨어하우스에 저장합니다.

다음 표에서는 스냅숏을 가져온 후 데이터를 분석하여 관리 데이터 웨어하우스에 업로드하는데 사용되는 쿼리를 보여 줍니다. 이 쿼리는 QueryActivityUpload.dtsx SSIS 패키지에 정의되어 있습니다.

쿼리

SET NOCOUNT ON
DECLARE @p1 datetime
SET @p1 = GETDATE()
SELECT 
    [sql_handle],
    statement_start_offset,
    statement_end_offset,
    -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats.  
    -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats 
    -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL. 
    MAX (plan_generation_num) AS plan_generation_num,
    plan_handle,
    MIN (creation_time) AS creation_time, 
    MAX (last_execution_time) AS last_execution_time,
    SUM (execution_count) AS execution_count,
    SUM (total_worker_time) AS total_worker_time,
    MIN (min_worker_time) AS min_worker_time,           -- NULLable
    MAX (max_worker_time) AS max_worker_time,
    SUM (total_physical_reads) AS total_physical_reads,
    MIN (min_physical_reads) AS min_physical_reads,     -- NULLable
    MAX (max_physical_reads) AS max_physical_reads,
    SUM (total_logical_writes) AS total_logical_writes,
    MIN (min_logical_writes) AS min_logical_writes,     -- NULLable
    MAX (max_logical_writes) AS max_logical_writes,
    SUM (total_logical_reads) AS total_logical_reads,
    MIN (min_logical_reads) AS min_logical_reads,       -- NULLable
    MAX (max_logical_reads) AS max_logical_reads,
    SUM (total_clr_time) AS total_clr_time,
    MIN (min_clr_time) AS min_clr_time,                 -- NULLable
    MAX (max_clr_time) AS max_clr_time,
    SUM (total_elapsed_time) AS total_elapsed_time,
    MIN (min_elapsed_time) AS min_elapsed_time,         -- NULLable
    MAX (max_elapsed_time) AS max_elapsed_time,
    @p1 AS collection_time
FROM
(
    SELECT  
        [sql_handle],
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        plan_handle,
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time,
        min_worker_time,
        max_worker_time,
        total_physical_reads,
        min_physical_reads,
        max_physical_reads,
        total_logical_writes,
        min_logical_writes,
        max_logical_writes,
        total_logical_reads,
        min_logical_reads,
        max_logical_reads,
        total_clr_time,
        min_clr_time,
        max_clr_time,
        total_elapsed_time,
        min_elapsed_time,
        max_elapsed_time 
    FROM sys.dm_exec_query_stats AS q
    UNION ALL 
    SELECT 
        r.[sql_handle],
        r.statement_start_offset,
        r.statement_end_offset,
        ISNULL (qs.plan_generation_num, 0) AS plan_generation_num,
        r.plan_handle,
        ISNULL (qs.creation_time, r.start_time) AS creation_time,
        r.start_time AS last_execution_time,
        1 AS execution_count,
        r.cpu_time AS total_worker_time,
        qs.min_worker_time,     -- min should not be influenced by in-progress queries
        r.cpu_time AS max_worker_time,
        r.reads AS total_physical_reads,
        qs.min_physical_reads,  -- min should not be influenced by in-progress queries
        r.reads AS max_physical_reads,
        r.writes AS total_logical_writes,
        qs.min_logical_writes,  -- min should not be influenced by in-progress queries
        r.writes AS max_logical_writes,
        r.logical_reads AS total_logical_reads,
        qs.min_logical_reads,   -- min should not be influenced by in-progress queries
        r.logical_reads AS max_logical_reads,
        qs.total_clr_time,      -- CLR time is not available in dm_exec_requests
        qs.min_clr_time,        -- CLR time is not available in dm_exec_requests
        qs.max_clr_time,        -- CLR time is not available in dm_exec_requests
        r.total_elapsed_time AS total_elapsed_time,
        qs.min_elapsed_time,    -- min should not be influenced by in-progress queries
        r.total_elapsed_time AS max_elapsed_time
    FROM sys.dm_exec_requests AS r 
    LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset 
        AND r.statement_end_offset = qs.statement_end_offset 
    WHERE r.sql_handle IS NOT NULL 
) AS query_stats 
OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sql
GROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset 
ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset

쿼리 출력

snapshots.query_stats, snapshots.notable_query_text, snapshots.notable_query_plan

변경 내역

업데이트된 내용
"컬렉션 단계" 및 "업로드 단계" 섹션이 추가되었습니다.
업로드 단계 중에 관리 데이터 웨어하우스에 저장되는 데이터를 결정하는 기준이 QueryActivityUpload.dtsx SSIS 패키지에 정의된 쿼리로 대체되었습니다.

참고 항목

개념

시스템 데이터 컬렉션 집합

관리 데이터 웨어하우스

데이터 컬렉션 용어

다음을 통해 공유