DMV를 활용한 SQL Server 모니터링

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를 잘 활용하여 효과적이 모니터링을 할 수 있도록 하자.

 

참고자료

• http://www.mssqltips.com/sqlservertip/1861/sql-server-monitoring-scripts-with-the-dmvs/
• http://msdn.microsoft.com/ko-kr/library/ms189747.aspx
• http://msdn.microsoft.com/ko-kr/library/ms181929.aspx
• http://technet.microsoft.com/ko-kr/library/ms176013.aspx

 

 

 

2013-01-18 / 강성욱 / http://sqlmvp.kr