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
실행 응용프로그램 확인
SELECT es.session_id
,es.program_name
,es.login_name
,es.nt_user_name
,es.login_time
,es.host_name
,es.cpu_time
,es.total_scheduled_time
,es.total_elapsed_time
,es.memory_usage
,es.logical_reads
,es.reads
,es.writes
,st.text
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec
ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE es.session_id > 50
ORDER BY es.cpu_time DESC
