[MSSQL] 동적관리뷰 DMV

출처 : https://thinkgm.tistory.com/entry/%EB%8F%99%EC%A0%81-%EA%B4%80%EB%A6%AC-%EB%B7%B0

---------------------------------------------------------------

 

DMV(동적 관리 뷰, Dynamic Management Views)란?

• 성능 관련 정보를 수집하기 위한 도구.

• SQL Server 모니터링 역할.

• 성능 최적화 및 문제점을 진단하는 용도로 사용 됨.

 

1. DMV를 확인하기 위해 사전에 부여될 권한들

• GRANT VIEW SERVER STATE TO '계정'     / VIEW SERVER STATE 권한
• GRANT VIEW DATABASE STATE TO '계정' /  VIEW DATABASE STATE 권한 부여
  

 

2. 동적 관리 함수 및 뷰 리스트 정보

 

3. 자주쓰는 동적 관리뷰

• sys.dm_exec_query_stats
• sys.dm_exec_procedure_stats
• sys.dm_exec_sql_text
• sys.dm_exec_query_plan

   

[sys.dm_exec_query_stats 정보]

 

[sys.dm_exec_procedure_stats 정보]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

사용 용도 : 

1. 조각화가 많이 발생하는 인덱스 확인.

[참고 이미지]

실행 시간 : 17분 소요.

 

2. CPU 사용량이 높은 쿼리 확인

 

3. 비용이 드는 쿼리 찾아내기

 

4.  SP 분석하기

 

 

 

1. 저장프로시져별 실행수 뽑기 

select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle)st
where DB_Name(st.dbid) is not null and cp.objtype = 'proc'
group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.execution_count) desc


2. CPU소모량이 많은 저장프로시져 뽑기

select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time
from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where db_name(st.dbid) is not null and cp.objtype='proc'
group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_worker_time) desc


3. IO량이 많은 저장프로시져 뽑기

select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum
(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc


4. 처리시간이 긴 저장프로시져 뽑기

select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc

출처: https://dbrang.tistory.com/716?category=198409 [dBRang [dɪ'·bɪ·raŋ]]

 

 

XEvent(확장 이벤트)

: 성능 리소스를 적게 사용하는 간단한 성능 모니터링 시스템.

확장 이벤트에서는새 세션 마법사 와 새 세션이라는 두 가지 그래픽 사용자 인터페이스가 제공되므로 세션 데이터를 작성, 수정, 표시 및 분석할 수 있습니다.

[용도]

 

• 가장 비용이 많이 드는 쿼리 찾기

• 래치 경합의 근본 원인 찾기

• 다른 쿼리를 차단하는 쿼리 찾기

• 쿼리 재컴파일에 의해 발생하는 과도한 CPU 사용 문제 해결

• 교착 상태 해결

 

참고 링크 :  https://docs.microsoft.com/ko-kr/sql/relational-databases/extended-events/advanced-viewing-of-target-data-from-extended-events-in-sql-server?view=sql-server-2017

 

예시)

 

 

CREATE TABLE TestTable ( c1 INT IDENTITY, c2 CHAR (1000) DEFAULT 'a'); GO   INSERT INTO [TestTable] DEFAULT VALUES; GO

 

 

 

XEvent를 설정한다.

 

-- Drop the session if it exists. IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE [name] = N'MonitorLog') DROP EVENT SESSION [MonitorLog] ON SERVER GO   -- Create the event session CREATE EVENT SESSION [MonitorLog] ON SERVER ADD EVENT [sqlserver].[file_write_completed], ADD EVENT [sqlserver].[transaction_log] ADD TARGET [package0].[ring_buffer] WITH (MAX_MEMORY = 50MB, max_dispatch_latency = 1 seconds) GO   -- Start the session ALTER EVENT SESSION [MonitorLog] ON SERVER STATE = START; GO

 

 

 

 

참고 자료

https://docs.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-2017 

https://rocabilly.tistory.com/103  

https://m.blog.naver.com/PostView.nhn?blogId=chsmanager&logNo=220511665320&proxyReferer=https%3A%2F%2Fwww.google.com%2F

https://rocabilly.tistory.com/105

http://www.sqler.com/bColumn/900579

 

출처: https://thinkgm.tistory.com/entry/동적-관리-뷰 [minkgm12]

출처: https://thinkgm.tistory.com/entry/동적-관리-뷰 [minkgm12]