Monitoring SQL Server, your own way

When you think about monitoring SQL Server instances, you probably often think about monitoring that the service is up, performance, security and such.

But just as important in many cases is to monitor some specific part of you most critical systems. For example, how many rows are in a particular table, if a specific job takes too long to run or if a certain process is blocked. That is something that you cannot do out of the box in most monitoring systems. You could of course write specific scripts for it, depending on what tool you use for monitoring. But there is one way that works with most if not all monitoring tools, and that is to use the user settable performance monitor counters.

You have 10 counters to use, and it’s quite easy. all you do is execute a stored procedure and use the value you want to expose to the performance counter as a parameter:

-- Set the value of user counter 1 to 5
exec sp_user_counter1 5
--The performance counter will now show 5

More information here: http://msdn.microsoft.com/en-US/library/ms187480(v=sql.110).aspx