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
[…] This post was mentioned on Twitter by mjswart, mikesql. mikesql said: RT @MJSwart: Blogged something fun: Tricks with User Settable Perfmon Counters https://michaeljswart.com/?p=800 #perfmon #sql […]
Pingback by Tweets that mention Blogged something fun: Tricks with User Settable Perfmon Counters #perfmon #sql -- Topsy.com — July 29, 2010 @ 5:50 pm
Beautiful and fun, but I think that tall thing looks more like the San Fran Transamerica building….
Comment by Karen Lopez — July 29, 2010 @ 6:00 pm
Hey, that’s exactly what I thought! But I do the best with what I got. 🙂
Comment by Michael J. Swart — July 29, 2010 @ 6:37 pm
Ha ha nice! I’m off to get creative with some pretty PerfMon graphs of my own to help with getting in new hardware 🙂
Comment by John Sansom — July 30, 2010 @ 4:28 am
Very cool John. I just checked out perfmon on Windows Server 2008 R2 and I’m a little disappointed that they didn’t improve it (or improve it more). There’s certainly a lot of room for improvement on that user interface. Especially for such a useful tool.
Comment by Michael J. Swart — July 30, 2010 @ 9:15 am
Michael,
SQL Server version of the etch-a-sketch! Thanks for the tip.
Comment by Dave Schutz — August 10, 2010 @ 10:01 am
[…] you’re comfortable with. One suggestion is to use the user-settable performance counter via sp_increment_counter1 then Poof! You’ve got your own deprecated-features performance counter just like Microsoft. […]
Pingback by Triggers On Views? What For? | Michael J. Swart — October 31, 2012 @ 12:01 pm