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:
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.
