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’.
Accept the license terms and conditions, click ‘Next’.
Enter the Name and Company details, click ‘Next’.
Choose the installation location, click ‘Next’.
Click ‘Install’ to start the installation.
Click ‘Finish’ to complete the installation.
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.
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