Creating SQL Server performance based reports using Excel

I’m not a big fan of Performance Monitor but in this post we will review the steps in how to export the results so they can be read more easily in Excel.  This post will not discuss how to setup a data collector set and will assume the reader already has results saved to the file system in a .blg file format.

If you’ve ever used PerfMon you’ve probably noticed .blg files. These are the files that hold all of our performance data that we have collected over the past week. When you open this file in PerfMon it can be tedious work to get the data in a presentable form. Actually, I don’t know if there’s a good way to get this data in a presentable form using the PerfMon’s GUI.

SQL Freelancer SQL Server PeSQL Freelancer SQL Server Perfmon Graph Excel

First thing we need to do is to convert the .blg to .csv so we can open it in Excel. To convert this file open command prompt and navigate to the directory the file is located and type:

Relog SQL_BASELINE_20140128.blg –f CSV –o NewFile.csv

SQL Freelancer SQL Server Perfmon Graph Excel
Once the command completes successfully, you should see the new file in the same directory:

SQL Freelancer SQL Server Perfmon Graph Excel

Open NewFile.csv in Excel:

SQL Freelancer SQL Server Perfmon Graph Excel

Not pretty huh? First thing we need to do is format the first column….which is the Date column. Change the text in the A1 cell from PDH-CSV 4.0…. to DateTime:

SQL Freelancer SQL Server Perfmon Graph Excel

Next, remove Row 2. We do not need this data. Also, highlight column A and right click anywhere in the column and choose Format Cells:

SQL Freelancer SQL Server Perfmon Graph Excel

On the number tab, click the Data Category and select 3/14/2012 1:30PM:

SQL Freelancer SQL Server Perfmon Graph Excel

Press Ctrl+Home to select the A1 cell.

Create Pivot Chart

Click on the Insert tab and select Pivot Chart:

SQL Freelancer SQL Server Perfmon Graph Excel

The Pivot Chart dialog box should automatically select the correct cells to analyze. If not, make sure all of the cells are selected:

SQL Freelancer SQL Server Perfmon Graph Excel

Once the new worksheet opens, drag DateTime to the Axis Fields pane:

SQL Freelancer SQL Server Perfmon Graph Excel

Now, depending on what counter(s) you want to analyze, drag it down to the Values pane. For this example, I’ll analyze Processor(_Total)\% Processor Time:

SQL Freelancer SQL Server Perfmon Graph Excel

Ta da! You now have a graph that displays your Processor %.

SQL Freelancer SQL Server Perfmon Graph Excel

To make this presentable, simply format the graph to your liking and you’re done.