SQL Server 상태 모니터링

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

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

  - dm_db_* : 데이터베이스 및 데이터베이스 개체
  - dm_exec_* : 사용자 코드 및 관련 연결 실행
  - dm_os_* : 메모리, 잠금 및 예약
  - dm_tran_* : 트랜잭션 및 격리
  - dm_io_* : 네트워크 및 디스크의 입/출력

/*** DMV(동적 관리 뷰) 의 모든 이름을 확인 할 수 있다. *************************/
SELECT * FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name


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

/**********************************************************************
현재 캐시된 배치나 프로시저 중 CPU 사용률이 가장 높은 항목
total_worker_time : 컴파일된 이후 실행되는 데 사용된 총 CPU시간(마이크로초)
execution_count : 컴파일된 이후 실행된 횟수
sql_handle : statement_start_offset 및 statement_end_offset과 함께 사용되어 sys.dm_exec_sql_text 동적 관리 함수를 호출하여 쿼리의 SQL Text를 검색할 수 있다.
**********************************************************************/
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


/**********************************************************************
앞서 말했듯이 sys.dm_exec_sql_text로 Query Text 를 함께 보여준다.
**********************************************************************/
SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.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
      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


/**********************************************************************
재 컴파일 된 상위 25개의 프로시저
plan_generation_num : 컴파일한 횟수
plan_handle : 컴파일 계획을 참조하고 있는 토큰 sys.dm_exec_query_plan 와 연결하여 Query plan를 가지고 올 수 있다.
**********************************************************************/
SELECT TOP 25
      sql_text.text,
      sql_handle,
      plan_generation_num,
      execution_count,
      dbid,
      objectid 
FROM sys.dm_exec_query_stats AS 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 시간별 상위 25개의 Query 실행 계획
**********************************************************************/
SELECT TOP 25 
      total_worker_time/execution_count AS [Avg CPU Time],
      Plan_handle, 
      query_plan 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time


/**********************************************************************
현재 SQL Server 메모리를 나타낸다.
cpu_count : 시스템의 논리적 cpu 갯수
hyperthread_ratio : 논리적 cpu 갯수와 실제 프로세스 갯수
physical_memory_in_bytes : 사용할 수 있는 실제 메모리 양
virtual_memory_in_bytes : 사용할 수 있는 가상 메모리 양
bpool_committed : 버퍼풀에 커밋된 실제 메모리, 예약된 메모리는 불 포함 즉, 현재 SQL Server가 사용하고 있는 메모리를 듯 함
bpool_commit_target : 현재 SQL이 사용하겠다고 요청한 메모리의 양

bpool_commit_target 값이 bpool_committed 보다 작으면 메모리 부족
**********************************************************************/
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


/**********************************************************************
LATCH 대기시간으로 I/O 병목현상을 확인 하기 위한 Query이다. 하지만 확인 하기 위해서는 미리 기준점을 만들 필요가 있다.

wait_type : 대기 유형 이름
waiting_tasks_count : 대이 유형의 대기 수, 대기가 시작 될때 증가
wait_time_ms : 대기 유형의 총 대기 시간
signal_wait_time_ms : 대기 스레드가 신호를 받은 시간과 실행을 시작한 시간의 차

대기유형 목록 : http://technet.microsoft.com/ko-kr/library/ms179984.aspx

LATCH란? 경량 잠금이라고도 하고 경량 잠금이란? 가볍고 짧은 동기화 개체라고 말한다.
음.. 즉 LATCH는 잠금처럼 동작을 하고 예상하지 못한 변화로 부터 데이터를 보호하기 위해 존재를 한다고 볼 수 있다. 쉬운 예를 들어, 하나의 행이 버퍼로부터 SQL 서버의 저장소 엔진으로 이동될 때, 이 짧은 시간 동안의 이동 중에 행 내부의 데이터가 변형 되지 못하도록 SQL 서버가 사용하는것이 LATCH이다. 즉, LATCH가 긿어지면 성능에 안좋은 영향이 미치는것이다.
**********************************************************************/
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

출처: http://ddoung2.tistory.com/18 [DDoung2]