This post is a part-2 for my previous blog on Collection and Reporting of Perfmon data for SQL Server “Capacity Planning” and “Trend Analysis” in which I demonstrated the steps on how to collect and prepare reports for SQL server capacity planning and/or trend analysis by data collected using Performance counters. To extend the discussion further, in this post I provide with you some templates I have prepared which can be used to easily generate the reports I talked about in my previous blog.
So, here is how you can make use of these predefined templates to generate the report.
1. Follow steps 1-3 as per this blog so that you have perfmon data collected and imported into a SQL database.
2. Download and install PowerPivot for SQL 2008 R2
3. Download the following templates as per your need. There are two reports templates attached herewith:
i. Perfmon_Report Template_Month_Day.xlsx –> Use this template if you have collected Perfmon data over an extended period of time (say three or more days or weeks). This template has Months & Days in the axis field for overall analysis.
ii. Perfmon_Report Template_Day_Hour.xlsx –> Use this template if you have collected Perfmon data for a short period of time (say few hours, a day or two). This template has Days & Hours in the axis field for detailed analysis.
4. Let’s assume you’re using Perfmon_Report Template_Day_Hour.xlsx template. Open up this file, under Powerpivot > Powerpivot window > Design tab, click Existing Connections. Edit the default connection named SqlServer by providing the server\instance and database name (where you loaded perfmon data). Click Save and then click Refresh forpowerpivot table to be populated.This may take a while, once done, save the current powerpivot window. It will take you back to the excel window.
5. On the Excel window, under Data menu click Refresh All. This will populate the charts in all the sheets. Now you have the charts/reports generated for your analyses.
P.S: Please fill-in System details in the System Overview sheet manually.
Hope this eases the task of generating reports/charts out of perfmon data. Feel free to leave a comment should you have one.