By: Ben Snaidero
Overview
Although everyone could just use Performance Monitor to view the data sometimes you want to be able to do something a little more complex when visualizing the data. Many people, especially Windows users, like to use Excel to generate graphs and charts but before we can use a spreadsheet to create our own reports there is one small change that we have to make to the data collector. By default, the log file used to store the performance counter data is in a binary format. In order for us to be able to open the log file data in a spreadsheet we need to store this data in CSV format. To do this we open the "Properties" for the data collector and update the "Log format" to "Comma Separated" as show below.
Once that is done and we have run our data collector set again, the latest performance counter data should be stored in a CSV format. Below is an excerpt from the CSV file.
I won't go into too much detail in creating reports using Excel as that could be a whole topic on its own but once you have the performance counter data in a file in csv format creating a report is as simple as opening the file, selecting all the data, and then using the "Insert" Menu to select the type of chart you want to create as shown below.
Below is an example of the 2D line graph we selected above.
Additional Information
- Collect SQL Server Performance Counters and Build Reports with SSRS
- Collecting Performance Counters and Using SQL Server to Analyze the Data
- Creating SQL Server Performance Based Reports Using Excel
