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
CREATE PROCEDURE sp_increment_counter1
	@value INT
	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

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


  1. […] 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. Michael,

    SQL Server version of the etch-a-sketch! Thanks for the tip.

    Comment by Dave Schutz — August 10, 2010 @ 10:01 am

  7. […] 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

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress