mssqltips logo

Storing Windows Performance Counter Values in SQL Server

By:   |   Updated: 2011-05-26   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Monitoring

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:

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:

create the import 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.


Last Updated: 2011-05-26


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools