The one and only important factor in monitoring is data. How much of x and how little of y? If you don’t know these values then you’re shooting in the dark. Microsoft has done a great job adding critical data points over the years; but, what happens when the data you need doesn’t exist?

You create your own! Or you simply realize that you never really needed the data that you thought you did; but, that’s a completely different topic.

Creating your own counter

Microsoft has plenty of metrics to monitor: Disk, Memory, CPU, Network, SQL, and much more. This is all fine and dandy but what about application specific counters? Let’s say you want to create a counter for how many users are doing something within your application. You’ve got a couple options and possibly more.

The first that comes to mind is adding a counter in your application. Here’s a good write up from Microsoft on that: https://msdn.microsoft.com/en-us/library/5e3s61wf(v=vs.90).aspx

Now let’s assume that the application team is too busy to add this bit of code and get custom counters in place. What now?

Another option comes to mind. As far back as SQL 2000 and possibly earlier, Microsoft included 10 stored procedures that you can use to accomplish such a task. It won’t be as clean as including your own counters in the application but will get the job done.

sp_user_counter[n]

Each of the stored procedures named sp_user_counter1 through sp_user_counter10 control a corresponding counter in Perfmon. You can find these in the User Settable object for your instance. Default instances will be as shown below:

Here’s a quick example of using these counters:

EXEC master.dbo.sp_user_counter1 5

results in the associated counter increasing to 5:

The real world

Whether you’re just now finding out about these or you’ve been reminded that these exist, I’m sure your asking: “How can I use them?”

Some many years ago, I used the procedures in SQL 2000 to monitor blocked processes. I was onsite at a customer in San Jose, Costa Rica and I figured pictures would help bridge the gap since I didn’t speak Spanish.

The customer was experiencing outages that they could see with blocking. While investigating I found that blocking was directly related to disk latency. More Latency = More Blocking.

Here we can see User Counter 1 being used to hold the number of blocked processes and the correlation between the latency.

A counter for blocked processes was added in SQL Server 2005 and I haven’t had a lot of use for these until last week.

I noticed that there seems to be no counter for Runnable Tasks. I thought, easy enough. Simply add sp_user_counter1 to an Agent job and execute it every 5 seconds, setting the counter equal to the current number of runnable tasks. This allowed me to go back and look at the customer’s load test data in greater detail after the test was complete. On a side note, this customer was constrained by CPU and had a very high load factor for each scheduler during their load tests.

What’s great about these counters is that you can easily add any metric you want to see using this same technique; though, I want to reiterate that this isn’t the cleanest way to go about it. When in a pinch, this works well. For long term monitoring, add your custom counter.

Summary

Custom counters can be created to monitor virtually anything you want. It’s best to add the counters in your application and give them a meaningful name; but, the user configurable counters in SQL may help in a pinch.