블로그 이미지

VB+WMI 로 Performance counter 를 DB에 저장하기

2013.05.06 22:19 Posted by 건방진연이

2013/05/05 - [SQLNULL] Study - 나만의 SQL Server 2008 성능 대시보드 만들기 에서 발표하였던 VB 스크립트와 WMI 객체를 이용하여 Performance Counter 를 Database에 저장하는 스크립티 이며, 해당 스크립트를 이용하여 원격지에 있는 서버의 Performance Counter를 저장 할 수도 있습니다.

 

1. VB 스크립트를 .vbs로 저장한다.

2. Database Table를 만든다.

3. 카운터를 저장시키는 SP를 만든다.

4. SQL Agent Job을 만들고 일정을 등록하여 일정 간격으로 실행하게 한다.

 

wMonitor.vbs

 

/*************************************************************************

**  Desc :

**      성능 카운터 데이터를 저장할 테이블을 생성한다.

*************************************************************************/

IF OBJECT_ID('dbo.AT_DBMS_Server_Perfmon_Counters', 'U') IS NOT NULL BEGIN

    DROP TABLE dbo.AT_DBMS_Server_Perfmon_Counters

END

 

CREATE TABLE [dbo].[AT_DBMS_Server_Perfmon_Counters] (

    Seq INT IDENTITY(1, 1) NOT NULL,

    CategoryName VARCHAR(500) NOT NULL, -- 성능카운터 분류

    CounterName VARCHAR(500) NOT NULL, -- 성눙카운터 이름

    Instance VARCHAR(100) NOT NULL DEFAULT '', -- 성능카운터 인스턴스 이름

    FriendlyName VARCHAR(100) NOT NULL, -- 단순한 이름

    Counters BIGINT NOT NULL, -- 카운터 수치

    RegDate SMALLDATETIME DEFAULT GETDATE() -- 성능카운터 등록

);

 

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'순번' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'Seq'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'성능카운터 분류' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'CategoryName'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'성능카운터 이름' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'CounterName'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'인스턴스 이름' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'Instance'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'단순한 이름' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'FriendlyName'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'카운터 수치' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'Counters'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'카운터 등록일' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters', @level2type=N'COLUMN',@level2name=N'RegDate'; GO;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'성능카운터 저장 테이블' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AT_DBMS_Server_Perfmon_Counters'; GO;

 

 

 

USE DBA

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER OFF

GO

 

-- ObjectName : AP_DBMS_Server_Perfmon_Counters_I

/*************************************************************************

**  Name : AP_DBMS_Server_Perfmon_Counters_I

**  Desc : 성능카운터 수집용 프로시저

**  EXEC :              

**  Auth : 이승연(gamebible)

**  Date : 2013.04.27

**************************************************************************

**  Change History

**************************************************************************

**  Date:       Author:             Description:

**  ----------  --------            ---------------------------------------

**  2013.04.27  이승연(gamebible)

*************************************************************************/

CREATE PROCEDURE [dbo].[AP_DBMS_Server_Perfmon_Counters_I]

    @CategoryName VARCHAR(500) , -- 성능카운터 분류

    @CounterName VARCHAR(500) , -- 성눙카운터 이름

    @Instance VARCHAR(100) = '', -- 성능카운터 인스턴스 이름

    @FriendlyName VARCHAR(100) , -- 단순한 이름

    @Counters BIGINT -- 카운터 수치

AS

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

    INSERT INTO [DBA].[dbo].[AT_DBMS_Server_Perfmon_Counters](

        CategoryName,

        CounterName,

        Instance,

        FriendlyName,

        Counters

    ) VALUES (

        @CategoryName ,

        @CounterName ,

        @Instance ,

        @FriendlyName ,

        @Counters

    )

 

END

 

 

USE [msdb]

GO

 

/****** Object:  Job [[DBA]] Perfmon]    Script Date: 2013-05-06 오후 9:45:57 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2013-05-06 오후 9:45:58 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBA] Perfmon',

               @enabled=1,

               @notify_level_eventlog=0,

               @notify_level_email=0,

               @notify_level_netsend=0,

               @notify_level_page=0,

               @delete_level=0,

               @description=N'설명이 없습니다.',

               @category_name=N'[Uncategorized (Local)]',

               @owner_login_name=N'이승연\gamebible', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [1]    Script Date: 2013-05-06 오후 9:45:58 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',

               @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'CmdExec',

               @command=N'cscript wMonitor.vbs localhost',

               @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_jobschedule @job_id=@jobId, @name=N'1',

               @enabled=1,

               @freq_type=4,

               @freq_interval=1,

               @freq_subday_type=2,

               @freq_subday_interval=30,

               @freq_relative_interval=0,

               @freq_recurrence_factor=0,

               @active_start_date=20130427,

               @active_end_date=99991231,

               @active_start_time=0,

               @active_end_time=235959

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

 

 

 

이렇게 하시면 디스크 여유공간과 해당 서버의 CPU 사용률이 저장이 됩니다. 더 많은 카운터를 저장하시고 싶으시면 WMIexplorer를 이용하여 WMI Query를 만드신 후 VB 스크립트에 적용하시면 됩니다.