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