Performance Monitoring Part 7 - Using Performance Monitor with a Database

After a lot of theory about performance monitoring, this article demonstrates how the Windows Performance Monitor can be used to log to a database - even from multiple machines.

Most of you have probably used Performance Monitor before. It allows you to monitor performance metrics (local and remote) in real-time and to log performance data from multiple machines for later analysis. In this article, I will focus on the latter because collecting and analysing performance data from multiple machines is a time consuming task.

For the sake of this article, I assume that your are familiar with Performance Monitor and that you know how to create a data collector set. The following example was built on Windows Server 2008. But earlier instances of Windows do not differ greatly.

How to Log to a Database

It is not very well-known that Performance Monitor is able to log the collected data to a SQL database. There are several advantages to this method:

Let me quickly outline how to log performance data to a SQL database. First, create a database on the SQL server. In my scenario, this database is called PerfMonDB. Next create a system data source pointing to your database - I called this PerfMonDSN.

After these preliminary steps, we can finally configure a new data collector set which I called PerfMonDataSet. The DataCollector01 inside PerfMonDataSet requires the following settings to successfully connect to the database. These are displayed in the two screenshots below.

Configure data collector 1

Configure data collector 2

The settings above result in a unique name identifying every single run of the data collector set.

Database Schema

When Performance Monitor writes to a database, three tables are involved in the process: CounterData, CounterDetails and DisplayToID (see screenshot below).

Explore database schema 1

The table CounterData contains the collected data for the selected performance counters. It also references the other two tables to identify which counter and which data set the data belongs to. The screenshow below shows the relationships between the three tables.

Explore database schema 2

The following screenshot shows the columns of the table DisplayToID and some example data for two runs of the above data collector set. Expect a new line for every execution of every data collector. It identifies the individual runs by the following (selected) fields:

Explore database schema 3

The table CounterDetails contains a description of the performance counters logged in the database. These records are independent of the data set and can be used from several data sets at once. These details contain the following information:

If, for example, you have added the counter Processor(_Total)\% Processor Time this resolves to the following: ObjectName is Processor, CounterName is % Processor Time and InstanceName is _Total.

Explore database schema 4

The last table called CounterData contains the data for all counters collected in individual runs of data collector sets. The following fields are relevant for an analysis:

Explore database schema 5

How to Analyze the Data

In the next part of this series, I will demonstrate how to use Excel to efficiently analyze the contents of the Performance Monitor database.

Feedback is always welcome! If you'd like to get in touch with me concerning the contents of this article, please use Twitter.