Monitoring Process for Performance Counters
Filed under: Performance & Tuning, Syndication, T-SQL Scripts
Recently I needed to create a process to monitor performance counters over a short period of time. We were going to implement a change and we wanted to compare performance before and after to see if there was any impact.
To do this, I first created a couple of tables. One table is used to actually store the monitored values. The second table is used for configuration; you insert only the counters you want to monitor.
/* Create the table to store our logged perfmon counters */ CREATE TABLE dbo.dba_perfCounterMonitor ( capture_id INT IDENTITY(1,1) Not Null , captureDate SMALLDATETIME Not Null , objectName NVARCHAR(128) Not Null , counterName NVARCHAR(128) Not Null , instanceName NVARCHAR(128) Not Null , VALUE FLOAT(6) Not Null , valueType NVARCHAR(10) Not Null CONSTRAINT PK_dba_perfCounterMonitor PRIMARY KEY CLUSTERED(capture_id) ); /* Create the table that controls which counters we're going to monitor */ CREATE TABLE dbo.dba_perfCounterMonitorConfig ( objectName NVARCHAR(128) Not Null , counterName NVARCHAR(128) Not Null , instanceName NVARCHAR(128) Null );
If you leave the instanceName NULL in the config table, it’ll monitor all instances. Now we’re going to insert some sample performance counters into the config table. The counters you’re interested in can, and likely will, vary.
/* Insert some perfmon counters to be monitored */ INSERT INTO dbo.dba_perfCounterMonitorConfig SELECT 'SQLServer:Buffer Manager', 'Page Life Expectancy', Null UNION All SELECT 'SQLServer:Locks', 'Lock Requests/sec', Null UNION All SELECT 'SQLServer:Locks', 'Lock Waits/sec', Null UNION All SELECT 'SQLServer:Locks', 'Lock Wait Time (ms)', Null UNION All SELECT 'SQLServer:Buffer Manager', 'Page reads/sec', Null UNION All SELECT 'SQLServer:Buffer Manager', 'Page writes/sec', Null UNION All SELECT 'SQLServer:Buffer Manager', 'Buffer cache hit ratio', Null UNION All SELECT 'SQLServer:Databases', 'Transactions/sec', 'AdventureWorks' UNION All SELECT 'SQLServer:General Statistics', 'Processes blocked', Null;
Now let’s create our proc. This proc will run for a specified time period and will *average* the counters over that time. I personally take snapshots every 15 seconds for 4 minutes; I have a scheduled task that runs this every 5 minutes. It’s not perfect, but it gives me a good idea of what’s happening on the server.
CREATE PROCEDURE dbo.dba_perfCounterMonitor_sp /* Declare Parameters */ @samplePeriod INT = 240 /* how long to sample, in seconds */ , @sampleRate CHAR(8) = '00:00:15' /* how frequently to sample, in seconds */ , @displayResults BIT = 0 /* display the results when done */ AS /********************************************************************************* Name: dba_perfCounterMonitor_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Monitors performance counters. Uses the dba_perfCounterMonitorConfig table to manage which perf counters to monitor. @samplePeriod - specifies how long the process will try to monitor performance counters; in seconds. @sampleRate - how long inbetween samples; in seconds. The average values over sample period is then logged to the dba_perfCounterMonitor table. Notes: There are 3 basic types of performance counter calculations: Value/Base: these calculations require 2 counters. The value counter (cntr_type = 537003264) has to be divided by the base counter (cntr_type = 1073939712). Per Second: these counters are store cumulative values; the value must be compared at 2 different times to calculate the difference (cntr_type = 537003264). Point In Time: these counters show what the value of the counter is at the current point-in-time (cntr_type = 65792). No calculation is necessary to derive the value. Called by: DBA Date User Description ---------------------------------------------------------------------------- 2009-09-04 MFU Initial Release ********************************************************************************* Exec dbo.dba_perfCounterMonitor_sp @samplePeriod = 60 , @sampleRate = '00:00:01' , @displayResults = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; BEGIN /* Declare Variables */ DECLARE @startTime DATETIME , @endTime DATETIME , @iteration INT; SELECT @startTime = GETDATE() , @iteration = 1; DECLARE @samples TABLE ( iteration INT Not Null , objectName NVARCHAR(128) Not Null , counterName NVARCHAR(128) Not Null , instanceName NVARCHAR(128) Not Null , cntr_value FLOAT Not Null , base_value FLOAT Null , cntr_type BIGINT Not Null ); BEGIN Try /* Start a new transaction */ BEGIN TRANSACTION; /* Grab all of our counters */ INSERT INTO @samples SELECT @iteration , RTRIM(dopc.OBJECT_NAME) , RTRIM(dopc.counter_name) , RTRIM(dopc.instance_name) , RTRIM(dopc.cntr_value) , (SELECT cntr_value FROM sys.dm_os_performance_counters AS dopc1 WHERE dopc1.OBJECT_NAME = pcml.objectName And dopc1.counter_name = pcml.counterName + ' base' And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name)) , dopc.cntr_type FROM sys.dm_os_performance_counters AS dopc Join dbo.dba_perfCounterMonitorConfig AS pcml ON dopc.OBJECT_NAME = pcml.objectName And dopc.counter_name = pcml.counterName And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name); /* During our sample period, grab our counter values and store the results */ WHILE GETDATE() < DATEADD(SECOND, @samplePeriod, @startTime) BEGIN SET @iteration = @iteration + 1; INSERT INTO @samples SELECT @iteration , RTRIM(dopc.OBJECT_NAME) , RTRIM(dopc.counter_name) , RTRIM(dopc.instance_name) , dopc.cntr_value , (SELECT cntr_value FROM sys.dm_os_performance_counters AS dopc1 WHERE dopc1.OBJECT_NAME = pcml.objectName And dopc1.counter_name = pcml.counterName + ' base' And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name)) , dopc.cntr_type FROM sys.dm_os_performance_counters AS dopc Join dbo.dba_perfCounterMonitorConfig AS pcml ON dopc.OBJECT_NAME = pcml.objectName And dopc.counter_name = pcml.counterName And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name); /* Wait for a small delay */ WAITFOR Delay @sampleRate; END; /* Grab our end time for calculations */ SET @endTime = GETDATE(); /* Store the average of our point-in-time counters */ INSERT INTO dbo.dba_perfCounterMonitor ( captureDate , objectName , counterName , instanceName , VALUE , valueType ) SELECT @startTime , objectName , counterName , instanceName , AVG(cntr_value) , 'value' FROM @samples WHERE cntr_type = 65792 GROUP BY objectName , counterName , instanceName; /* Store the average of the value vs the base for cntr_type = 537003264 */ INSERT INTO dbo.dba_perfCounterMonitor ( captureDate , objectName , counterName , instanceName , VALUE , valueType ) SELECT @startTime , objectName , counterName , instanceName , AVG(cntr_value)/AVG(IsNull(base_value, 1)) , 'percent' FROM @samples WHERE cntr_type = 537003264 GROUP BY objectName , counterName , instanceName; /* Compare the first and last values for our cumulative, per-second counters */ INSERT INTO dbo.dba_perfCounterMonitor ( captureDate , objectName , counterName , instanceName , VALUE , valueType ) SELECT @startTime , objectName , counterName , instanceName , (MAX(cntr_value) - MIN(cntr_value)) / DATEDIFF(SECOND, @startTime, @endTime) , 'value' FROM @samples WHERE cntr_type = 272696576 GROUP BY objectName , counterName , instanceName; /* Should we display the results of our most recent execution? */ IF @displayResults = 1 SELECT captureDate , objectName , counterName , instanceName , VALUE , valueType FROM dbo.dba_perfCounterMonitor WITH (NoLock) WHERE captureDate = CAST(@startTime AS SMALLDATETIME) ORDER BY objectName , counterName , instanceName; /* If you have an open transaction, commit it */ IF @@TRANCOUNT > 0 COMMIT TRANSACTION; END Try BEGIN Catch /* Whoops, there was an error... rollback! */ IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; /* Return an error message and log it */ EXECUTE dbo.dba_logError_sp; END Catch; SET NOCOUNT OFF; RETURN 0; END Go
Like I said, it’s not perfect, but it gets the job done.
Getting an error about dba_logError_sp? Take a look at my error handling proc.
Replication Monitor
In my last blog post, I provided a script to view replication latency. Ian Kirk took the script and ran with it, adding centralized execution and permanent logging. I’ve tweaked it a little bit further and deployed to production. So far, so good.
Here’s the latest and greatest for those interested:
IF OBJECT_ID('dbo.dba_replicationMonitor') IS Null BEGIN CREATE TABLE dbo.dba_replicationMonitor ( monitor_id INT IDENTITY(1,1) Not Null , monitorDate SMALLDATETIME Not Null , publicationName sysname Not Null , publicationDB sysname Not Null , iteration INT Null , tracer_id INT Null , distributor_latency INT Null , subscriber VARCHAR(1000) Null , subscriber_db VARCHAR(1000) Null , subscriber_latency INT Null , overall_latency INT Null ); END; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_replicationLatencyMonitor_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_replicationLatencyMonitor_sp; PRINT 'Procedure dba_replicationLatencyMonitor_sp dropped'; END; Go SET Quoted_Identifier ON Go SET ANSI_Nulls ON Go CREATE PROCEDURE dbo.dba_replicationLatencyMonitor_sp /* Declare Parameters */ @publicationToTest sysname = N'yourPublicationName' , @publicationDB sysname = N'yourPublicationDB' , @replicationDelay VARCHAR(10) = N'00:00:30' , @iterations INT = 5 , @iterationDelay VARCHAR(10) = N'00:00:30' , @displayResults BIT = 0 , @deleteTokens BIT = 1 AS /********************************************************************************* Name: dba_replicationLatencyMonitor_sp Author: Michelle F. Ufford Purpose: Retrieves the amount of replication latency in seconds Notes: Default settings will run 1 test every minute for 5 minutes. @publicationToTest = defaults to yourPublicationName publication @publicationDB = the database that is the source for the publication. The tracer procs are found in the publishing DB. @replicationDelay = how long to wait for the token to replicate; probably should not set to anything less than 10 (in seconds) @iterations = how many tokens you want to test @iterationDelay = how long to wait between sending test tokens (in seconds) @displayResults = print results to screen when complete @deleteTokens = whether you want to retain tokens when done Called by: DBA Date Initials Description ---------------------------------------------------------------------------- 2008-11-20 MFU Initial Release 2008-11-24 ILK Tweaked to allow for centralized execution Replaced temp table with permanent table. 2008-11-25 MFU More tweaking, added publication data to dba_replicationMonitor, fixed NULL latency data, moved dba_replicationMonitor creation out of proc ********************************************************************************* Exec dbo.dba_replicationLatencyMonitor_sp @publicationToTest = N'myTestPublication' , @publicationDB = N'sandbox_publisher' , @replicationDelay = N'00:00:05' , @iterations = 1 , @iterationDelay = N'00:00:05' , @displayResults = 1 , @deleteTokens = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare Variables */ DECLARE @currentIteration INT , @tokenID BIGINT , @currentDateTime SMALLDATETIME , @sqlStatement NVARCHAR(200) , @parmDefinition NVARCHAR(500); DECLARE @tokenResults TABLE ( iteration INT Null , tracer_id INT Null , distributor_latency INT Null , subscriber VARCHAR(1000) Null , subscriber_db VARCHAR(1000) Null , subscriber_latency INT Null , overall_latency INT Null ); /* Initialize our variables */ SELECT @currentIteration = 0 , @currentDateTime = GETDATE(); WHILE @currentIteration < @iterations BEGIN /* Prepare the stored procedure execution string */ SET @sqlStatement = N'Execute ' + @publicationDB + N'.sys.sp_postTracerToken ' + N'@publication = @VARpublicationToTest , ' + N'@tracer_token_id = @VARtokenID OutPut;' /* Define the parameters used by the sp_ExecuteSQL later */ SET @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARtokenID bigint OutPut'; /* Insert a new tracer token in the publication database */ EXECUTE SP_EXECUTESQL @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARtokenID = @TokenID OUTPUT; /* Give a few seconds to allow the record to reach the subscriber */ WAITFOR Delay @replicationDelay; /* Prepare our statement to retrieve tracer token data */ SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_helpTracerTokenHistory ' + N'@publication = @VARpublicationToTest , ' + N'@tracer_id = @VARtokenID' , @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARtokenID bigint'; /* Store our results for retrieval later */ INSERT INTO @tokenResults ( distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) EXECUTE SP_EXECUTESQL @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARtokenID = @TokenID; /* Assign the iteration and token id to the results for easier investigation */ UPDATE @tokenResults SET iteration = @currentIteration + 1 , tracer_id = @tokenID WHERE iteration IS Null; /* Wait for the specified time period before creating another token */ WAITFOR Delay @iterationDelay; /* Avoid endless looping... :) */ SET @currentIteration = @currentIteration + 1; END; /* Display our results */ IF @displayResults = 1 BEGIN SELECT iteration , tracer_id , IsNull(distributor_latency, 0) AS 'distributor_latency' , subscriber , subscriber_db , IsNull(subscriber_latency, 0) AS 'subscriber_latency' , IsNull(overall_latency, IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0)) AS 'overall_latency' FROM @tokenResults; END; /* Store our results */ INSERT INTO dbo.dba_replicationMonitor ( monitorDate , publicationName , publicationDB , iteration , tracer_id , distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) SELECT @currentDateTime , @publicationToTest , @publicationDB , iteration , tracer_id , IsNull(distributor_latency, 0) , subscriber , subscriber_db , IsNull(subscriber_latency, 0) , IsNull(overall_latency, IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0)) FROM @tokenResults; /* Delete the tracer tokens if requested */ IF @deleteTokens = 1 BEGIN SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_deleteTracerTokenHistory ' + N'@publication = @VARpublicationToTest , ' + N'@cutoff_date = @VARcurrentDateTime' , @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARcurrentDateTime datetime'; EXECUTE SP_EXECUTESQL @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARcurrentDateTime = @currentDateTime; END; SET NOCOUNT OFF; RETURN 0; END Go SET Quoted_Identifier OFF; Go SET ANSI_Nulls ON; Go
Note: All of my stored procedures have standardized error handling that I remove before posting to avoid confusion; you may want to implement your own error handling.
Checking Replication Latency with T-SQL
This post may only appeal to small subset of DBA’s.
This particular script was born of a need to view replication latency in production. I had to investigate whether the replication latency issues we were experiencing in production were occurring on the publisher (us) or the subscriber (them), and address accordingly. “Why just not use Replication Monitor?”, you might ask. Good question! I would definitely use Replication Monitor if it were available to me; however, I do not have the necessary permissions to the Distributor.
Fortunately, SQL 2005 provides us with some tools to help: sp_postTracerToken and sp_helpTracerTokenHistory. This allows us to access the same Tracer Tokens feature that is in Replication Monitor.
So that’s the backstory, and here’s the script I came up with. For those interested, it’d be pretty easy to modify this script to use a permanent table and regularly log replication latency.
CREATE PROCEDURE dbo.dba_replicationLatencyGet_sp /* Declare Parameters */ @publicationToTest sysname = N'yourPublicationName' , @replicationDelay VARCHAR(10) = N'00:00:30' , @iterations INT = 5 , @iterationDelay VARCHAR(10) = N'00:00:30' , @deleteTokens BIT = 1 , @deleteTempTable BIT = 1 AS /********************************************************************************* Name: dba_replicationLatencyGet_sp Author: Michelle F. Ufford Purpose: Retrieves the amount of replication latency in seconds Notes: Default settings will run 1 test every minute for 5 minutes. @publicationToTest = change the default to your publication @replicationDelay = how long to wait for the token to replicate; probably should not set to anything less than 10 (in seconds) @iterations = how many tokens you want to test @iterationDelay = how long to wait between sending test tokens (in seconds) @deleteTokens = whether you want to retain tokens when done @deleteTempTable = whether or not to retain the temporary table when done. Data stored to ##tokenResults; set @deleteTempTable flag to 0 if you do not want to delete when done. Called by: DBA Date Initials Description ---------------------------------------------------------------------------- 2008-11-20 MFU Initial Release ********************************************************************************* Exec dbo.dba_replicationLatencyGet_sp @publicationToTest = N'yourPublicationName' , @replicationDelay = N'00:00:05' , @iterations = 1 , @iterationDelay = N'00:00:05' , @deleteTokens = 1 , @deleteTempTable = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare Variables */ DECLARE @currentIteration INT , @tokenID BIGINT , @currentDateTime SMALLDATETIME; IF OBJECT_ID('tempdb.dbo.##tokenResults') IS Null BEGIN CREATE TABLE ##tokenResults ( iteration INT Null , tracer_id INT Null , distributor_latency INT Null , subscriber VARCHAR(1000) Null , subscriber_db VARCHAR(1000) Null , subscriber_latency INT Null , overall_latency INT Null ); END; /* Initialize our variables */ SELECT @currentIteration = 0 , @currentDateTime = GETDATE(); WHILE @currentIteration < @iterations BEGIN /* Insert a new tracer token in the publication database */ EXECUTE sys.sp_postTracerToken @publication = @publicationToTest, @tracer_token_id = @tokenID OUTPUT; /* Give a few seconds to allow the record to reach the subscriber */ WAITFOR Delay @replicationDelay; /* Store our results in a temp table for retrieval later */ INSERT INTO ##tokenResults ( distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) EXECUTE sys.sp_helpTracerTokenHistory @publicationToTest, @tokenID; /* Assign the iteration and token id to the results for easier investigation */ UPDATE ##tokenResults SET iteration = @currentIteration + 1 , tracer_id = @tokenID WHERE iteration IS Null; /* Wait for the specified time period before creating another token */ WAITFOR Delay @iterationDelay; /* Avoid endless looping... :) */ SET @currentIteration = @currentIteration + 1; END; SELECT * FROM ##tokenResults; IF @deleteTempTable = 1 BEGIN DROP TABLE ##tokenResults; END; IF @deleteTokens = 1 BEGIN EXECUTE sp_deleteTracerTokenHistory @publication = @publicationToTest, @cutoff_date = @currentDateTime; END; SET NOCOUNT OFF; RETURN 0; END Go





