• 아티클

시스템 데이터 컬렉션 집합

SQL Server 2008 설치 시 데이터 수집기가 세 가지 시스템 데이터 컬렉션 집합을 설치합니다. 이러한 컬렉션 집합을 사용자의 모니터링 요구 사항에 맞게 구성할 수 있지만 삭제할 수는 없습니다. 시스템 데이터 컬렉션 집합은 다음으로 구성됩니다.

  • 디스크 사용. 시스템에 설치된 모든 데이터베이스의 디스크 및 로그 사용에 대한 데이터를 수집합니다.

  • 서버 작업. 리소스 사용 통계 및 성능 데이터를 서버 및 SQL Server에서 수집합니다.

  • 쿼리 통계. 쿼리 통계, 개별 쿼리 텍스트, 쿼리 계획 및 특정 쿼리를 수집합니다.

디스크 사용 컬렉션 집합

디스크 사용 컬렉션 집합에서는 데이터베이스 및 로그 파일의 크기 증가를 추적하고 일일 평균 크기 증가(MB) 등의 파일 관련 통계를 제공합니다.

컬렉션 집합에는 두 개의 컬렉션 항목인 디스크 사용 – 데이터 파일 및 디스크 사용 – 로그 파일이 있으며, 둘 다 일반 T-SQL 쿼리 수집기 유형을 사용합니다.. 컬렉션 집합에서는 다음 데이터를 수집합니다.

  • sys.partitions 및 sys.allocation_units 뷰에 표시되는 데이터 파일 크기의 스냅숏

  • DBCC SQLPERF(LOGSPACE) 명령을 통해 표시되는 로그 파일 크기의 스냅숏

  • sys.dm_io_virtual_file_stats 함수를 통해 얻는 I/O 통계의 스냅숏

다음 표에서는 디스크 사용 컬렉션 집합 및 해당 컬렉션 항목에 대한 자세한 정보를 알려 줍니다.

컬렉션 집합 이름

디스크 사용

컬렉션 모드

캐시되지 않음

업로드 일정 빈도

6시간마다

데이터 보존 기간

730일

컬렉션 항목

디스크 사용 - 데이터 파일

디스크 사용 - 로그 파일

컬렉션 항목 이름

디스크 사용 - 데이터 파일

수집기 유형

일반 T-SQL 쿼리

쿼리 1

 
SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files
SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id < 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN sys.allocation_units a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 
SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''

쿼리 1 출력

disk_usage

컬렉션 항목 이름

디스크 사용 - 로그 파일

수집기 유형

일반 T-SQL 쿼리

쿼리 1

 
INSERT INTO @tran_log_space_usage 
EXEC(''DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'');
SELECT 
    database_name,
    log_size_mb,
    log_space_used,
    status    
FROM @tran_log_space_usage

쿼리 1 출력

log_usage

서버 작업 컬렉션 집합

서버 작업 컬렉션 집합에서는 SQL Server 작업, SQL Server 리소스 사용률 및 SQL Server 리소스 충돌의 개요를 제공합니다. 또한 전체 시스템 리소스 사용률의 캡슐화된 뷰를 제공하여 이를 통해 성능 문제가 SQL Server 범위 외부의 작업과 관련이 있는지 여부를 확인할 수 있습니다.

이 컬렉션 집합은 다음과 같은 동적 관리 뷰에서 데이터 샘플을 수집합니다.

  • sys.dm_os_wait_stats

  • sys.dm_os_latch_stats

  • sys.dm_os_schedulers

  • sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_waiting_tasks(조인된 쿼리 사용)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

또한 여러 시스템과 SQL Server 성능 카운터에서도 데이터 샘플을 수집합니다.

서버 작업 컬렉션 집합에서는 리소스 사용률 및 리소스 병목 상태를 기준으로 시스템을 전체적으로 보여 줍니다. 리소스 사용률은 CPU, 디스크 I/O, 메모리 및 네트워크의 4가지 일반적인 영역으로 추적됩니다. sys.dm_exec_sessions, sys.dm_exec_requests 및 sys.dm_os_waiting_tasks를 샘플링하면 시스템 작업과 리소스 병목 상태 및 차단 문제를 연계할 수 있습니다.

이 컬렉션 집합 자체적으로 실행하면 리소스 병목 상태와 차단된 세션을 연결할 수 있고 세션 수준에서 차단 체인을 보여 줄 수 있습니다. 쿼리 텍스트가 수집되지 않더라도 쿼리 통계 컬렉션 집합에서 수집한 sql_handle 및 plan_handle 정보를 사용하여 세션 수준 아래로 드릴다운할 수 있습니다.

다음 표에서는 서버 작업 컬렉션 집합 및 해당 컬렉션 항목에 대한 자세한 정보를 알려 줍니다.

컬렉션 집합 이름

서버 작업

컬렉션 모드

캐시됨

업로드 일정 빈도

15분마다

데이터 보존 기간

14일

컬렉션 항목

서버 작업 - DMV 스냅숏

서버 작업 - 성능 카운터

컬렉션 항목 이름

서버 작업 - DMV 스냅숏

수집기 유형

일반 T-SQL 쿼리

컬렉션 빈도

60초

쿼리 1

 
SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms
FROM
 (SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    waiting_tasks_count, 
    wait_time_ms,  
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
UNION ALL 
    SELECT 
        LEFT (wait_type, 45) AS wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    WHERE wait_duration_ms > 60000
) AS merged_wait_stats
GROUP BY wait_type

쿼리 1 출력

snapshots.os_wait_stats

쿼리 2

 
SELECT 
  LEFT(latch_class,45) as latch_class,
  waiting_requests_count,
  wait_time_ms
FROM sys.dm_os_latch_stats 
WHERE waiting_requests_count > 0 OR wait_time_ms > 0

쿼리 2 출력

snapshots.os_latch_stats

쿼리 3

 
SELECT 
    pm.physical_memory_in_use_kb            AS sql_physical_memory_in_use_kb, 
    pm.large_page_allocations_kb            AS sql_large_page_allocations_kb, 
    pm.locked_page_allocations_kb           AS sql_locked_page_allocations_kb, 
    pm.total_virtual_address_space_kb       AS sql_total_virtual_address_space_kb, 
    pm.virtual_address_space_reserved_kb    AS sql_virtual_address_space_reserved_kb, 
    pm.virtual_address_space_committed_kb   AS sql_virtual_address_space_committed_kb, 
    pm.virtual_address_space_available_kb   AS sql_virtual_address_space_available_kb, 
    pm.page_fault_count                     AS sql_page_fault_count, 
    pm.memory_utilization_percentage        AS sql_memory_utilization_percentage, 
    pm.available_commit_limit_kb            AS sql_available_commit_limit_kb, 
    pm.process_physical_memory_low          AS sql_process_physical_memory_low, 
    pm.process_virtual_memory_low           AS sql_process_virtual_memory_low, 
    
    sm.total_physical_memory_kb             AS system_total_physical_memory_kb, 
    sm.available_physical_memory_kb         AS system_available_physical_memory_kb, 
    sm.total_page_file_kb                   AS system_total_page_file_kb, 
    sm.available_page_file_kb               AS system_available_page_file_kb, 
    sm.system_cache_kb                      AS system_cache_kb, 
    sm.kernel_paged_pool_kb                 AS system_kernel_paged_pool_kb, 
    sm.kernel_nonpaged_pool_kb              AS system_kernel_nonpaged_pool_kb, 
    sm.system_high_memory_signal_state      AS system_high_memory_signal_state, 
    sm.system_low_memory_signal_state       AS system_low_memory_signal_state, 
    
    si.bpool_commit_target                  AS bpool_commit_target, 
    si.bpool_committed                      AS bpool_committed, 
    si.bpool_visible                        AS bpool_visible
FROM sys.dm_os_process_memory AS pm
CROSS JOIN sys.dm_os_sys_memory AS sm   -- single-row DMV
CROSS JOIN sys.dm_os_sys_info AS si;    -- single-row DMV

쿼리 3 출력

snapshots.sql_process_and_system_memory

쿼리 4

 
SELECT 
    memory_node_id, 
    virtual_address_space_reserved_kb, 
    virtual_address_space_committed_kb, 
    locked_page_allocations_kb, 
    single_pages_kb, 
    multi_pages_kb, 
    shared_memory_reserved_kb, 
    shared_memory_committed_kb
FROM sys.dm_os_memory_nodes

쿼리 4 출력

snapshots.os_memory_nodes

쿼리 5

 
SELECT 
    type,
    memory_node_id as memory_node_id,
    SUM(single_pages_kb) as single_pages_kb,
    SUM(multi_pages_kb) as multi_pages_kb,
    SUM(virtual_memory_reserved_kb) as virtual_memory_reserved_kb,
    SUM(virtual_memory_committed_kb) as virtual_memory_committed_kb,
    SUM(awe_allocated_kb) as awe_allocated_kb,
    SUM(shared_memory_reserved_kb) as shared_memory_reserved_kb,
    SUM(shared_memory_committed_kb) as shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
GROUP BY type, memory_node_id

쿼리 5 출력

snapshots.os_memory_clerks

쿼리 6

 
SELECT 
    [parent_node_id],
    [scheduler_id],
    [cpu_id],
    [status],
    [is_idle],
    [preemptive_switches_count],
    [context_switches_count],
    [yield_count],
    [current_tasks_count],
    [runnable_tasks_count],
    [work_queue_count],
    [pending_disk_io_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 128

쿼리 6 출력

snapshots.os_schedulers

쿼리 7

 
SELECT 
    DB_NAME (f.database_id) AS database_name, f.database_id, f.name AS logical_file_name, f.[file_id], f.type_desc, 
    CAST (CASE 
        -- Handle UNC paths (e.g. ''\\fileserver\readonlydbs\dept_dw.ndf'' --> ''\\fileserver\readonlydbs'')
        WHEN LEFT (LTRIM (f.physical_name), 2) = ''\\'' 
            THEN LEFT (LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 3) + 1) - 1)
        -- Handle local paths (e.g. ''C:\Program Files\...\master.mdf'' --> ''C:'') 
        WHEN CHARINDEX (''\'', LTRIM(f.physical_name), 3) > 0 
            THEN UPPER (LEFT (LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS nvarchar(255)) AS logical_disk, 
    fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, 
    fs.io_stall_write_ms, fs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]

쿼리 7 출력

snapshots.io_virtual_file_stats

컬렉션 항목 이름

서버 작업 - 성능 카운터

수집기 유형

성능 카운터

컬렉션 빈도

60초

사용된 성능 카운터

"Memory" Counters="% Committed Bytes In Use"

"Memory" Counters="Available Bytes"

"Memory" Counters="Cache Bytes"

"Memory" Counters="Cache Faults/sec"

"Memory" Counters="Committed Bytes"

"Memory" Counters="Free & Zero Page List Bytes"

"Memory" Counters="Modified Page List Bytes"

"Memory" Counters="Pages/sec"

"Memory" Counters="Page Reads/sec"

"Memory" Counters="Page Write/sec"

"Memory" Counters="Page Faults/sec"

"Memory" Counters="Pool Nonpaged Bytes"

"Memory" Counters="Pool Paged Bytes"

"Memory" Counters="Standby Cache Core Bytes"

"Memory" Counters="Standby Cache Normal Priority Bytes"

"Memory" Counters="Standby Cache Reserve Bytes"

"Memory" Counters="Pool Paged Bytes"

"Memory" Counters="Write Copies/sec"

"Process" Counters="*" Instances="_Total"

"Process" Counters="*" Instances="$(TARGETPROCESS)"

"Process" Counters="Thread Count" Instances="*"

"Process" Counters="% Processor Time" Instances="*"

"Process" Counters="IO Read Bytes/sec" Instances="*"

"Process" Counters="IO Write Bytes/sec" Instances="*"

"Process" Counters="Private Bytes" Instances="*"

"Process" Counters="Working Set" Instances="*"

"Processor" Counters="% Processor Time" Instances="*"

"Processor" Counters="% User Time" Instances="*"

"Processor" Counters="% Privileged Time" Instances="*"

"Server Work Queues" Counters="Queue Length" Instances="*"

"LogicalDisk" Counters="% Disk Time" Instances="*"

"LogicalDisk" Counters="Avg. Disk Queue Length" Instances="*"

"LogicalDisk" Counters="Avg. Disk Read Queue Length" Instances="*"

"LogicalDisk" Counters="Avg. Disk Write Queue Length" Instances="*"

"LogicalDisk" Counters="Avg. Disk sec/Read" Instances="*"

"LogicalDisk" Counters="Avg. Disk sec/Write" Instances="*"

"LogicalDisk" Counters="Avg. Disk sec/Transfer" Instances="*"

"LogicalDisk" Counters="Disk Reads/sec" Instances="*"

"LogicalDisk" Counters="Disk Bytes/sec" Instances="*"

"LogicalDisk" Counters="Disk Writes/sec" Instances="*"

"LogicalDisk" Counters="Split IO/sec" Instances="*"

"System" Counters="Processor Queue Length"

"System" Counters="File Read Operations/sec"

"System" Counters="File Write Operations/sec"

"System" Counters="File Control Operations/sec"

"System" Counters="File Read Bytes/sec"

"System" Counters="File Write Bytes/sec"

"System" Counters="File Control Bytes/sec"

"Network Interface" Counters="Bytes Total/sec" Instances="*"

"Network Interface" Counters="Output Queue Length" Instances="*"

"SQLServer:Buffer Manager" Counters="Stolen pages"

"SQLServer:Buffer Manager" Counters="Page life expectancy"

"SQLServer:Memory Manager" Counters="Memory Grants Outstanding"

"SQLServer:Memory Manager" Counters="Memory Grants Pending"

"SQLServer:Databases" Counters="Transactions/sec" Instances="_Total"

"SQLServer:Databases" Counters="Transactions/sec" Instances="tempdb"

"SQLServer:Databases" Counters="Active Transactions" Instances="*"

"SQLServer:General Statistics" Counters="Logins/sec"

"SQLServer:General Statistics" Counters="Logouts/sec"

"SQLServer:General Statistics" Counters="User Connections"

"SQLServer:General Statistics" Counters="Logical Connections"

"SQLServer:General Statistics" Counters="Transactions"

"SQLServer:General Statistics" Counters="Processes blocked"

"SQLServer:General Statistics" Counters="Active Temp Tables"

"SQLServer:SQL Statistics" Counters="Batch Requests/sec"

"SQLServer:SQL Statistics" Counters="SQL Compilations/sec"

"SQLServer:SQL Statistics" Counters="SQL Re-Compilations/sec"

"SQLServer:SQL Statistics" Counters="SQL Attention rate"

"SQLServer:SQL Statistics" Counters="Auto-Param Attempts/sec"

"SQLServer:SQL Statistics" Counters="Failed Auto-Params/sec"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="_Total"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="Object Plans"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="SQL Plans"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="Temporary Tables & Table Variables"

"SQLServer:Transactions" Counters="Free Space in tempdb (KB)"

"SQLServer:Workload Group Stats" Counters="Active requests" Instances="*"

"SQLServer:Workload Group Stats" Counters="Blocked tasks" Instances="*"

"SQLServer:Workload Group Stats" Counters="CPU usage %" Instances="*"

쿼리 통계 컬렉션 집합

쿼리 통계 컬렉션 집합에서는 쿼리 통계와 개별 쿼리 텍스트, 쿼리 계획 및 특정 쿼리에 대한 데이터를 수집합니다. 시스템 수준 통계 및 작업과 연결된 경우 이 데이터를 사용하면 세션 수준 아래의 개별 쿼리로 드릴다운할 수 있습니다.

이 컬렉션 집합은 다음 원본에서 데이터를 수집합니다.

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats 및 기타 관련된 동적 관리 뷰

  • 선택한 일괄 처리 및 쿼리의 텍스트

  • 선택한 일괄 처리 및 쿼리의 계획

  • 선택한 일괄 처리의 정규화된 텍스트

쿼리 통계 컬렉션 집합에서는 쿼리 작업 수집기 유형을 사용합니다. 쿼리 작업 수집기 유형은 QueryActivityCollect.dtsx SSIS 패키지를 사용하여 데이터를 수집하고 QueryActivityUpload.dtsx SSIS 패키지를 사용하여 데이터를 업로드합니다. 쿼리 작업 수집 유형의 수집 및 업로드 단계와 사용되는 쿼리에 대한 자세한 내용은 쿼리 작업 수집기 유형을 참조하십시오.

다음 표에서는 쿼리 통계 컬렉션 집합 및 해당 컬렉션 항목에 대한 정보를 알려 줍니다.

컬렉션 집합 이름

쿼리 통계

컬렉션 모드

캐시됨

업로드 일정 빈도

15분마다

데이터 보존 기간

14일

컬렉션 항목

쿼리 통계 - 쿼리 작업

변경 내역

업데이트된 내용

컬렉션 집합 및 컬렉션 항목에 대한 자세한 정보를 보여 주는 모든 표가 올바른 정보와 코드로 업데이트되었습니다.

쿼리 통계 컬렉션 집합에 사용되는 쿼리에 대한 자세한 정보가 쿼리 작업 수집기 유형 항목으로 이동되었습니다.