July 29, 2010

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!

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.

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