Collecting performance counters and using SQL Server to analyze the data

http://www.mssqltips.com/tip.asp?tip=1722

Problem
Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services.

Solution
The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved.

Select Counters

I first need to collect my data.

I use TypePerf to display a list of performance monitor counters on the machine I wish to collect from. TypePerf is installed by default on all machines after Windows 2000.

I use the following query to get a list of all the counters that can be used on the machine I am monitoring. Although the counters are generally the same they may be different from machine to machine.. This is run in a DOS command window.

typeperf -q

You can return the entire list and store this list in a text file by using the following command.

typeperf -q > counterstxt

Once you have the counters from the machine you want to monitor you need to edit the list to keep only the counters you wish to collect.

I normally collect all counters for the following objects. The * after the counter group specifies that all counters should be collected. After you have the list of counters you want to collect save this as counterstxt. This is a list of counters that were available on the machine that I wanted to analyze again not all of these counters may exist on your machine that you are monitoring.

\.NET CLR Data(*)\SqlClient: (for connection pooling)
\SQLServer:SSIS Pipeline\*
\SQLServer:SSIS Service \*
\MSFTESQLServer\*
\MSOLAPServer\*
\LogicalDisk(*)\*
\Memory\*
\Network Interface(*)\*
\Paging File(*)\*
\PhysicalDisk(*)\*
\Process(*)\*
\Processor(*)\*
\Server\*
\SQLServer:Access Methods\*
\SQLServer:Buffer Manager\*
\SQLServer:General Statistics\*
\SQLServer:Latches\*
\SQLServer:Locks(*)\*
\SQLServer:Memory Manager\*
\SQLServer:SQL Statistics\*
\System\*

--------------------------------------------------------------------------------

Collect Data

I then use logman to create my collection

logman create counter MyCollection -s %computername% -cf counterstxt

I then start the collection like this:

logman MyCollection start

Once I have collected a representative sample I stop the collection as follows:

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 (blg is the extension for perfmon counters). On Windows 2000, 2003 and XP machines they will be stored by default in %systemdrive%\PerfLogs.


--------------------------------------------------------------------------------

Load to SQL Server

Now that I have collected my perfmon counters I am ready to push them into SQL Server. To do this I use relog.

To use relog to input your performance monitor counters into SQL Server you must first select a database you wish to push them into and then create a system DSN to this SQL Server database (any version of SQL Server will work from SQL 2000 to SQL 2008). I use Windows Authentication as I don’t want to worry about saving the account and password in the DSN.

Open up the Data Sources (ODBC) (In the Control Panel applet in the Administrative Tools section)
Under "User DSN" click "Add" and select SQL Server for your driver and click "Finish"
Give your System DSN a name – I call it "relog", and then point to a SQL Server in the drop down list or type in the server name and click "Next"
Select Windows Authentication (ensure that your windows login has dbo rights in the database that you wish to write your performance monitor counters to). and click "Next"
Select your database from the dropdown and click "Next"
Click "Finish"
Click "Test Data Source..." to test your data source
If the test was successful click "OK" and click "OK" again and then close this applet
Now push your performance monitor counters to your SQL Server database by using the following command. 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.

You will want to run this command in the folder that has the "blg" file that was created or you will need to specify the path too. Also, you need to make sure the filename that was created is what is used for the command.

relog MyCollection.blg -f SQL -o SQL:relog!ServerName

--------------------------------------------------------------------------------

Analyze the Data

Now that the data has been loaded it is time to query the data.

The collection tables that are created when the data is loaded are the following:

DisplayToID - table containing information about your collection
CounterDetails - contains details about your perfmon counters
CounterData - contains the actual counter data
Here is a sample query illustrating how to access your perfmon counter data. Here we are looking for context switches (Context Switches/sec). This will group the data in one minute intervals.

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
WHERE CounterName = 'Context Switches/sec'
GROUP BY MachineName,
CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime))