아티클
04/01/2012

쿼리 작업 수집기 유형

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

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

아키텍처 및 처리

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

입력 매개 변수 스키마(내부)
데이터 수집을 위한 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

참고 항목

개념

시스템 데이터 컬렉션 집합

관리 데이터 웨어하우스

데이터 컬렉션 용어

쿼리 작업 수집기 유형

아키텍처 및 처리

컬렉션 단계

업로드 단계

참고 항목

개념

관련 자료

추가 리소스