데이터베이스 연결 정보 수집

데이터베이스 연결 정보 수집

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 데이터베이스는 여러 응용프로그램에서 연결하여 사용한다. 문제는 이러한 응용프로그램이 제대로 연결을 종료하지 않을 때 발생 한다. 커넥션이 연결된 채로 종료 되지 않으면 다른 응용프로그램에서 커넥션을 할당하지 못하는 문제가 발생한다.

연결 개수를 모니터링 하여 어느 응용프로그램에서 연결을 많이 사용하는지 알아보자. 연결 개수를 모니터링 하기 위한 스크립트는 다음과 같다.

SELECT [host_name], [program_name], login_name, count(c.session_id ) num_sessions, getdate() FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id GROUP BY host_name, program_name, login_name ORDER BY 4 DESC

 

 

 

다음 스크립트를 실행하면 위의 연결 정보를 저장하는 테이블과 Job Agent를 생성하여 주기적으로 데이터를 수집할 수 있도록 생성한다.(수집 일정에 대한 부분은 사용자가 따로 설정 해주어야 함)

CREATE TABLE dbo._demo_sessions_alert( [host_name] nvarchar(128) NULL, [program_name] nvarchar(128) NULL, login_name nvarchar(128) NULL, num_sessions int NULL, capture_time datetime NULL ) ON [PRIMARY] GO   BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Sessions Monitoring', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Inserts log records when number of connections is higher than 50', @category_name=N'DBA', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log info', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'INSERT INTO dbo._demo_sessions_alert SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate() FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id GROUP BY host_name,program_name,login_name ORDER BY 4 DESC', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO

 

 

 

수집된 정보를 활용하여 임계치 이상 연결이 생성되어 있을 때 어떤 응용프로그램에서 많은 연결을 사용하는지 파악 할 수 있다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3193/automated-collection-of-sql-server-database-connections-for-monitoring/

 

 

2014-04-15 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, SQL 연결, sql connection, sql session