SQL Server 상태 모니터링

출처 : http://technet.microsoft.com/ko-kr/library/bb838723(v=office.12).aspx

업데이트: 2009-04-30

Microsoft SQL Server 2005에서는 데이터베이스를 모니터링하기 위한 몇 가지 도구를 제공합니다. 그 중 하나가 동적 관리 뷰입니다. DMV(동적 관리 뷰)와 DMF(동적 관리 함수)는 서버 인스턴스의 상태를 모니터링하고, 문제를 진단하고, 성능을 조정하는 데 사용할 수 있는 서버 상태 정보를 반환합니다.

일반적인 서버 동적 관리 개체로는 다음과 같은 것이 있습니다.

• dm_db_*: 데이터베이스 및 데이터베이스 개체

• dm_exec_*: 사용자 코드 및 관련 연결 실행

• dm_os_*: 메모리, 잠금 및 예약

• dm_tran_*: 트랜잭션 및 격리

• dm_io_*: 네트워크 및 디스크의 입/출력

이 단원에서는 SQL Server 상태를 모니터링하기 위해 이러한 동적 관리 뷰 및 함수에 대해 일반적으로 사용하는 몇 가지 쿼리를 보여 줍니다.

샘플 쿼리

다음 쿼리를 실행하면 모든 DMV 및 DMF 이름을 확인할 수 있습니다.

SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name

CPU 병목 현상 모니터링

CPU 병목 현상은 일반적으로 최적화되지 않은 쿼리 계획, 잘못된 구성, 잘못된 디자인 요소 또는 충분하지 않은 하드웨어 리소스 등으로 인해 발생합니다. 다음은 CPU 병목 현상을 일으키는 원인을 찾아내기 위해 일반적으로 사용되는 몇 가지 쿼리입니다.

다음 쿼리를 실행하면 현재 캐시된 배치나 프로시저 중 CPU 사용률이 가장 높은 항목이 무엇인지 쉽게 파악할 수 있습니다.

SELECT TOP 50 
      SUM(qs.total_worker_time) AS total_cpu_time, 
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements, 
      qs.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC

다음 쿼리는 SQL 텍스트를 사용하여 캐시된 계획별로 집계한 CPU 사용량을 보여 줍니다.

SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset, 
            --MAX(statement_end_offset) AS statement_end_offset
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2 

다음 쿼리는 평균 이상의 CPU 사용률을 보이는 상위 50개 SQL 문을 보여 줍니다.

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

다음은 과도한 컴파일/재컴파일을 찾기 위한 DMV 쿼리입니다.

select * from sys.dm_exec_query_optimizer_info
where 
      counter = 'optimizations'
      or counter = 'elapsed time'

다음 샘플 쿼리에서는 다시 컴파일된 상위 25개의 저장 프로시저를 표시합니다. plan_generation_num은 쿼리를 다시 컴파일한 횟수를 나타냅니다.

select top 25
      sql_text.text,
      sql_handle,
      plan_generation_num,
      execution_count,
      dbid,
      objectid 
from sys.dm_exec_query_stats a
      cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

쿼리 계획이 충분하지 않으면 CPU 사용량이 증가할 수 있습니다.

다음 쿼리에서는 가장 많은 누적 CPU 사용량을 보이는 쿼리를 표시합니다.

SELECT 
    highest_cpu_queries.plan_handle, 
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from 
    (select top 50 
        qs.plan_handle, 
        qs.total_worker_time
    from 
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

다음 쿼리에서는 '%Hash Match%', '%Sort%' 등과 같이 CPU를 집중적으로 사용할 수 있는 몇 가지 의심해볼 만한 연산자를 표시합니다.

select *
from 
      sys.dm_exec_cached_plans
      cross apply sys.dm_exec_query_plan(plan_handle)
where 
      cast(query_plan as nvarchar(max)) like '%Sort%'
      or cast(query_plan as nvarchar(max)) like '%Hash Match%'

쿼리 계획이 충분하지 않아 CPU 사용량이 높아졌다는 사실을 확인했다면 쿼리에 관련된 테이블에 대해 UPDATE STATISTICS를 실행하고 문제가 지속되는지 확인합니다. 그런 다음 데이터를 수집하고 그 문제를 PerformancePoint 계획 지원 센터에 보고합니다.

시스템에서 컴파일과 재컴파일이 과도하게 이루어지면 시스템의 CPU 관련 성능에 문제가 발생할 수 있습니다.

다음 DMV 쿼리를 실행하면 과도한 컴파일/재컴파일을 찾아낼 수 있습니다.

select * from sys.dm_exec_query_optimizer_info
where 
counter = 'optimizations'
or counter = 'elapsed time'

다음 샘플 쿼리에서는 다시 컴파일된 상위 25개의 저장 프로시저를 표시합니다. plan_generation_num은 쿼리를 다시 컴파일한 횟수를 나타냅니다.

select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid 
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

과도한 컴파일 또는 재컴파일을 확인했다면 가능한 한 많은 데이터를 수집하여 계획 지원 센터에 보고합니다.

메모리 병목 현상

메모리 부족 문제에 대한 감지와 조사를 시작하려면 먼저 SQL Server에서 고급 옵션을 활성화해야 합니다. 마스터 데이터베이스에 대해 다음 쿼리를 실행하여 우선 이 옵션을 활성화합니다.

sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go

다음 쿼리를 실행하여 메모리 관련 구성 옵션을 먼저 검사합니다.

sp_configure 'awe_enabled'
go
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
sp_configure 'min memory per query'
go
sp_configure 'query wait'
go

다음 DMV 쿼리를 실행하여 CPU, 스케줄러 메모리 및 버퍼 풀 정보를 확인합니다.

select 
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info

I/O 병목 현상

I/O 병목 현상은 래치 대기 시간을 조사하여 확인합니다. 다음 DMV 쿼리를 실행하여 I/O 래치 대기 시간에 대한 통계를 확인합니다.

select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats  
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type

waiting_task_counts 및 wait_time_ms가 평상시와 비교하여 크게 달라졌으면 I/O 문제가 있는 것입니다. 이 비교를 위해서는 SQL Server가 안정적으로 실행되고 있을 때 성능 카운터와 주요 DMV 쿼리 출력의 기준선을 마련해 두는 것이 중요합니다.

이러한 wait_types를 살펴보면 I/O 하위 시스템이 병목 현상을 겪고 있는지 알 수 있습니다.

다음 DMV 쿼리를 사용하면 현재 보류 중인 I/O 요청을 확인할 수 있습니다. 이 쿼리를 정기적으로 실행하여 I/O 하위 시스템의 상태를 점검하고 I/O 병목 현상에 관련된 물리적 디스크를 격리하십시오.

select 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

정상적인 상태에서는 대개 쿼리를 실행해도 아무 것도 반환되지 않습니다. 만일 이 쿼리에서 어떤 행이 반환된다면 더 자세한 조사를 해볼 필요가 있습니다.

다음 DMV 쿼리를 실행하여 I/O 관련 쿼리를 찾을 수도 있습니다.

select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
                   (total_logical_writes/execution_count) as avg_logical_writes,
           (total_physical_reads/execution_count) as avg_physical_reads,
           Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
      cross apply sys.dm_exec_query_plan(plan_handle) p
      cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc

다음 DMV 쿼리를 사용하면 가장 많은 I/O를 생성하는 배치/요청이 무엇인지 찾을 수 있습니다. 다음과 같은 DMV 쿼리를 사용하면 가장 많은 I/O를 생성하는 상위 5개 요청을 찾을 수 있습니다. 이러한 쿼리를 조정하여 시스템 성능을 향상시킬 수 있습니다.

select top 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    sql_handle, 
    plan_handle
from sys.dm_exec_query_stats  
order by  (total_logical_reads + total_logical_writes) Desc

차단

다음 쿼리를 실행하면 차단 세션을 확인할 수 있습니다.

select blocking_session_id, wait_duration_ms, session_id from 
sys.dm_os_waiting_tasks
where blocking_session_id is not null

이 호출을 사용하면 blocking_session_id를 통해 반환되는 SQL을 찾을 수 있습니다. 예를 들어 blocking_session_id가 87인 경우 SQL을 확인하려면 다음 쿼리를 실행합니다.

dbcc INPUTBUFFER(87)

다음 쿼리에서는 SQL 대기 상태 분석 및 대기 중인 상위 10개 리소스를 표시합니다.

select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc

어떤 spid가 다른 spid를 차단하고 있는지 확인하려면 데이터베이스에 다음과 같은 저장 프로시저를 만들어 실행합니다. 이 저장 프로시저는 차단 상황을 보고합니다. @spid를 알아내려면 sp_who를 입력합니다. @spid는 선택적 매개 변수입니다.

create proc dbo.sp_block (@spid bigint=NULL)
as
select 
    t1.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id    
from 
    sys.dm_tran_locks as t1, 
    sys.dm_os_waiting_tasks as t2
where 
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = isnull(@spid,t1.request_session_id)

다음은 이 저장 프로시저를 사용하는 예제입니다.

exec sp_block
exec sp_block @spid = 7