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):