Clean-Up the Performance Monitor Data using Microsoft Excel
-
The next step before you can import the log file into SQL Server is to do some clean up of the file using Microsoft Excel. The reason we have to do this is because the export file has some header information that needs to be removed before we can get a clean import into a SQL Server table using DTS. In the illustration below, you can see the kinds of header information that is produced in the export file. It is our job to clean it up.
-
Here’s is what you will need to do to clean up this file. The first six rows contain header data, and row seven is a blank line, so all of these rows need to be deleted. Row 11 contains the name of the object for each counter. We don’t need this information so delete row 11. Row 12 includes the names of the Date and Time columns, but the rest of the columns indicate the name of the server being monitored. What I do here is move the Date and Time column names to row 8, which is where the rest of the column names are located. After moving these two column headings, I then delete row 12. You may or may not have any data in row nine. This row is used for the name of the counter’s instance, if there is one. In this example, there are a few cases where there is more than once instance of a counter, so what I do is to move the instance name from the column in row 9 and add it to the column name in row 8. This way, I know which counter goes with each instance. Rows 10 is also blank, and I delete it. This should only leave you row 8 at the top of the spreadsheet, which should now have all the column names that will be exported to a SQL Server table.
-
Once my data is cleaned up, I save the spreadsheet as an Excel spreadsheet, not as a CSV file. The format you decide to save the file in is not too important, but for this article, I will assume the Excel format.
Create a Database to Store Your Performance Monitor Data
-
Before you can import the data into a SQL Server table, you will need to create a database. I call my database SQL_Performance_Data. I created it to be 5MB with a 1MB log file, but you can start with any sizes you feel appropriate. To store the data, you will create a table later when you import the performance data into the database using DTS for the first time.
Use DTS to Create a Package to Import the Excel Data into a SQL Server Table
-
Once the database is created, the next step is to use DTS to import the spreadsheet into the database as a table. The first time you do this you can have DTS create the table for you, using the column names from the Excel spreadsheet. After you run DTS for the first time, when you import additional performance data at a later time, you will only need to append the data to the preexisting data in the table. Use the following steps to use DTS to import the performance data from the Excel spreadsheet into a newly created table.
-
Now start the DTS Import Wizard and specify “Microsoft Excel 8.0” as the data source and point to the Excel spreadsheet that contains your performance data.
-
For the destination source, specify the “Microsoft OLE DB Provider for SQL Server”, your SQL Server name, and the name of your database. Use whichever authentication method is appropriate for you.
-
Next, choose the “Copy table(s) from the source database” option from the wizard.
-
This next step requires the most work, and there are several different approaches you can take, all of which will work. The one I am showing you here is just one possible approach. Use your own experience and preferences to determine exactly how you want to proceed.
-
The first thing I recommend is to rename the destination table as the same name as the SQL Server that is being logged. This way, you can keep a separate table of performance data for each SQL Server you monitor, and by giving each table the name of the server, you won’t get confused about which data belongs to which server.
-
Next, click on the “Transform” button, which displays the column mappings. You may want to choose different Data Types for the table that will be created rather than choosing the ones selected by the DTS Wizard. I use all of the default Data Type settings except for one. For the Time column, I change the default Data Type from smalldate to Char(11).
Import the Excel Spreadsheet Data into a SQL Server Table
-
Once the DTS package has been defined, then save the DTS package. And once the package is saved, it can be run and the data imported from the Excel spreadsheet into a newly created table.
-
The next time you import data into your table, you will need to modify the DTS package so that is appends the data to currently existing table instead of creating a new one.
-
Now that the data is in SQL Server for the first time, you may want to add indexes to your table to boost query performance, as there aren’t any by default. Other than that, you are ready to use the data in the table anyway you want. You can analyze it directly using Transact-SQL, or export it back out to Excel for analysis.
Create a Routine
All the steps above are worthless unless you regularly import your Performance Monitor log files into SQL Server. You need to develop a routine, such as daily, weekly, or monthly, where you import your Performance Monitor logs into SQL Server. If you have a knack for programming, you may want to write your own code to automate the tasks. However you decide to do it, you need to develop a regular routine and stick to it.
]]>