데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1252
» DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1278
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 874
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 726
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 3028
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1321
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1075
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1587
1705 BCP 사용 jevida(강성욱) 2016.09.13 3492
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1161
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2639
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 8211
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1595
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1629
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1857
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1604
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 708
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1843
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 11271
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2072





XE Login