Automated Reports for SQL Server Perfmon data   20 comments


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.

Enjoy!

20 responses to “Automated Reports for SQL Server Perfmon data

Subscribe to comments with RSS.

  1. Pingback: Collection and Reporting of Perfmon data for SQL Server “Capacity Planning” and “Trend Analysis” « SQLactions.com

  2. Hi Prahsant,

    A very good idea and very generous of you to share the template out.

    But, When i try to populate the charts,it is not working for me. I am able to successfully populate the pivottable but when i click Refresh all under data, The charts are not getting populated.

    Any clues on how to troubleshoot it or if i am missing something?

    Thanks
    Karthik

    • Hi Karthik,

      Once data is populated in power pivot window, please save it (hit the save button at the top left) and return back to excel window. Now, re-try to “Refresh all” under Data menu. See if other charts are getting populated, there might be an issue with a particular sheet (e.g. the perform may not have collected data for some counters etc..)

  3. Nice 1 dude..

  4. Hi Prahsant,
    I would like to thanks for such a good post, keep writing.
    I am trying to use your template to make baseline.
    Thanks
    Parveen

  5. Pingback: We didn’t know that… | SQLactions.com

  6. Prashant, Thank you for all the awesome work you’ve created. Im having an issue while creating the graphs where I get the message that the number of data series per chart is 255, and the graphs are blank. I was only testing with two days of data too, Is there any way I can modify the counters/data to have the graphs presented?

  7. Yes, graphs are populated in some tabs, but for some others (especially for the main ones like Memory and SQLBuffer manager) the message pops up, and leaves the graphs blank.

  8. That would be awesome Thank you.

  9. hey, just checking if you have any new updates with the graphs? Please let me know how I could try to fix the issue.

  10. Hi Prashant,

    This is really helpful stuff. Thank you for sharing your hard work with all of us.
    I have a little problem with the templates though:
    1. I downloaded the “Perfmon_Report Template_Day_Hour.xlsx”.
    2. Opened it in Excel 2010. CLick on PiwerPivot>PowerPivot Window>Design tab>Existing connection.
    3. Tried to edit the sqlserver datasource but its greyed out.

    Any suggestions?

  11. Hi Kazi,

    Thank you for the kind words.

    I will check about your problem and get back to you later. Btw, please make sure you have Powerpivot plugin available in Excel.

    See this blog for details.
    https://sqlactions.com/2012/05/15/collection-and-reporting-of-perfmon-data-for-sql-server-capacity-planning-and-trend-analysis/

  12. I just realisd that the template you supplied doesn’t have the PivotTable on it. It only has the PivotCharts. Hence the charts are disconnected. Is it possible to shoot through a template with the PivotTable, so that I can then edit it to point it to my db?
    my email is kns_85@hotmail.com

    • Charts can’t be created without pivot tables. They are there in hidden sheets.

      • Hi Prashant,

        I installed MS Excel 2013 Professional instead of using PowerPivot on Excel 2010. The workbook seems to work now.
        However as Sam mentioned above, I get the “the number of data series per chart is 255” error for some sheets.
        Please keep us posted for any updates to this.
        Overall, you’re a legend, thank you for the beautiful tutorial.
        Kazi.

  13. Hello Prashant et al,

    Fantastic article as usual! Thanks again for your helping share this great stuff. I am having issues with the daily/hourly template listed in this article.

    I have followed part 1 to the Tee, I was also able to import all of counter data and I had no issue plotting the charts per part 1 of this series.

    Somehow I am having no luck able to populate the data per part 2 of his series.. I am using excel 2010 and the sql server 2008 R2 Power pivot plug in..

    Steps I am undertaking…..

    a) I open the template file in Excel 2010.

    b) Next I open the PowerPivot Window and go to design tab.

    c) click Existing Connections. Edit the default connection named SqlServer by providing the server\instance and database name (where you loaded perfmon data).

    I am unable to find or even edit the connection discussed above. It seems like an issue whichever way I go.

    Any help is appreciated. Thanks in advance.

    Amit

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: