Archive for the ‘Performance Monitoring’ Category

Using Excel 2007 and Excel 2010 to Analyze Perfmon Captures in the Form of .csv Comma-Delimited Counter Logs   10 comments

Here is an explanation of how to easily use Excel 2007 and Excel 2010 to analyze performance captures saved to counter logs via Windows’ built-in perfmon utility. This article assumes that you have already collected whatever SQL or Windows or other performance counters you wish to analyze, and that you have captured them to counter logs in the form of one or more comma-delimited .csv files.

We will look at how to easily open a .csv file, apply a few formatting changes, and use the very handy Pivot Chart functionality built into Excel to graph your counters. Finally, we will demonstrate how to use date filters to drill deeper into specific time ranges, in order to view the graph data in greater detail.

The screen shots and steps below are for Excel 2007, but they should also work for Excel 2010.

Note that in the process below, Excel may not by default select the line chart type you prefer, for example one with data points displayed. If this is the case, right-click on an open area of the Pivot Table chart and select “Change Chart Type.” Select the left-most line chart type (the most basic line chart), and click the option at the bottom to make that your default type. See Excel help for more info.

Let’s get started.

1. Double-click on the .csv counter log file. The .csv extension should already be associated, so the file will automatically open in Excel.

image

2. Make some formatting changes.

a. Change the text in the A1 cell from “(PDH-CSV 4.0…” to simply “DateTime.”

image

image

b. Remove row 2 by right clicking on the row number –> “Delete.” This row, containing the first row of data, is typically a junk row.

image

c.  Highlight the entire “A” column by clicking on the column header, then right-click anywhere in the column –> Format Cells…

image

d. On the Number tab, select the “Date” category and the “3/14/01 1:30 PM” type, and click OK.

image

e. Press Ctrl + Home to get the focus back on the A1 cell.

image

f. These steps are repetitive for every .csv file you will analyze, so I recommend that you automate steps a. through e. using an Excel macro, by having Excel record your actions, and then you will assign a keyboard shortcut to your macro. Please refer to the Excel help for how to do this.

Note that I have found using the Excel macro functionality for steps beyond this step unworkable.

3. Create your Pivot Chart

a. Switch to the “Insert” ribbon, click on the down-arrow on the “Pivot Table” button, and select “Pivot Chart.”

image

b. The Pivot Chart dialog “auto-magically” selects the correct Table/Range of data to analyze. Very nice functionality! Click OK.

image

c. In the PivotTable Field List box, click and drag “DateTime” down to the “Axis Fields (Categories)” pane below.

image

d. In this example, we are going to first analyze the total processor time. In the PivotTable Field List box, scroll down and find the “Processor(_Total)\% Processor Time” counter, and click and drag it to the “Values” pane below.

image

e. Notice that in this instance, Excel incorrectly guessed that I want to summarize the data by “Count of….” What I actually want is for Excel to summarize the data by “Sum of….” Sometimes Excel correctly guesses this, sometimes not.

To correct this, right-click on the the Excel header cell labeled “Count of \\mySQLInstance\Processor(_Total)\% Processor Time” –> “Summarize Data By” –> and select “Sum.”

You may find this step necessary for other counters that you want to graph later as well.

image    

4. Format and size the graph to your liking.

a. You will now have a basic graph that looks something like this.

image

b. For this chart, I will change the Max value shown on the left axis from “120” to “100.” Right-click on the left axis area –> Format Axis. Change the “Maximum” option under “Axis Options” from “Auto” to “Fixed” and type in “100.” Then click Close.

image

image

c. Edit the “Title” area text, which currently reads “Total,” to your liking. I will insert the text “\\mySQLInstance\Processor(_Total)\% Processor Time”

d. I will also click on the legend box on the right and delete it.

e. Here is what my final graph will look like.

image

f. You can click on any open space within the chart to select the whole chart, then press Ctrl + C to copy, then paste the chart image into an email or Paint or your favorite editor.

g. Once you have saved the image of the chart, in the PivotTable Field List box, you can uncheck the “Processor(_Total)\% Processor Time” counter, then drag a different counter down into the Values pane.

h. You may repeat the process for as many different counters as you may wish to graph and analyze, reusing the same chart area.

5. Drilling deeper into specific time ranges.

a. Looking at the “Processor(_Total)\% Processor Time” chart I already created, I notice there was a CPU spike around the hour of 5:30 a.m. to 6:30 a.m. I want to investigate that time period more closely, and “zoom” the graph in, to that time period, so I can see in more detail what was happening during that hour.

b. In the PivotChart Filter Pane, click on the down-arrow next to “DateTime.”

image

c. Go to “Date Filters” –> and select “Between….”

image

d. Type in the beginning and ending dates/times in the format “m/d/yy h:mm am/pm” and click OK.

image

e. Your chart will now be “zoomed in” to the hour of 5:30 a.m. to 6:30 a.m. and will look something like this.

image

There are other features you may want to experiment with, such as adding trend lines, and making other changes to the display of the left axis area.

Happy charting!

Posted May 30, 2011 by Norm Enger in Excel, Microsoft SQL Server, Performance Monitoring

Advertisements