DMV를 활용한 병목 점검 예제

DMV를 활용한 병목 점검 예제

카탈로그 뷰

디스크에 관리되는 메타 데이터이다.
DDL 문으로 생성된 오브젝트들이 반영된 뷰이다.
DBA가 사용자 생성 오브젝트들의 이름과 구조를 확인하는데 사용한다.

DMV

변화하는 서버의 상태 정보를 제공한다. (다중의 세션들, 다중의 트랜잭션, 다중의 요청과 관련)
서버 프로세스 자체 내부 또는 서버의 모든 세션들에 대하여 진행 중인 일들이 반영된다.
진단, 메모리 튜닝, 프로세스 튜닝, 서버 내의 모든 세션들을 모니터링하는데 사용된다

카탈로그 뷰

SQL Server 2005 데이터베이스 엔진이 사용하는 정보를 반환하는 뷰
카탈로그 메타 데이터에 대한 일반적인 인터페이스를 제공하면서, 메타 데이터를 사용자가 보기 편한 형태로 변환하여 보여 주는 가장 효율적인 방법 ? 시스템 테이블을 직접 쿼리하지 말고, 카탈로그 뷰를 사용하는 것을 권고함
사용자가 사용 가능한 카탈로그 메타 데이터는 모두 카탈로그 뷰로 제공됨
예;
sys.tables
sys.procedures
sys.indexes
개별 카탈로그 뷰에 대한 정보는 SQL Server Books Online 참조 요망

 

카탈로그 뷰 종류
--------------------------------------------------

CLR Assembly Catalog Views
Databases and Files Catalog Views
Database Mirroring Catalog Views
Data Spaces and Fulltext Catalog Views
Endpoints Catalog Views
Extended Properties Catalog Views
Linked Servers Catalog Views
Messages (For Errors) Catalog Views
Objects Catalog Views
Partition Function Catalog Views
Scalar Types Catalog Views
Schemas Catalog Views
Security Catalog Views
Service Broker Catalog Views
Server-wide Configuration Catalog Views
XML Schemas (XML Type System) Catalog Views

일반적인 서버 DMV와 DMF
-----------------------------------------------------

<<서버 레벨>>
- dm_exec_* : 사용자 코드와 관련 커넥션들의 실행

- dm_os_*: 메모리, 잠금, 스케줄링과 같은 low level system (server-wide) 정보
- dm_tran_*: 트랜잭션과 격리 (isolation)
- dm_io_*: 네트워크와 디스크 상에서의 Input/Output
- dm_db_*: 데이터베이스와 데이터베이스 오브젝트

<<컴포넌트 레벨>>

- dm_repl_*: 복제 (Replication)
- dm_broker_*: SQL 서비스 브로커
- dm_fts_*: 전체 텍스트 검색 (Full Text Search)
- dm_qn_*: Query Notifications
- dm_clr_*: Managed code의 CLR 실행

전형적인 리소스 이슈

CPU
Memory
IO
인덱스
잠금 (Locking)
네트워크

 

DMV를 활용한 모니터링

현재 실행 중인 SQL 문 확인
리소스를 많이 사용하는 상위 저장 프로시저 및 일괄 처리(Batch)
Parallelism
대기
블로킹 및 교착 상태
쿼리 실행 계획
컴파일과 재컴파일
인덱스 활용
Tempdb 사용
커서 사용

 

리소스 병목 찾기 CPU: 현재 실행 중인 SQL 문

=================================================================

select r.session_id
  ,status
  ,substring(qt.text,r.statement_start_offset/2, 
   (case when r.statement_end_offset = -1 
   then len(convert(nvarchar(max), qt.text)) * 2 
   else r.statement_end_offset end -    r.statement_start_offset)/2) 
  as query_text   -- 이쿼리를실행하는시점에실행되는SQL문
  ,qt.dbid
  ,qt.objectid
  ,r.cpu_time
  ,r.total_elapsed_time
  ,r.reads
  ,r.writes
  ,r.logical_reads
  ,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
      and r.session_id <> (select  @@SPID)  -- 자기 자신 쿼리는 제외 
order by r.scheduler_id, r.status, r.session_id

 

=================================================================

리소스 병목 찾기
CPU를 많이 사용하는 상위 프로시저 및 일괄처리(Batch)

 

select top 50 
sum(qs.total_worker_time) as total_cpu_time, 
sum(qs.execution_count) as total_execution_count,
count(*) as  '#_statements',
qt.dbid, qt.objectid, qs.sql_handle,
qt.[text]
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
group by qt.dbid,qt.objectid, qs.sql_handle,qt.[text]
order by sum(qs.total_worker_time) desc,qs.sql_handle

select top 50 
sum(qs.total_worker_time) as total_cpu_time, 
sum(qs.execution_count) as total_execution_count,
count(*) as  '#_statements',
qt.dbid, qt.objectid, qs.sql_handle,
qt.[text]
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
group by qt.dbid,qt.objectid, qs.sql_handle,qt.[text]
order by sum(qs.total_worker_time) desc,qs.sql_handle

====>

 total_cpu_time     total_execution_count     #_statements     dbid     objectid     sql_handle   text
 
 
 Statement별 Top CPU 사용
                                      
SQL2005
-- Avg CPU Time 기준 상위 50개 SQL 문
SELECT TOP 50
        qs.total_worker_time/qs.execution_count as [Avg CPU Time],
        SUBSTRING(qt.text,qs.statement_start_offset/2, 
   (case when qs.statement_end_offset = -1 
   then len(convert(nvarchar(max), qt.text)) * 2 
   else qs.statement_end_offset end -qs.statement_start_offset)/2) 
  as query_text,
  qt.dbid,
  qt.objectid 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY 
        [Avg CPU Time] DESC

 

-------------------->

 

 Avg CPU Time     query_text 

CPU ? 병렬로 실행중인 프로세스 확인       

DW의 경우에는 병렬처리(Parallelism)가 좋지만, OLTP의 경우에는 병렬처리가 반드시 좋은 것만은 아님
SQL 2000 ? 지원하지 않음

SQL 2005
select r.session_id,
 r.request_id,
 max(isnull(exec_context_id, 0)) as number_of_workers,
 r.sql_handle,
 r.statement_start_offset,
 r.statement_end_offset,
 r.plan_handle
from sys.dm_exec_requests r
 join sys.dm_os_tasks t on r.session_id = t.session_id
 join sys.dm_exec_sessions s on r.session_id = s.session_id
where s.is_user_process = 0x1
group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, 
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

                     
                     
                     
CPU: Parallelism과 CPU 사용        

select 
qs.sql_handle, 
qs.statement_start_offset, 
qs.statement_end_offset, 
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from sys.dm_exec_query_stats qs
 cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time

CPU: 컴파일 시간

SQL 2005 ? before / after 스냅샷을 관리함
최적화 과정에 소요된 시간은 일반적으로 최적화를 위하여 사용된 CPU 시간과 유사함

Select * from sys.dm_exec_query_optimizer_info

counter                 occurrence           value                                                 
---------------       -----------------   ------------------------------------------------ 
optimizations         81                       1.0
elapsed time          81    .064547820702944486

 

CPU Pressure: 재컴파일

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

 

메모리가 어디에 얼마나 할당되어 있는지 점검
내부 서버 컴포넌트들
Sys.dm_os_memory_clerks
Dm_exec_requests
SPs, Sorts
Worker threads
Sys.dm_os_workers
Memory grants pending
Sys.dm_os_wait_stats
SQLServer : Memory Manager ? Memory Grants Pending
 얼마나 많은 사용자 또는 프로세스가 메모리 허가를 기다리고 있는지 확인 가능. 메모리가 부족하면 더 많은 수의 사용자 프로세스들이 메모리를 대기하게 되므로 Memory Grants Pending 값 증가함.

 database_id     object_id     objname                    index_id     buffer_count    
 --------------  ------------  -------------------------  -----------  --------------- 
 32767           60            sysobjvalues               1            2705            
 32767           41            syscolpars                 1            334             
 4               60            sysobjvalues               1            87              
 1               60            sysobjvalues               1            80              
 4               41            syscolpars                 1            63              
 32767           34            sysschobjs                 1            56              
 4               94            sysobjkeycrypts            1            32              
 32767           94            sysobjkeycrypts            1            28              
 32767           34            sysschobjs                 3            27              
 1               41            syscolpars                 1            23              
 
 
 
 
 리소스 병목 찾기 메모리: 오브젝트 및 인덱스를 기준으로 한 상위 메모리 사용
 
 
 
 메모리 버퍼를 오브젝트 별로 분류 (테이블,인덱스)

select b.database_id
  ,p.object_id
  ,object_name(p.object_id) as objname
  ,p.index_id
  ,buffer_count=count(*)
from sys.allocation_units a,
  sys.dm_os_buffer_descriptors b,
  sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
group by b.database_id,p.object_id, p.index_id
order by buffer_count desc

리소스 병목 찾기
IO를 많이 발생시키는 상위 SQL 문

SELECT TOP 50
        (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
      SUBSTRING(qt.text,qs.statement_start_offset/2,    (case when qs.statement_end_offset = -1 
   then len(convert(nvarchar(max), qt.text)) * 2 
   else qs.statement_end_offset end -qs.statement_start_offset)/2) 
  as query_text,
  qt.dbid,
  qt.objectid 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg IO] DESC

--> 
 Avg IO     query_text
 
 
 
 리소스 병목 찾기
 파일 별 IO 대기
 
 
 
------------------->

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

 database_id     file_id     io_stall     io_pending_ms_ticks     scheduler_address    
 --------------  ----------  -----------  ----------------------  --------------------

 쿼리 실행 계획 확인
 
 
 
 SQL 2005
실행 계획이 캐시에 남아 있는 한 확인 가능함

 select * from sys.dm_exec_query_plan (plan_handle) 
실행 계획이 캐시에서 쫓겨 나는 경우에는, 프로필러로 실행 계획과 통계를 수집

 

리소스 병목 찾기
IO 할당 대기

 

Tempdb (DBID=2)
 Select session_id, wait_duration_ms, resource_description
 From sys.dm_os_waiting_tasks
 Where wait_type like 'PAGE%LATCH_%' 
 and resource_description like '2:%'
 
사용자 DB (다음에서 dbid 부분을 db_id() 값을 입력하여 실행하면 됨 )
 resource_description like ‘dbid:%’

Page%Latch 대기 확인
 select * from Sys.dm_os_wait_stats
 
 
 리소스 병목 찾기
 - IO Pressure: 사용되지 않는 사용자 인덱스

 

유용하지 않은 사용자 인덱스를 확인할 수 있음
예제

select object_name(i.object_id), i.name 
from sys.indexes i, sys.objects o 
where  i.index_id NOT IN (select s.index_id 
       from sys.dm_db_index_usage_stats s 
    where s.object_id=i.object_id and 
    i.index_id=s.index_id and 
   database_id = <dbid> )  /* dbid : db_id() 또는 dbid값을 입력 */
and o.type = 'U'
and o.object_id = i.object_id
order by object_name(i.object_id) asc

 

리소스 병목 찾기 IO Pressure: 누락된 인덱스

옵티마이저가 누락된 인덱스 정보를 알려 줌
xml plan 참조 요망
Select * from sys.dm_exec_query_plan(plan_handle)

DMV가 상세 정보를 제공함
Sys.dm_db_missing_index_group_stats
Sys.dm_db_missing_index_groups
Sys.dm_db_missing_index_details

리소스 병목 찾기 Tempdb 사용

Select SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2

 user_objects_kb     internal_objects_kb     version_store_kb     freespace_kb    
 ------------------  ----------------------  -------------------  --------------- 
 192                 128                     0                    6848        
 
 리소스 병목 찾기
 Tempdb 사용: task 단위
 
 SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
 deallocated 
from sys.dm_db_session_space_usage as t1, 
(select session_id, 
   sum(internal_objects_alloc_page_count)
      as task_alloc,
   sum (internal_objects_dealloc_page_count) as 
  task_dealloc 
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC

 session_id     allocated     deallocated    
 -------------  ------------  -------------- 
 51             64            56             
 52             0             0       
 
 
 리소스 병목 찾기
 Tempdb 사용: statement 단위
 
 
select t1.session_id, t1.request_id, t1.task_alloc,
  t1.task_dealloc,t2.sql_handle, t2.statement_start_offset, 
t2.statement_end_offset, t2.plan_handle
from (Select session_id, request_id,
                sum(internal_objects_alloc_page_count) as task_alloc,
               sum (internal_objects_dealloc_page_count) as task_dealloc
         from sys.dm_db_task_space_usage 
        group by session_id, request_id) as t1, sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and (t1.request_id = t2.request_id)
order by t1.task_alloc DESC

 session_id     request_id     task_alloc     task_dealloc     sql_handle                                        statement_start_offset     statement_end_offset     plan_handle                                      
 -------------  -------------  -------------  ---------------  ------------------------------------------------  -------------------------  -----------------------  ------------------------------------------------ 
 1              0              0              0                (null)                                            (null) 
 
 
 
 리소스 병목 찾기 비효율적인 커서 사용
 
 
     Fetch Buffer 크기가 1행인 API 커서를 사용하는 연결 정보 확인
만약 반드시 커서를 사용해야만 하는 경우라면, Fetch Buffer 크기를 1이 아닌 좀 더 큰 값을 사용하는 것이 보다 효율적임 (예를 들어, 100 행)

 

select cur.* from sys.dm_exec_connections concross apply sys.dm_exec_cursors(con.session_id) as cur
where cur.fetch_buffer_size = 1 and 
cur.properties LIKE 'API%' -- API 커서

 

리소스 병목 찾기 인덱스 사용 상세 정보

Sys.dm_db_index_operational_stats
인덱스 사용에 대한 통계 자료를 이해하기 쉬운 형태로 제공함
제공 정보
액세스 유형 (예를 들어, 범위 쿼리인지, 단일 룩업인지…)
잠금의 갯수 (예를 들어, 행 잠금, 페이지 잠금 등)
블로킹 또는 대기 발생 횟수
블로킹 또는 대기 유지시간
페이지 래치 대기 발생횟수
페이지 래치 대기 유지시간
잎(Leaf) 레벨에서의 Insert, update, delete 횟수
잎(Leaf) 레벨의 상위 레벨에서의 Insert, update, delete 횟수
잎 레벨에서 병합된 페이지 수 (행 삭제로 인하여 할당 취소된 빈 페이지들을 나타냄)

리소스 병목 찾기: 
블로킹과 교착상태(Deadlock) 추적

SQL2005
- 추적 (Trace) 
Deadlock 
TraceLog에 로깅된 그래픽 형태의 교착 상태 정보
블로킹 당한 프로세스 리포트
사용자가 지정 가능
Exec sp_configure ‘blocked process threshold’,200
블로킹 체인을 TraceLog에 기록 
조사 작업은 추후 수행 가능

- Dm_os_waiting_tasks (waiting_task_address, spid, blocking_task_address)
Dm_os_tasks (spid, task_address)
Dm_exec_requests (session_id,request_id)
Task들과 관련되는 잠금
min, max, duration, avg 제공

 

blocked process threshold
 초 단위 
 블로킹당한 프로세스 리포트에 대한 
  임계치 설정
- 0부터 86,400 까지 설정 가능

 

리소스 병목 찾기 블로킹: sp_block

create proc dbo.sp_block (@spid bigint=NULL)
as
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights. 
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
-- This proc reports blocks
-- 1. optional parameter @spid 
select t1.resource_type
 , 'database'=db_name(resource_database_id) 
, 'blk object' =isnull(object_name(t1.resource_associated_entity_id) ,t1.resource_associated_entity_id)
 ,t1.request_mode
 ,t1.request_session_id   -- spid 
 ,t2.blocking_session_id  -- spid 
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)

리소스 병목 찾기 - 쿼리 성능 튜닝

 

Sys.dm_exec_requests
Sys.dm_exec_query_stats
현재 캐시에 있는 쿼리 실행 계획 당 한 행
Min, max, avg, last
실행 소요 시간, 물리적 읽기, 논리적 읽기, 쓰기 등
누적 실행 횟수
최초 및 최종 실행 시각
Sys.dm_exec_sql_text(sql_handle)
인 메모리 SQL 텍스트
Sys.dm_exec_query_plan(plan_handle)
인 메모리 실행 계획

디폴트 추적(Default Trace)

 

최소의 부하, 소용량 관리 이벤트
DDL, DBCC, sp_configure 등을 추적
sp_configure로 비활성화 가능함 (‘default trace enabled’ 옵션)
오브젝트 생성, 오브젝트 삭제
인덱스 포함
실무 활용 예: IO spike와 인덱스 삭제의 관련성 분석
시도(attempt)와 실제로 성공한 작업이 모두 포함됨
100 MB 순환 구조 
최다 5개의 20 MB 크기 파일에 로깅된다 
하나의 파일이 가득 차거나 서버가 재시작되면 새로운 파일이 생성된다
스크립트 예:
  select t.StartTime, t.EventClass, e.name, 
  t.DatabaseName, t.ObjectName, t.ObjectID, t.IndexID
from fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_10.trc',0) t
  join sys.trace_events e
on e.trace_event_id = t.eventclass
Order by t.StartTime

디폴트 추적 이벤트들 Select distinct(eventid) from fn_trace_geteventinfo(1)

18  Audit Server Starts And Stops
20  Audit Login Failed
22  ErrorLog
46  Object:Created
47  Object:Deleted
55  Hash Warning
69  Sort Warnings
79  Missing Column Statistics
80  Missing Join Predicate
81  Server Memory Change
92  Data File Auto Grow
93  Log File Auto Grow
94  Data File Auto Shrink
95  Log File Auto Shrink
102 Audit Database Scope GDR Event
103  Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change  Property Event
108 Audit Add Login to Server  Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB  Role Event
111 Audit Add Role Event
115 Audit Backup/Restore Event
116 Audit DBCC Event
152 Audit Change Database  Owner
153 Audit Schema Object Take  Ownership Event
155 FT:Crawl Started
156 FT:Crawl Stopped
157 FT:Crawl Aborted
164 Object:Altered
167 Database Mirroring State  Change

요약
누가, 무엇을, 언제, 어디서, 왜

 

서버의 성능이 갑자기 저하되거나 SQL 문이 제 시간에 결과를 반환하지 않거나 서버가 갑자기 다운되는 경우에, 도대체 무슨 일이 일어나고 있는 것일까?
고객이 요청하기 전에 사전에 문제를 해결할 수 있는 방법은 없는가 ?

2005
DMV와 DMF 제공
관리자 전용 연결 기능 제공
부하를 최소로 주는 추적 기능 제공
디폴트 추적
성능 정보

SQLDiag가 수집 가능한 정보

프로필러 추적
블로킹 정보
SQL Server 환경 정보
SQL Server error log
성능 로그
이벤트 로그
MSInfo

SQLDiag 구문

SQLdiag
 { [/? 도움말] }
     | { [/I 환경 설정 파일]
       [/O 출력 폴더 경로]
       [/P support 폴더 경로]
       [/N 출력 폴더 관리 옵션]
       [/C 파일 압축 타입]
       [/B [+]시작 시간]
       [/E [+]종료 시간]
       [/A SQLdiag 어플리케이션 이름]
       [/Q Quiet 모드로 실행] 
       [/G Generic 모드로 실행]
       [/R 서비스 등록] 
       [/U 서비스 삭제] 
       [/L 반복 모드로 실행] 
       [/X 스냅샷 모드로 실행] }
     |     { [START | STOP | STOP_ABORT] }
     |     { [START | STOP | STOP_ABORT] /A SQLdiag 어플리케이션 이름 }

사용 예
 SQLDiag /B +01:00:00 /E +03:00:00
 SQLDiag /B +01:00:00 /E 08:30:00 
 SQLDiag /B 03:00:00 /X /L
 SQLDiag /I SD_Detailed.XML /O SQLDIAG_20051229 /B 12:10:00 /E 12:11:00
 SQLDiag /B +00:01:00 /E +00:02:00 /N 2