데이터베이스 개발자 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
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
» DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login