I need to create several counters for a system health check and monitoring. Since there are numerous tools for logging, reporting and alerting Windows Perfmon data, I am looking to publish that data as Perfmon counters.
Some of the values need to come from a SQL Server 2008 database, examples of such are the number of records in a table used as a queue, and the age of the oldest record in the table. While it looks like this can be accomplished by using a SQL Server, User Settable Object and the stored procedures sp_user_counter1 to sp_user_counter10 this limits me to only 10 counters per server and the counter names and descriptions cannot be customized to reflect what the counter is.
Without creating our own application to create the Perfmon counters, are there any other ways to create counters in SQL Server? If not, are there any tools/projects that allow for the creation of custom counters using SQL queries?
I think the only way to specifically use SQL server for the custom perfmon data is to use the ten sp_user_counterX stored procs. I did a bit of searching and the only results that came up in my searching were these stored procs.
I do have another suggestion though which requires a bit of coding. This article explains how to create a small application (be it service or console app) that allows you to create as many of your own performance counters as you like. You could use this to create your own counters.
In our organisation we use Idera Diagnostic Manager which we find very useful as we can use the built in metrics to monitor our servers as well as create our own counters. We find that it works very well and although it has quite a hefty price, it is the only monitoring tool that I know of that does not require any sort of agents on the SQL server.