Michael J. Swart

July 29, 2010

Tricks with User Settable Perfmon Counters

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 3:52 pm

So there’s this underrated SQL Server feature called User Settable Counters and they help SQL Server developers create our own custom perfmon counters.

You use them by using syntax like the following:

EXEC sp_user_counter1 @newvalue

This updates a counter than can be tracked using windows performance monitor etc…

Trick 1

Unfortunately this is not a counter that can be incremented. You have to know the new value to set. It would be great if we had stored procedures  like sp_increment_counterX so that we could simply increment the existing value of the counter by any given value. Well ask and you shall receive!

USE master
GO
 
CREATE PROCEDURE sp_increment_counter1
	@value INT
AS
 
	DECLARE @newValue INT
	SELECT @newValue = @value + cntr_value
	FROM sys.dm_os_performance_counters
	WHERE instance_name = 'User counter 1'
	and [object_name] like '%User Settable%'
	and counter_name = 'Query'
 
	EXEC sp_user_counter1 @newValue
GO

I like this stored procedure because it’s very light weight:

  • The cpu/IO/memory is negligible. Update Feb 8, 2011: Woah! On a performance test, this sproc is seen to take a lot of cpu!
  • It doesn’t take or hold any significant locks.

Trick 2

If you have a lot of spare time, you can the commands sp_user_counter combined with WAITFOR to make some pretty pictures of your favorite skyline!

Toronto Skyline

Like the Toronto Skyline

Based on this this photo from BriYYZ (at Flickr):

Toronto skyline

Toronto skyline

Powered by WordPress