Tips for DBA: Store Performance Counters in Database (Job-Step: Power Shell)

Остались в прошлом те времена, когда средствами SQL Server можно было получить состояния счётчиков производительности, которые относились только к самому SQL Server. Для этого традиционно использовались системные представления sys.sysperfinfo и sys.dm_os_performance_counters.

С помощью Power Shell и нового в SQL Server 2008 типа шага в заданиях по расписанию, который позволяет запускать под управлением SQL Server Agent сценарии  Power Shell, теперь можно получить абсолютно любые счётчики производительности. Причём, сделать это можно как для локального, так и удалённого в сети сервера. А получение сведений о счётчиках посредством WMI избавляет от необходимости агрегации сырых значений, что делает этот метод простым и понятным.

Ниже представлен макет сценария, который создаёт администраторскую базу данных и в ней таблицу для хранения данных о двух счётчиках производительности: PercentDiskTime и PercentProcessorTime. Данные в эту таблицу поставляет создаваемое в сценарии задание по расписанию, в единственном шаге которого запускается сценарий Power Shel. Это сценарий подключается к указанному серверу, посредством WMI получает значения заданных счётчиков и записывает эти значения в созданную ранее таблицу.

Чтобы протестировать этот метод, в примере сценария замените имена серверов и учётных записей на те, которые действительны в вашей тестовой среде.

USE [master]

GO

CREATE DATABASE [_DBA_]

GO

ALTER DATABASE [_DBA_] SET COMPATIBILITY_LEVEL = 100

GO

USE [_DBA_]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[HostUtilization](

      [countertime] [datetime] NOT NULL,

      [PercentDiskTime] [tinyint] NOT NULL,

      [PercentProcessorTime] [tinyint] NOT NULL,

      [Host] [varchar](30) NOT NULL,

 CONSTRAINT [PK_HostUtilization] PRIMARY KEY CLUSTERED

(

      [Host] ASC,

      [countertime] ASC

))

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_countertime] 

DEFAULT (getdate()) FOR [countertime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_PercentDiskTime] 

DEFAULT ((100)) FOR [PercentDiskTime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_PercentProcessorTime] 

DEFAULT ((100)) FOR [PercentProcessorTime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_Host] 

DEFAULT ('.') FOR [Host]

GO

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job

     @job_name=N'HostUtilization',

     @enabled=1,

     @notify_level_eventlog=2,

     @notify_level_email=0,

     @notify_level_netsend=0,

     @notify_level_page=0,

     @delete_level=0,

     @description=N'Monitoring server name: MICROSOFT'

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

     @owner_login_name=N'RU\AGladchenko',

     @job_id = @jobId OUTPUT

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

     @step_id=1,

     @cmdexec_success_code=0,

     @on_success_action=3,

     @on_success_step_id=0,

     @on_fail_action=3,

     @on_fail_step_id=0,

     @retry_attempts=0,

     @retry_interval=0,

     @os_run_priority=0,

     @subsystem=N'PowerShell',

     @command=N'$RemoteHost = "MICROSOFT"

$PercentDiskTime=(gwmi Win32_PerfFormattedData_PerfDisk_PhysicalDisk -computerName $RemoteHost -filter "name = ''_Total''").PercentDiskTime

$PercentProcessorTime=(gwmi Win32_PerfFormattedData_PerfOS_Processor -computerName $RemoteHost -filter "name = ''_Total''").PercentProcessorTime

$conn = new-object system.data.oledb.oledbconnection

$connstring = "provider=sqloledb;data source=MYSUPERPUPERSERVERNAME;initial catalog=_DBA_;integrated security=SSPI"

$conn.connectionstring = $connstring

$conn.open()

$RemoteHost = "''"+$RemoteHost+"''"

$sqlquery = "INSERT INTO [_DBA_].[dbo].[HostUtilization] ([countertime],[PercentDiskTime],[PercentProcessorTime],[Host] ) VALUES (DEFAULT, $PercentDiskTime, $PercentProcessorTime, $RemoteHost)"

$cmd = New-Object system.data.oledb.oledbcommand

$cmd.connection = $conn

$cmd.commandtext = $sqlquery

$cmd.executenonquery() > NULL

$conn.close()',

     @database_name=N'master',

     @flags=32

EXEC msdb.dbo.sp_update_job

     @job_id = @jobId,

     @start_step_id = 1

EXEC 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=20090206,

     @active_end_date=99991231,

     @active_start_time=0,

     @active_end_time=235959,

     @schedule_uid=N'1b20bdf0-4627-4d31-8036-d85ff30cd664'

 

EXEC msdb.dbo.sp_add_jobserver

     @job_id = @jobId,

     @server_name = N'(local)'

GO