Perfmon – store counter data in SQL


In my previous two post I tried to categorize performance counters for OS and SQL Server and how these performance counters could be collected using Performance Monitor and collector sets. The next step is to put these counter values on regular basis to a database.

When storing the counter values into a SQL database we can use select and aggregate values in a better fashion, even create a data warehouse where fast reports can be presented on the state of the server performance.

When you have one or more BLG files stored on disk, you can use the relog utility to store all counter definitions and values into a 32-bit ODBC data source. An example for the syntax is shown below:

relog MyCounterData.blg -f SQL -o SQL:MyOdbc!MyServer

This command will take all performance counter values from the file MyCounterData.blg and put these into a database where connection parameters are defined in the ODBC DSN MyOdbc running on the server MyServer. The database must be created before the command can be ran, but the relog will create the needed tables for storing the counter data if it has necessary rights.

The output will look something like:

Input
----------------
File(s):
     MyCounterData.blg (Binary)

Begin:    23.5.2013 15:29:36
End:      23.5.2013 15:55:21
Samples:  104

100.00%

Output
----------------
File:     SQL:MyOdbc!MyServer

Begin:    23.5.2013 15:29:36
End:      23.5.2013 15:55:21
Samples:  104

The command completed successfully.

If your user defined in the ODBC DSN has sufficent access right, the following tables will be created (slightly changed for better readablility):

CREATE TABLE [dbo].[DisplayToID](
	[GUID] [uniqueidentifier] NOT NULL,
	[RunID] [int] NULL,
	[DisplayString] [varchar](1024) NOT NULL,
	[LogStartTime] [char](24) NULL,
	[LogStopTime] [char](24) NULL,
	[NumberOfRecords] [int] NULL,
	[MinutesToUTC] [int] NULL,
	[TimeZoneName] [char](32) NULL,
)

CREATE TABLE [dbo].[CounterDetails](
	[CounterID] [int] IDENTITY(1,1) NOT NULL,
	[MachineName] [varchar](1024) NOT NULL,
	[ObjectName] [varchar](1024) NOT NULL,
	[CounterName] [varchar](1024) NOT NULL,
	[CounterType] [int] NOT NULL,
	[DefaultScale] [int] NOT NULL,
	[InstanceName] [varchar](1024) NULL,
	[InstanceIndex] [int] NULL,
	[ParentName] [varchar](1024) NULL,
	[ParentObjectID] [int] NULL,
	[TimeBaseA] [int] NULL,
	[TimeBaseB] [int] NULL,
)

CREATE TABLE [dbo].[CounterData](
	[GUID] [uniqueidentifier] NOT NULL,
	[CounterID] [int] NOT NULL,
	[RecordIndex] [int] NOT NULL,
	[CounterDateTime] [char](24) NOT NULL,
	[CounterValue] [float] NOT NULL,
	[FirstValueA] [int] NULL,
	[FirstValueB] [int] NULL,
	[SecondValueA] [int] NULL,
	[SecondValueB] [int] NULL,
	[MultiCount] [int] NULL,
)

This should be enough details for this time, but I will most probably come back later with refined approaches.

Happy debugging and monitoring….

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s