To create a SQL Server performance report in Excel

Reprinted from Kun Lee "Creating SQL Server performance based reports using Excel"

It is important to monitor the database in performance test tuning, and it is also important to use Excel to easily generate the report for our productivity gains. The Excel data used in this article is described in detail in another blog post, "How to obtain performance counter values for SQL Server under the Windows platform." Or go to the blog link given in this article "Setting up performance Monitor to always collect performance statistics" (in the next paragraph), according to the method obtained.

Problem

In a previous tip, ' Setting up performance Monitor to always collect performance statistics ' I wrote about what to collect  Performance Monitor data, but once you have the data and what does with it. In this tip I'll show how do I use Excel to analyze the data to help determine where your bottlenecks could be and also a N Easy-to-create quick reports and charts for your SQL Servers.

Solution

Before we get started here is a couple of things you'll need for this tip.

• Microsoft Excel 2007-you also can use Excel 2003 or earlier version of this tip, I used the latest version.
• Perfmon trace files At least one day in "csv" format. -If you had a file in "BLG" format, you can easily convert it by using the "relog" tool.  When I get a chance, I'll write another tip about the Relog tool and other tools that work well with Perfmon. To collect data using Perfmon can review this tip Setting up performance Monitor to always collect performance statist Ics.

Step 1:open the CSV file

Once you has collected the performance data you can open the CSV file using Excel and your should see data similar to the Following.

Step 2:adjust The format

To allow easy reporting of the data there is a few things that I did to adjust the data.

• Replace server name with a empty string-it helps to make reading the counter names easier. In this box I am replacing "\\AREA51\" the name of my server to nothing. (this was optional, but recommended)

• Cell-a1:replace "(Pdh-csv 4.0) (Eastern Standard Time) (+" Time " (Optional, but recommended)
• Delete the second row-very often, the first data row have bad data
• Change COLUMN A cell format to "date Time"

Final look before we start using it the data.

Step 3:create PivotTable with PivotChart

• From the Insert, menu select PivotTable and then select PivotChart as shown below

• Take the default settings and click "OK"

• After you select the above, you'll get a screen similar to the following. (To get a bigger workspace area can close the "PivotChart Filter Pane")

Step 4:let ' s generate our first graph

For this example we'll look at CPU

• From the ' PivotTable Field List ' select ' Time ' and drag it into the ' Axis fields (Categories) ' Area
• From the "PivotTable Field List" select "Process (_total) \% process Time" and drag it to the "Values" area
• At this point you'll have a graph similar to the one shown below

• You can now just select the chart and copy and paste it to a report, an email, Word document etc ... As shown below

• If you want to change it from processor time to batch requests you can remove "process (_total) \% process Time" and select "Sqlserver:sql statistics\batch requests/sec" and you'll get a chart like below

Next Steps

• There is many ways to extend this reporting to make it more useful for both short term and long term needs. In order to do, it's easier to load the Perfmon data into SQL Server and use the power of SQL Server along with EXCE L to generate the reports.
• By using the ' relog ' tool, you can load the Perfmon data directly into SQL Server
• By using the ' logman ' tool, you can setup Perfmon to store the performance data directly to SQL Server
• To get the started you can download a sample CSV file here with a lot of performance counters
• Here is a few more examples of reports and charts can create

Sample 1

Sample 2

Sample 3

To create a SQL Server performance report in Excel