Storing Windows Performance Counter Values in SQL Server

Problem

I capture performance counter values to a CSV file using TYPEPERF.EXE as described in the tip How To Collect Performance Data With TYPEPERF.EXE. I would like to import this information into a SQL Server database. Can you provide an example of how to do this? Read this tip to find out.

Solution

There are a couple of earlier tips that show how to get performance counter values into a SQL Server database:

Collecting performance counters and using SQL Server to analyze the data uses the LOGMAN utility to collect performance counter values and the RELOG utility to import into a SQL Server table using an ODBC connection
Collect and store historical performance counter data using SQL Server DMV sys.dm_os_performance and Collecting SQL Server performance counter data for trending use the sys.dm_os_performance dynamic management view to retrieve the SQL Server-specific performance counter values then use a T-SQL INSERT statement to insert them into a table

Since you asked how to do this with TYPEPERF output and we're talking about transforming and loading data into a SQL Server database, SQL Server Integration Services (SSIS) is a good choice. In this tip I will create an SSIS package to parse the CSV output file and load the values into a table in a SQL Server database. I'll cover the following steps:

Create a list of performance counters to capture
Create a performance counter history table
Create the SSIS package to import the performance counter values into the history table

Create a List of Performance Counters

I covered how to create list of performance counters to capture in the tip How To Collect Performance Data With TYPEPERF.EXE. I like TYPEPERF because it's easy to use and it can output a CSV file which I can open in Excel and do some quick and dirty analysis. Take a look at our earlier tip Creating SQL Server performance based reports using Excel for the details on analyzing performance counters with Excel.

You can use TYPEPERF.EXE to get the available performance counters then simply create a text file with the list of performance counters that you want to capture. Here is a simple example that I will use for this tip:

\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec

To start capturing performance counter values, use the following command:

TYPEPERF -f CSV -o COUNTER_TEST.csv -si 15 -cf COUNTER_TEST.txt -sc 4

The above command will:

Capture the list of performance counters in the COUNTER_TEST.txt text file
Retrieve the performance counter values every 15 seconds 4 times
Output the performance counter values to COUNTER_TEST.csv

The first line of the output CSV file has the column names (shown on separate lines below) where VSSQLDB02 is the name of my server:

"(PDH-CSV 4.0) (Eastern Daylight Time)(240)",
"\\VSSQLDB02\SQLServer:SQL Statistics\Batch Requests/sec",
"\\VSSQLDB02\SQLServer:SQL Statistics\SQL Compilations/sec",
"\\VSSQLDB02\SQLServer:SQL Statistics\SQL Re-Compilations/sec"

The remaining lines of the CSV file are shown below:

"05/14/2011 10:54:57.495"," "," "," "
"05/14/2011 10:55:16.002","858.47871950770968","858.10247664291171","0"
"05/14/2011 10:55:38.133","907.23303676381374","907.05312837320071","0"
"05/14/2011 10:55:56.219","924.5109440561165","924.23584111495836","0"

One interesting point about the CSV output file is that each row has a column for each performance counter value. As you change the list of performance counters to capture, the columns change. When we import the data we'll need to "unpivot" the columns and turn them in to rows.

Create Performance Counter History Table

We'll use the following table to capture the history of performance counter values:

CREATE TABLE [dbo].[PerformanceCounterHistory](
 [ServerName] [varchar](50) NULL,
 [CaptureDate] [datetime] NOT NULL,
 [CaptureTime] [varchar](50) NOT NULL,
 [CaptureHourOfDay] [int] NOT NULL,
 [CounterName] [varchar](100) NOT NULL,
 [CounterValue] [float] NOT NULL,
 [CaptureTimestamp] [varchar](50) NOT NULL,
 [InputFile] [varchar](100) NOT NULL
)

I like to look at averages by hour so that is the reason for the CaptureHourOfDay column. The InputFile column is the filename that contained the performance counter values. This allows for easily detecting whether a CSV file has already been imported.

Create the Import SSIS Package

I noted above that the output CSV file needs to be unpivoted; i.e. the performance counter values are in columns and we need to produce a row for each that we will import into the history table. SSIS has built-in capabilities to read a CSV file and unpivot data; however, since the columns in the output CSV file change as we change the list of performance counters to capture, we would have to continually change the SSIS package. Rather than do that I'll use a data flow Script component to parse the output CSV file. The following is the Data Flow of the SSIS package:

Parse Performance Counter CSV file is a script component source; i.e. it will read the output CSV file, unpivot the data, and add a row to the data flow for each performance counter value. The Append to PerformanceCounterHistory table object is an OLEDB Destination which will insert each row into our history table.

The following is the VB.NET code in the script component:

Public Overrides Sub CreateNewOutputRows()
  Dim InputFile As String
  Dim RowBuffer As String = Nothing
  Dim Done As Boolean = False
  Dim RawColumnNames As String()
  Dim ColumnNames As New List(Of String)
  Dim ColumnName As String
  Dim CharsToTrim() As Char = {""""c, " "c}
  Dim ServerName As String = Nothing
  Dim FullColumnName As String
  InputFile = Variables.InputFile
  Using s As StreamReader = New StreamReader(InputFile)
    RowBuffer = s.ReadLine()
    If RowBuffer IsNot Nothing Then
      Dim ServerNameEndIndex As Integer
      Dim RawServerName As String = Nothing
      ' Get column names from first row
      RawColumnNames = RowBuffer.Split(New [Char]() {","c})
      For Each RawColumnName As String In RawColumnNames
        ' strip off any double quotes 
        FullColumnName = RawColumnName.Trim(CharsToTrim)
        ' Get the server name prepended to each counter;
        ' e.g. \\SERVERNAME\counter name
        If ServerName Is Nothing Then
          If FullColumnName.StartsWith("\\") Then
            ServerNameEndIndex = FullColumnName.IndexOf("\", 2)
            If ServerNameEndIndex <> -1 Then
              ServerName = FullColumnName.Substring(2, ServerNameEndIndex - 2)
              RawServerName = "\\" + ServerName + "\"
            End If
          End If
        End If
        ' Get the counter name; strip off the server name
        If RawServerName IsNot Nothing Then
          If FullColumnName.StartsWith(RawServerName) Then
            ColumnName = FullColumnName.Substring(ServerNameEndIndex + 1)
          Else
            ColumnName = FullColumnName
          End If
        Else
          ColumnName = FullColumnName
        End If
        ColumnNames.Add(ColumnName)
      Next
      Dim ColumnNumber As Integer = 0
      Dim DateTimeString As String = Nothing
      Dim RawColumnValues As String()
      Dim CounterValue As Double
      Dim ColumnValue As String
      Dim CaptureDate As Date
      Dim CaptureTime As String
      Dim CaptureHourOfDay As Integer
      Dim CaptureTimeBlankIndex As Integer
      ' iterate thru each row
      While Done = False
        ColumnNumber = 0
        RowBuffer = s.ReadLine()
        If RowBuffer IsNot Nothing Then
          RawColumnValues = RowBuffer.Split(New [Char]() {","c})
          For Each RawColumnValue As String In RawColumnValues
            ' strip the double quotes
            ColumnValue = RawColumnValue.Trim(CharsToTrim)
            If ColumnNumber = 0 Then
              ' Parse the datetime column; format is: MM/DD/YYYY HH:MI:SS.999
              DateTimeString = ColumnValue
              CaptureTimeBlankIndex = ColumnValue.IndexOf(" ")
              Date.TryParse(ColumnValue.Substring(0, CaptureTimeBlankIndex), _
                CaptureDate)
              CaptureTime = ColumnValue.Substring(CaptureTimeBlankIndex + 1)
              Integer.TryParse(CaptureTime.Substring(0, _
                CaptureTime.IndexOf(":")), CaptureHourOfDay)
            Else
              If String.IsNullOrEmpty(ColumnValue) = False Then
                If Double.TryParse(ColumnValue, CounterValue) Then
                  OutputBuffer.AddRow()
                  OutputBuffer.CounterValue = CounterValue
                  OutputBuffer.CaptureTimestamp = DateTimeString
                  OutputBuffer.CounterName = ColumnNames(ColumnNumber)
                  OutputBuffer.InputFile = InputFile
                  OutputBuffer.ServerName = ServerName
                  OutputBuffer.CaptureDate = CaptureDate
                  OutputBuffer.CaptureTime = CaptureTime
                  OutputBuffer.CaptureHourOfDay = CaptureHourOfDay
                End If
              End If
            End If
            ColumnNumber += 1
          Next
        Else
          Done = True
        End If
      End While
    End If
  End Using
End Sub

The following are the main points about the above code:

Get the column names from the first row
Parse the column name into the server name and performance counter name (a CSV file should only have performance counters from a single server)
Iterate thru the remaining rows, parse each row into the output fields, and add a row to the data flow

There is one section of code that may seem odd; I handle the situation where the column names don't include the server name. This is done because I had some CSV files where the server name had been manually removed.

To run the SSIS package I use the following DTEXEC command:

dtexec /FILE PerformanceCounterETL.dtsx 
       /SET "\Package.Variables[User::InputFile].Properties[Value]";
            "C:\mssqltips\PerformanceCounterETL\COUNTER_TEST.csv"

The SSIS package has a variable InputFile which can be set to the output CSV file name to process on the command line. For additional details on using DTEXEC and specifying command line parameters, take a look at our tip How to Specify Command Line Parameters for DTEXEC Using Command Files.

Note that the above command should be entered on a single line in a command prompt window; it is shown on separate lines for clarity.

After running the package on my sample CVS file, I can see the following rows in the PerformanceCounterHistory table:

the ssis package has a variable inputfile

Next Steps

An SSIS package is a great way to import performance counter values into a history table for analysis and identifying trends.
You can download the sample SSIS package here.
Check out all of the SQL Server Integration Services tips and tutorial.
Check out all of the SQL Server Performance Monitoring and Tuning tips.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2011-05-26

Storing Windows Performance Counter Values in SQL Server

Problem

Solution

Next Steps

About the author

Comments For This Article