우리는 많은 수의 데이터베이스를 운영하고 있고, 데이터베이스에 대한 연결 정보를 관리한다. 그리고 어플리케이션에서는 연결정보를 제대로 닫지 못한다고 의심을 한다. 이런일들이 발생하는 것에 대해 모니터링 하기 위해 자동화된 방법이 필요하다.
정보를 수집하기 위해서 SQL Server Agent를 설치하고, Connection 정보가 있을때 마다 Agent 작업을 실행할 것이다. 50개의 Connection으로 간단히 테스트를 해보겠습니다.
이 작업은 어플리케이션과 Connection 정보를 테이블에 저장합니다.
1. 모니터링 데이터를 저장할 테이블 생성
: 작업에 의해 데이터가 저장될 테이블을 생성합니다.
1 | CREATE TABLE perf_warehouse.dbo._demo_sessions_alert( |
2 | [host_name] nvarchar(128) NULL, |
3 | [program_name] nvarchar(128) NULL, |
4 | login_name nvarchar(128) NULL, |
6 | capture_time datetime NULL |
2. SQL Server Agent 작업을 생성
: 아래의 스크립트를 Agent Job의 Step에 넣어 작업을 생성합니다.
1 | INSERT INTO perf_warehouse.dbo._demo_sessions_alert |
5 | count(c.session_id ) num_sessions, |
7 | FROM sys.dm_exec_connections c JOIN |
8 | sys.dm_exec_sessions s on c.session_id = s.session_id |
9 | GROUP BY host_name, program_name, login_name ORDER BY 4 DESC |
모든 Connection 정보를 캡쳐하거나, "SELECT TOP 10 [host_name]..." 으로 상위 몇 개만을 캡쳐할 수 있습니다.
아래 쿼리는 Agent Job 까지 모두 생성하는 스크립트입니다.
02 | DECLARE @ReturnCode INT |
04 | IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories |
05 | WHERE name=N'DBA' AND category_class=1) |
07 | EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA' |
08 | IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback |
10 | DECLARE @jobId BINARY(16) |
11 | EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Sessions Monitoring', |
13 | @notify_level_eventlog=0, |
14 | @notify_level_email=2, |
15 | @notify_level_netsend=0, |
18 | @description=N'Inserts log records when number of connections is higher than 50', |
19 | @category_name=N'DBA', |
20 | @owner_login_name=N'sa', |
21 | @job_id = @jobId OUTPUT |
22 | IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback |
23 | EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log info', |
25 | @cmdexec_success_code=0, |
27 | @on_success_step_id=0, |
32 | @os_run_priority=0, @subsystem=N'TSQL', |
33 | @command=N'INSERT INTO perf_warehouse.dbo._demo_sessions_alert |
34 | SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate() |
35 | FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id |
36 | GROUP BY host_name,program_name,login_name ORDER BY 4 DESC', |
37 | @database_name=N'master', |
39 | IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback |
40 | EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 |
41 | IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback |
42 | EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' |
43 | IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback |
47 | IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION |
SQL Server Agent Alert(알림) 생성하기
Connections 수가 50 이상이 될 경우 성능 경고를 해주는 경고(알림)를 생성하려고 합니다.( 2분 간격 ) 아래의 알림은 위에서 만든 작업을 가지고 생성됩니다.
아래의 쿼리는 Alert(알림)을 만들 수 있는 전체 스크립트입니다. ( "@operator_name" 파라미터는 본인의 시스템에서 사용하는 operator 값을 넣어야 합니다. )
01 | EXEC msdb.dbo.sp_add_alert @name=N'Perf: General Statistics: User Connections', |
05 | @delay_between_responses=120, |
06 | @include_event_description_in=1, |
07 | @performance_condition=N'SQLServer:General Statistics|User Connections||>|50', |
08 | @job_name=N'Sessions Monitoring' |
10 | EXEC msdb.dbo.sp_add_notification @alert_name=N'Perf: General Statistics: User Connections', |
11 | @operator_name=N'DBA_Operator', |
12 | @notification_method = 1 |
Alert(알림) 테스트
서버의 Connections 수가 50 이상이 되면 email의 알림을 받을 것입니다. 그리고 아래의 스크립트를 가지고 Log 테이블 다시 보실 수 있습니다.
1 | SELECT TOP 5 [host_name] |
6 | FROM [dbo].[_demo_sessions_alert] |
7 | ORDER BY [capture_time] DESC, [num_sessions] DESC |
이 정보를 가지고 어플리케이션이나 host 별로 가장 많은 Connection을 확인해 볼 수 있습니다.
위에서 테스트로 설정한 Connection 수 50은 일반적인 서버에서 낮은 값일 수 있고, Alert가 너무 많을 수 있습니다. 이 원문을 작성한 Svetlana Golovko 블로거의 회사에서는 연결을 종료하지 않는 어플리케이션의 문제를 추적하기 위해서 1000 Connections 로 설정해서 사용했다고 합니다.
출처 : http://iheedol.tistory.com/entry
