Michael J. Swart

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!

Like the Toronto Skyline

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

Toronto skyline