Querying Perfmon data from SQL

In a previous post I talked about Relogging Perfmon binary log files to SQL. The next obvious step is to query the data from the database.

There are two ways to do this:

1. Use Perfmon itself to connect to the SQL data source
2. Use another tool to query the SQL data source directly

1. Using Performance Monitor

Once you open Performance Monitor, press the View Log Data button (Ctrl-L):

Then on the Source tab, if you have the ODBC System DSN configured to connect to your performance database, it will show up.

This allows you to view the log files directly from SQL, just as if you had loaded the binary log files.

2. Querying SQL directly

At first glance, the schema doesn’t seem very intuitive. But with a little digging, it’s not too bad.

Whenever you use PerfMon or relog to log to a database, it automatically sets up this schema for you. There are three tables:

1. DisplayToID – This lists all the log sets that are in the database
2. CounterData – This is where the actual counter values are stored
3. CounterDetails – This is where the metadata about each counter / object / instance is stored

Here are some queries that I’ve found useful:

Get all computers that have counter data logged:

SELECT DISTINCT MachineName
FROM CounterDetails
ORDER BY MachineName

Get available object names for a particular computer:

SELECT DISTINCT ObjectName
FROM CounterDetails
WHERE MachineName = ‘\\MYSERVER’
ORDER BY ObjectName

Get counter names for a particular computer and object:

SELECT DISTINCT CounterName
FROM CounterDetails
WHERE MachineName = ‘\\MYSERVER’
AND ObjectName = ‘Processor’
ORDER BY CounterName

Get instance names for a particular computer, object and counter:

SELECT DISTINCT InstanceName
FROM CounterDetails
WHERE MachineName = ‘\\MYSERVER’
AND ObjectName = ‘Processor’
AND CounterName = ‘% Processor Time’
ORDER BY InstanceName

Get counter values for a particular computer, object, counter and instance. Name the column appropriately:

SELECT
    CAST(LEFT(CounterDateTime, 16) as smalldatetime) AS CounterDateTime,
    REPLACE(CounterDetails.MachineName,'\\','') AS ComputerName,
    CounterDetails.ObjectName + ISNULL('(' + CounterDetails.InstanceName + ')','') + '\' + CounterDetails.CounterName AS [Counter],
    CounterData.CounterValue
FROM CounterData
    INNER JOIN CounterDetails ON CounterData.CounterID = CounterDetails.CounterID
    INNER JOIN DisplayToID ON CounterData.GUID = DisplayToID.GUID
WHERE CounterDetails.ObjectName = 'Processor'
    AND    CounterDetails.CounterName = '% Processor Time'
    AND    CounterDetails.MachineName = '\\MYSERVER’
    AND CounterDetails.InstanceName = '_Total'
ORDER BY CounterData.CounterDateTime

There’s a few nuances in the schema which creates complexity in this query. Let me try and explain:

• CounterDateTime is a char(24) instead of a datetime. By trimming it to 16 characters, this allows it to be converted to a smalldatetime at the ‘minutes’ resolution – not seconds.
• MachineName includes ‘\\’, we want to trim it to make it look pretty.
• InstanceName can be null. An example is the Memory\Pages/sec object & counter. The instance value for this combination is null. So we only show the instance name if it’s not null.

Of course now that you have the data being collected, post-processed and queryable in SQL – the next logical step is to make it reportable. Here is a report that I’ve built to display this data. I’ll talk about it more in a future blog post.