SQL Server 2012 : Performance Dashboard Reports

Views 1778

With the release of SQL Server 2012, out of the box you have the ability to review your environment using Dynamic Management Views (DMV’s) and Dynamic Management Functions (DMF’s) which allow you access to real time information about your environment since the last restart of the SQL Server 2012 instance.

If you are like me, then I am sure you will like this add-on for your SQL Server 2012 instance. It easily allows you to monitor your environment with some SQL Server Reporting Services (SSRS) reports which are accessing these DMV’s & DMF’s to provide you with at a glance overview of your instances performance.

The Performance Dashboard Reports are available for download from the Microsoft SQL Server 2012 Feature Pack download page. This is just one of many additional features available for you to utilise.

Installation

Now that we have downloaded ‘SQLServer2012_PerformanceDashboard.msi’ we are ready to install the dashboard into our SQL Server 2012 environment. Navigate to the directory you saved the ‘SQLServer2012_PerformanceDashboard.msi’ to and double click on the msi, click ‘Next’.

Windows-Live-Writer-ff33fec5328a_515E-image_2

Accept the license terms and conditions, click ‘Next’.

Windows-Live-Writer-ff33fec5328a_515E-image_4

Enter the Name and Company details, click ‘Next’.

Windows-Live-Writer-ff33fec5328a_515E-image_6

Choose the installation location, click ‘Next’.

Windows-Live-Writer-ff33fec5328a_515E-image_8

Click ‘Install’ to start the installation.

Windows-Live-Writer-ff33fec5328a_515E-image_10

Click ‘Finish’ to complete the installation.

Windows-Live-Writer-ff33fec5328a_515E-image_12

To complete the process, in SQL Server Management Studio (SSMS) you need to navigate to the setup.sql script which you will find in the installation directory you chose above. This will create the required stored procedures and functions used by the dashboard reports.

To utilise these reports in your environment, in SSMS right click on your instance name | Reports | Custom Reports. Navigate to the installation directory ‘C:Program Files (x86)Microsoft SQL Server110ToolsPerformance Dashboard’ by default and select performance_dashboard_main.rdl.

We are now ready to use the dashboard to monitor our SQL Server 2012 environment. Right click on your instance name | Reports | Performance_Dashboard_Main.

Windows-Live-Writer-ff33fec5328a_515E-image_14

Historical Information

From the dashboard main page you can drill down to investigate how your environment is performing. All of the information that is displayed in these reports are collected from the DMV’s & DMF’s in your SQL Server 2012 instance. Remember with these that the data is flushed when a restart of your instance occurs. So the results can be a little misleading if your instance has recently restarted and not all of your normal processing has completed.

Historical Waits

This report outlines the total counts for the various waits occurring in your environment. You are able to see a breakdown of a particular wait type by expanding the wait category tree’s.

IO Statistics

This report gives you a breakdown of the IO operations happening for each database in the SQL Server 2012 instance. For each database you can see the breakdown of IO operations for the data and log files as well as showing you the top 20 IO intensive objects.

Expensive Queries

To help you troubleshoot your environment and identify problematic processes there are 6 reports available based on:

  • CPU
  • Logical Reads
  • Logical Writes
  • Duration
  • Physical Reads
  • CLR Time

Each of these reports shows the top 20 worst performing cached queries. You are able to see the statements being run, giving you any missing index recommendations the database engine has determined would improve the performance of the statement. When undertaking any performance troubleshooting any recommendations around indexes need to be tested prior to implementing them.

Help

As part of the installation completed with the steps outlined above, a help system is available for you to learn about the Performance Dashboard Reports. You will find this in the installation directory that you chose above.

Happy monitoring.

Leave a Reply

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search