2013/05/09 - [Performance] - CLR로 Performance Counter를 DB에 저장하기
2013/05/06 - [Performance] - VB+WMI 로 Performance counter 를 DB에 저장하기
2013.05.04 DBNuri Study 에서 발표하였던 PowerShell을 이용하여 성능카운터를 데이터베이스에 저장하는 스크립트를 공유드립니다. 해당 스크립트는 아래 사이트에서 참고 또는 그대로 사용한 것 임을 먼저 말씀 드립니다. ^^
http://www.travisgan.com/2013/03/powershell-and-performance-monitor.html
http://www.codeproject.com/Articles/369888/Diagnosing-applications-using-Performance-Counters
해당 PowerShell 스크립트 도 위 사이트를 보시면 아시겠지만 기본적으로 중앙집중 관리 방식 입니다. 다시 말하면 1대의 서버에서 여러대의 성능카운터를 수집할 수 있도록 만들어진 스크립트입니다.
2. 성능카운터를 저장할 저장프로시저를 만든다.
3. PowerShell 스크립트를 SQL Agent Job을 이용하여 주기적으로 원하는 서버의 성능카운터를 수집한다.
기본적으로 파워쉘 에서는 아래와 같은 명령어로 손쉽게 성능카운터를 수집할 수 있으며,
해당 명령어에 -ComputerName 를 추가하면 원격 서버의 성능카운터를 출력할 수 있습니다.
일단 기본적인 파워쉘로 성능카운터를 출력하는 명령어를 보았고, 아래 파워쉘 스크립트는 SQL Agent Job에서 사용하게 될 스크립트입니다. 파워쉘 -Sqlcmd 스크립트를 이용하여 성능카운터를 저장하는 프로시저를 호출하는 형태입니다.
{
param(
[string]$server,
[string]$instance
)
[Microsoft.PowerShell.Commands.GetCounter.PerformanceCounterSampleSet]$collections
$monitorServer = "localhost"
$monitorDB = "DBA"
$counters = @("\Processor(_Total)\% Processor Time")
$collections = Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 1
$sampling = $collections.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue
$xmlString = $sampling | ConvertTo-Xml -As String
$query = "dbo.AP_PowerShell_PerfCounter_I '$server', '$instance', '$xmlString';"
Invoke-Sqlcmd -Database $monitorDB -Query $query
}
getPerfmonData -server "localhost" -instance "mssqlserver"
위 스크립트에 보시면 $counters = @("\Processor(_Total)\% Processor Time") 부분에 더 기록하시고 싶으신 카운터를 기술하시면 되며, 아래 스크립트는 해당 스크립트을 저장시킬 테이블과 저장프로시저 입니다.
USE [DBA]
GO
/****** Object: Table [dbo].[AT_PowerShell_PerfCounter] Script Date: 2013-05-04 오전 7:47:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AT_PowerShell_PerfCounter](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Server] [varchar](30) NULL,
[SQLInstance] [varchar](30) NULL,
[TimeStamp] [varchar](30) NULL,
[Counter] [varchar](200) NULL,
[CounterValue] [decimal](18, 5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[AP_PowerShell_PerfCounter_I] Script Date: 2013-05-03 오후 11:52:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[AP_PowerShell_PerfCounter_I]
(
@Server varchar(30),
@SQLInstance varchar(30),
@xmlString varchar(max)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xml xml;
SET @xmlString = REPLACE(@xmlString , '', '')
SET @xml = @xmlString
INSERT INTO dbo.AT_PowerShell_PerfCounter (Server, SQLInstance, [TimeStamp], Counter, CounterValue)
SELECT @Server, @SQLInstance, [Timestamp], [Path], [CookedValue]
FROM
(SELECT
[property].value('(./text())[1]', 'VARCHAR(100)') AS [Value],
[property].value('@Name', 'VARCHAR(30)') AS [Attribute],
DENSE_RANK() OVER (ORDER BY [object]) AS [Sampling]
FROM @xml.nodes('Objects/Object') AS mn ([object])
CROSS APPLY mn.object.nodes('./Property') AS pn (property)) AS bp
PIVOT (MAX(value) FOR Attribute IN ([Timestamp], [Path], [CookedValue]) ) AS ap;
END
SQL Agent Job 스크립트 입니다. 매일 30초에 한번씩 파워쉘을 실행하므로써 성능카운터를 저장합니다.
USE [msdb]
GO
/****** Object: Job [[DBA]] PowerShell Perfmon] Script Date: 2013-05-11 오후 12:12:28 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 2013-05-11 오후 12:12:28 ******/
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] PowerShell Perfmon',
@enabled=0,
@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-11 오후 12:12:29 ******/
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'PowerShell',
@command=N'function getPerfmonData
{
param(
[string]$server,
[string]$instance
)
[Microsoft.PowerShell.Commands.GetCounter.PerformanceCounterSampleSet]$collections
$monitorServer = "localhost"
$monitorDB = "DBA"
$counters = @(
"\Processor(_Total)\% Processor Time"
)
$collections = Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 1
$sampling = $collections.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue
$xmlString = $sampling | ConvertTo-Xml -As String
$query = "dbo.AP_PowerShell_PerfCounter_I ''$server'', ''$instance'', ''$xmlString'';"
Invoke-Sqlcmd -Database $monitorDB -Query $query
}
getPerfmonData -server "localhost" -instance "mssqlserver"
',
@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_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=20130503,
@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
2013/05/09 - [Performance] - CLR로 Performance Counter를 DB에 저장하기
2013/05/06 - [Performance] - VB+WMI 로 Performance counter 를 DB에 저장하기
'Performance' 카테고리의 다른 글
| CLR로 Performance Counter를 DB에 저장하기 (0) | 2013.05.09 |
|---|---|
| VB+WMI 로 Performance counter 를 DB에 저장하기 (0) | 2013.05.06 |
