How to Programmatically Capture SQL Server Performance Metrics
Automating the Capturing of Performance Metrics:
USE DBA;
GO
CREATE PROC GetMetrics
AS
SET NOCOUNT ON;
-- Variables for Counters
DECLARE @BatchRequestsPerSecond BIGINT;
DECLARE @CompilationsPerSecond BIGINT;
DECLARE @ReCompilationsPerSecond BIGINT;
DECLARE @LockWaitsPerSecond BIGINT;
DECLARE @PageSplitsPerSecond BIGINT;
DECLARE @CheckpointPagesPerSecond BIGINT;
-- Variable for date
DECLARE @stat_date DATETIME;
-- Table for First Sample
DECLARE @RatioStatsX TAbLE(
[object_name] varchar(128)
,[counter_name] varchar(128)
,[instance_name] varchar(128)
,[cntr_value] bigint
,[cntr_type] int
)
-- Table for Second Sample
DECLARE @RatioStatsY TABLE(
[object_name] VARCHAR(128)
,[counter_name] VARCHAR(128)
,[instance_name] VARCHAR(128)
,[cntr_value] BIGINT
,[cntr_type] INT
);
-- Capture stat time
SET @stat_date = getdate();
INSERT INTO @RatioStatsX (
[object_name]
,[counter_name]
,[instance_name]
,[cntr_value]
,[cntr_type] )
SELECT [object_name]
,[counter_name]
,[instance_name]
,[cntr_value]
,[cntr_type] FROM sys.dm_os_performance_counters;
-- Capture each per second counter for first sampling
SELECT TOP 1 @BatchRequestsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Batch Requests/sec'
AND object_name LIKE '%SQL Statistics%';
SELECT TOP 1 @CompilationsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'SQL Compilations/sec'
AND object_name LIKE '%SQL Statistics%';
SELECT TOP 1 @ReCompilationsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'SQL Re-Compilations/sec'
AND object_name LIKE '%SQL Statistics%';
SELECT TOP 1 @LockWaitsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Lock Waits/sec'
AND instance_name = '_Total'
AND object_name LIKE '%Locks%';
SELECT TOP 1 @PageSplitsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%';
SELECT TOP 1 @CheckpointPagesPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Checkpoint Pages/sec'
AND object_name LIKE '%Buffer Manager%';
WAITFOR DELAY '00:00:01'
-- Table for second sample
INSERT INTO @RatioStatsY (
[object_name]
,[counter_name]
,[instance_name]
,[cntr_value]
,[cntr_type] )
SELECT [object_name]
,[counter_name]
,[instance_name]
,[cntr_value]
,[cntr_type] FROM sys.dm_os_performance_counters
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
,c.cntr_value AS [PageLifeExpectency]
,d.[BatchRequestsPerSecond]
,e.[CompilationsPerSecond]
,f.[ReCompilationsPerSecond]
,g.cntr_value AS [UserConnections]
,h.LockWaitsPerSecond
,i.PageSplitsPerSecond
,j.cntr_value AS [ProcessesBlocked]
,k.CheckpointPagesPerSecond
,GETDATE() AS StatDate
FROM (SELECT * FROM @RatioStatsY
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name LIKE '%Buffer Manager%') a
CROSS JOIN
(SELECT * FROM @RatioStatsY
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name LIKE '%Buffer Manager%') b
CROSS JOIN
(SELECT * FROM @RatioStatsY
WHERE counter_name = 'Page life expectancy '
AND object_name LIKE '%Buffer Manager%') c
CROSS JOIN
(SELECT (cntr_value - @BatchRequestsPerSecond) /
(CASE WHEN datediff(ss,@stat_date, getdate()) = 0
THEN 1
ELSE datediff(ss,@stat_date, getdate()) END) AS [BatchRequestsPerSecond]
FROM @RatioStatsY
WHERE counter_name = 'Batch Requests/sec'
AND object_name LIKE '%SQL Statistics%') d
CROSS JOIN
(SELECT (cntr_value - @CompilationsPerSecond) /
(CASE WHEN datediff(ss,@stat_date, getdate()) = 0
THEN 1
ELSE datediff(ss,@stat_date, getdate()) END) AS [CompilationsPerSecond]
FROM @RatioStatsY
WHERE counter_name = 'SQL Compilations/sec'
AND object_name LIKE '%SQL Statistics%') e
CROSS JOIN
(SELECT (cntr_value - @ReCompilationsPerSecond) /
(CASE WHEN datediff(ss,@stat_date, getdate()) = 0
THEN 1
ELSE datediff(ss,@stat_date, getdate()) END) AS [ReCompilationsPerSecond]
FROM @RatioStatsY
WHERE counter_name = 'SQL Re-Compilations/sec'
AND object_name LIKE '%SQL Statistics%') f
CROSS JOIN
(SELECT * FROM @RatioStatsY
WHERE counter_name = 'User Connections'
AND object_name LIKE '%General Statistics%') g
CROSS JOIN
(SELECT (cntr_value - @LockWaitsPerSecond) /
(CASE WHEN datediff(ss,@stat_date, getdate()) = 0
THEN 1
ELSE datediff(ss,@stat_date, getdate()) END) AS [LockWaitsPerSecond]
FROM @RatioStatsY
WHERE counter_name = 'Lock Waits/sec'
AND instance_name = '_Total'
AND object_name LIKE '%Locks%') h
CROSS JOIN
(SELECT (cntr_value - @PageSplitsPerSecond) /
(CASE WHEN datediff(ss,@stat_date, getdate()) = 0
THEN 1
ELSE datediff(ss,@stat_date, getdate()) END) AS [PageSplitsPerSecond]
FROM @RatioStatsY
WHERE counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%') i
CROSS JOIN
(SELECT * FROM @RatioStatsY
WHERE counter_name = 'Processes blocked'
AND object_name LIKE '%General Statistics%') j
CROSS JOIN
(SELECT (cntr_value - @CheckpointPagesPerSecond) /
(CASE WHEN datediff(ss,@stat_date, getdate()) = 0
THEN 1
ELSE datediff(ss,@stat_date, getdate()) END) AS [CheckpointPagesPerSecond]
FROM @RatioStatsY
WHERE counter_name = 'Checkpoint Pages/sec'
AND object_name LIKE '%Buffer Manager%') k
To complete the automation process I need do two additional things.
One is to create the table that will store these metrics, which can be
accomplished by running the following CREATE TABLE statement. Note this
code again assumes that there is a DBA database where the table will be
created:
USE DBA;
GO
CREATE TABLE [dbo].[PerformanceMetricHistory](
[BufferCacheHitRatio] [numeric](38, 13) NULL,
[PageLifeExpectency] [bigint] NULL,
[BatchRequestsPerSecond] [bigint] NULL,
[CompilationsPerSecond] [bigint] NULL,
[ReCompilationsPerSecond] [bigint] NULL,
[UserConnections] [bigint] NULL,
[LockWaitsPerSecond] [bigint] NULL,
[PageSplitsPerSecond] [bigint] NULL,
[ProcessesBlocked] [bigint] NULL,
[CheckpointPagesPerSecond] [bigint] NULL,
[StatDate] [datetime] NOT NULL
) ON [PRIMARY]
The second thing is to create a SQL Server Agent job that is schedule
to run however often I want to capture these statistics. Below is the
code to create a SQL Server Agent Job name 'Collect Performance Metrics'
that runs every minute:
USE [msdb]
GO
/****** Object: Job [Collect Performance Metrics] Script Date: 07/22/2011 14:01:05 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 07/22/2011 14:01:05 ******/
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'Collect Performance Metrics',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Collect Metrics] Script Date: 07/22/2011 14:01:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Metrics',
@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 EXEC dbo.GetMetrics',
@database_name=N'DBA',
@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'Every Minute',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110722,
@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:
No comments:
Post a Comment