Home / server / Questions / 126689
In Process
Michael Stum
Asked: 2010-03-27 10:20:25 +0800 CST

Free tool to write Performance Counters to MS SQL Database?

  • 772

Is there some (free) tool that reads Performance Counters (Windows 2008 Server, but 2003 support is a bonus) and adds them to a MS SQL Server 2008 database?

Doesn't need to be too fancy as in the worst case I can add "log rotation" through triggers.

Edit: To clarify, I want to read any Performance Counter (CPU Load, Disk Queue, ASP.net stuff) and merely use SQL Server as a datastore. Kinda like how the built-in CSV Logging works.

windows-server-2008 sql-server windows-server-2003 monitoring

3 Answers

  1. logman.exe and relog.exe, built-in tools from Windows OS. See Description of the Windows XP Logman.exe, Relog.exe, and Typeperf.exe Tools.

    A short example at Relogging Perfmon binary log files to SQL

    It's true that in theory you can collect the samples straight to SQL with logman, in practice is better to collect to binary and then convert them to SQL with relog.

    • 2

  2. So actually you want it as cleartext. Perfmon can log directly to sql server but it creates it's own schema and tables. It might be worth while to taker al ook at the schema and develop your own views to add to it because then any counter you add will auto generate whatever calculations you want to do on the metric. The Schema can be found here. If you use relog you'll want to use this article to convert the dates/times

    Here is a sample query to return min max and average grouped by 30 min. intervals:

    SELECT t2.objectname, t2.countername, 
  DATEADD( minute, 
           (DATEPART(minute,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME))/30)*30,
                 DATEADD(hour,DATEDIFF(hour,0,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME)),0)) AS Period,
  AVG(t1.countervalue) AS [Avg], 
  MIN(t1.countervalue) AS [Min],  
  MAX(t1.countervalue) AS [Max]
FROM counterdata t1 INNER JOIN counterdetails t2 ON t2.counterid=t1.counterid
WHERE t2.countername = '% Processor Time' AND t2.instancename = '_Total'
AND t2.objectname = 'Processor'
GROUP BY DATEADD( minute, (DATEPART(minute,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME))/30)*30,
                  DATEADD(hour,DATEDIFF(hour,0,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME)),0)),
  t2.objectname, t2.countername
ORDER BY Period
    • 2

  3. I know this is old, but I don't see any answer marked as accepted, so I'll add my two cents...

    I was looking for a similar thing and came across a tool called PolyMon (http://polymon.codeplex.com/). The actual performance counters are gathered by a Windows service and then stashed into SQL Server. The management UI is a kind of clunky, but it works and I've set up about 60 counters so far.

    • 1