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 28296
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 15463
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1897
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1284
» DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1356
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 915
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 769
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 3124
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1392
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1112
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1657
1705 BCP 사용 jevida(강성욱) 2016.09.13 3622
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1189
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 3090
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 9525
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1733
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1688
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1973
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1688
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 731
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1928
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 11683





XE Login