I would like to log data from a given set of Windows performance counters directly to tables SQL Server. Does Windows provide a way to do this without writing a custom application to do so?

If so, are there any serious issues with doing so?

there is no built-in way in Windows to write the PerfCounters to a SQL Database. You must code a tool which does this.

Yes, it is possible. Here's a resume based on this article.

Select the counters you want to analyze

Export all counters on local machine to a local folder.

typeperf -q  > counters.txt

Edit the file exported and leave only the counters you want to sample. I left the two below:

\Processor(*)\% Processor Time
\Memory\Pages/sec

Collect Data

Here you will collect the data you want to analyze using logman.

Create a collection.

logman create counter MyCollection -s %computername% -cf counters.txt

Start Logging

logman MyCollection start

Once you have collected a representative sample, stop coolection.

logman MyCollection stop

By default on Vista and Windows 2008 servers, your performance monitor counters will be stored in %systemdrive%\PerfLogs\Admin and will be named after your collection name (in our case they will be called MyCollection.blg So, you need to go to this folder.

cd %systemdrive%\PerfLogs\Admin

Load to SQL Server

Create a ODBC datasource. Warning: SQL Server Native Client cannot be used with perfmon. Perfmon is a Windows tool that can only be used with DSNs that use the MDAC SQLODBC driver included with Windows.

I created an ODBC datasource named relogDSN. ServerName is the name of the server which I collected the data on. This name will be written to the SQL Server table DisplayToID that is created and I can query on it when I want to look at my counters.

relog MyCollection_000001.blg -f SQL -o SQL:relogDSN!ServerName

Analyzing Data

Your counters will be saved to the tables CounterDetails, CounterData and DisplayToID, here's a SQL query as an example.

SELECT MachineName,
   CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime)) as [Date],
   AVG(CounterValue) as Average,
   MIN(CounterValue) as Minimum,
   MAX(CounterValue) as Maximum
FROM CounterDetails
   JOIN CounterData ON CounterData.CounterID = CounterDetails.CounterID
   JOIN DisplayToID ON DisplayToID.GUID = CounterData.GUID
GROUP BY MachineName,
   CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime)) 
  • 2
    Can you summarize the information from the link in your answer in case the page disappears? – jonsca Nov 27 '13 at 2:28

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.