DMV를 활용한 SQL Server 모니터링

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

아침에 출근하자 마자 야근한 동료에게서 서버가 너무 느려서 업무 처리에 힘들었다는 연락을 받았다. 어제 밤 SQL Server에 무슨 일이 있었던 걸까? 어떤 일이 있었는지 우리는 명쾌하게 답변을 할 수 있을까?

DBA라면 SQL Server에 어떤 일이 일어나고 있는지 알아야 한다. 또한 모니터링 하는데 있어서 성능에 영향도 없어야 한다.

내가 없을 경우에도 모니터링을 하기 위해서는 지속적으로 모니터링 기록을 하는 도구가 필요하다. DMV를 통하여 지속적인 모니터링 기록을 남겨보자.

 

아래 스크립트는 SQL Server가 처리하고 있는 실행 코드를 캡처하는 기능을 제공한다.

SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name],

S.[client_interface_name], S.[login_name], R.*

FROM sys.dm_exec_requests R

INNER JOIN sys.dm_exec_sessions S

ON S.session_id = R.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T

CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P

GO

 

 

 

위의 스크립트에서 실행된 결과를 저장 하기 위하여 테이블을 생성 한다.

CREATE TABLE [dbo].[MyMonitorTable](

[text] [nvarchar](max) NULL,

[query_plan] [xml] NULL,

[host_name] [nvarchar](128) NULL,

[program_name] [nvarchar](128) NULL,

[client_interface_name] [nvarchar](32) NULL,

[login_name] [nvarchar](128) NOT NULL,

[session_id] [smallint] NOT NULL,

[request_id] [int] NOT NULL,

[start_time] [datetime] NOT NULL,

[status] [nvarchar](30) NOT NULL,

[command] [nvarchar](16) NOT NULL,

[sql_handle] [varbinary](64) NULL,

[statement_start_offset] [int] NULL,

[statement_end_offset] [int] NULL,

[plan_handle] [varbinary](64) NULL,

[database_id] [smallint] NOT NULL,

[user_id] [int] NOT NULL,

[connection_id] [uniqueidentifier] NULL,

[blocking_session_id] [smallint] NULL,

[wait_type] [nvarchar](60) NULL,

[wait_time] [int] NOT NULL,

[last_wait_type] [nvarchar](60) NOT NULL,

[wait_resource] [nvarchar](256) NOT NULL,

[open_transaction_count] [int] NOT NULL,

[open_resultset_count] [int] NOT NULL,

[transaction_id] [bigint] NOT NULL,

[context_info] [varbinary](128) NULL,

[percent_complete] [real] NOT NULL,

[estimated_completion_time] [bigint] NOT NULL,

[cpu_time] [int] NOT NULL,

[total_elapsed_time] [int] NOT NULL,

[scheduler_id] [int] NULL,

[task_address] [varbinary](8) NULL,

[reads] [bigint] NOT NULL,

[writes] [bigint] NOT NULL,

[logical_reads] [bigint] NOT NULL,

[text_size] [int] NOT NULL,

[language] [nvarchar](128) NULL,

[date_format] [nvarchar](3) NULL,

[date_first] [smallint] NOT NULL,

[quoted_identifier] [bit] NOT NULL,

[arithabort] [bit] NOT NULL,

[ansi_null_dflt_on] [bit] NOT NULL,

[ansi_defaults] [bit] NOT NULL,

[ansi_warnings] [bit] NOT NULL,

[ansi_padding] [bit] NOT NULL,

[ansi_nulls] [bit] NOT NULL,

[concat_null_yields_null] [bit] NOT NULL,

[transaction_isolation_level] [smallint] NOT NULL,

[lock_timeout] [int] NOT NULL,

[deadlock_priority] [int] NOT NULL,

[row_count] [bigint] NOT NULL,

[prev_error] [int] NOT NULL,

[nest_level] [int] NOT NULL,

[granted_query_memory] [int] NOT NULL,

[executing_managed_code] [bit] NOT NULL,

[group_id] [int] NOT NULL,

[query_hash] [binary](8) NULL,

[query_plan_hash] [binary](8) NULL

)

GO

 

 

 

SQL Server Agent에서 분 단위 또는 모니터링 할 시간 단위로 예약작업을 생성하여 캡처된 결과를 테이블에 저장한다.

INSERT INTO MyMonitorTable

SELECT T.text, P.query_plan, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.*

FROM sys.dm_exec_requests R

JOIN sys.dm_exec_sessions S on S.session_id=R.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T

CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P

GO

 

 

 

시간이 지나서 SQL Server 캡처문이 저장되면 조건에 맞는 쿼리를 호출하여 기록된 내용을 살펴 볼 수 있다.

(예제 그림은 blocking_session_id 조건을 포함하지 않은 결과 값임)

select

    SUBSTRING([text], (statement_start_offset/2)+1,

    ((case statement_end_offset

        when 01 then datalength([text])

        else statement_end_offset

        end - statement_start_offset)/2) +1) as statement_text,

        query_plan, program_name, session_id, request_id, status,

        command, blocking_session_id, wait_type, wait_time, last_wait_type

from dbo.MyMonitorTable

where blocking_session_id <> 0

 

 

 

결과값에서 query_play을 클릭하면 다음과 같이 그래픽 화면으로 실행계획을 볼 수 있다.

 

 

지금까지 DMV를 통한 모니터링은 매우 간단하면서 효과적인 방법이라 생각한다. SQL Server를 모니터링 하는데 있어서는 더 좋은 방법이 많다. DMV를 잘 활용하여 효과적이 모니터링을 할 수 있도록 하자.

 

참고자료

 


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 14474
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 8885
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1886
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1283
» DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1351
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 911
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 764
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 3114
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1384
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1108
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1653
1705 BCP 사용 jevida(강성욱) 2016.09.13 3613
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1186
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 3038
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 9366
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1691
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1679
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1948
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1676
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 725
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1919
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 11668





XE Login