SQLStarters

As the summer vacation season is pretty much over, it’s time for me to get back to working and blogging again. I had originally planned to write up a few posts during the summer, but as I spend most of my days writing my bachelor’s thesis, I simply didn’t have the energy to do so. And I did also want to enjoy the Finnish summer with the family as the sunny season here isn’t exactly a long one. And what’s a better way to get started than about blogging about SQL Server and Performance Monitor!

There are literally hundreds of Performance Monitor counters available for SQL Server  that allow you to monitor pretty much every aspect of the database engine, the agent and other related services. While there’s a huge amount of information that you can get with out of the box Performance Monitor counters, they cannot tell you how many rows you inserted into a table during an ETL-process or how many new orders were placed to your system during a day. And that is where the custom Performance Monitor counter comes in. There are two main components that are used for creating your own customized Performance Monitor counter, they are.

• A performance counter called Query under SQL Server:User Settable object.
• A stored procedure called sp_user_counterX (where X is number between 1-10) that is used for setting the value to the counter.

The Query counter has 10 instances that you can monitor (called User counter 1 through User counter 10) and they’ll accept any integer value you  pass through the stored procedure. The counter instances do not auto-refresh like rest of the Performance Monitor counters do, you do need to use sp_user_counterX procedure to do it manually. If you wish to automate the process then you can, for example, create a SQL Server Agent job to run it on whatever schedule you like.

You can easily test how these components work by firing up the Performance Monitor, adding the desired counter instance (User counter 1 in my example) and then executing the stored procedure with whatever value you want:

EXECUTE sp_user_counter1 5;

When you run it multiple times with different values you should see something like this happening.

You can also use variables with sp_user_counterX stored procedure to pass the value to performance monitor counter:

DECLARE @ThisIsMyNumber INT
SET @ThisIsMyNumber = 25;
EXECUTE sp_user_counter1 @ThisIsMyNumber;

I don’t see customizable counters used all that often, even though they’re quick and easy to set up. And with them you’re not limited to monitoring just the system performance but you can also use it to monitor statistics that are vital to your business,  if you can write a query that returns an integer value, that is. And since we’re dealing with Performance Monitor we can take advantage of features like SQL Server Alerts to complete our monitoring solution.

And that’s it for this post, happy customizing!