Custom SQL Server performance counters

Question

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?

Answer 1

Late answer - but can be useful to other thread visitors

Besides other mentioned solutions, I can suggest a 3rd party tool called ApexSQL Monitor. It allows the creation of custom SQL performance counters for specific configuration of system on-demand monitoring, historical trending and alerting.

Custom metrics are T-SQL based queries that can be added to this tool. The data collected by those queries can be analyzed and alerted on, just like other built-in metrics.

The following article provides examples of custom SQL performance counters that may be useful: http://solutioncenter.apexsql.com/using-custom-sql-performance-counters-to-monitor-sql-server/