MS SQL – Performance Dashboard

Microsoft is distributing a nice – MS SQL Performance Dashboard which you can use in the SSMS and SSRS.

MS SQL 2005 : http://www.microsoft.com/en-us/download/details.aspx?id=22602

MS SQL 2008 / 2012 : http://www.microsoft.com/en-us/download/details.aspx?id=29063

Getting Started with the Performance Dashboard Reports in SSMS

1. Each SQL Server instance you plan to monitor must contain the procedures and functions used by the queries in the reports. Using SQL Server Management Studio (SSMS), open the Setup.sql Script from your installation directory (default of %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script.

Close the query window once it completes.

2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory and open the performance_dashboard_main.rdl file. Explore the health of your server by clicking on the various charts and hyperlinks in the report.

All of the remaining reports are accessed as drill through operations from the main page or one of its children. For a detailed explanation of all installation requirements and guidance on how to use the reports, please see the help file, PerformanceDashboardHelp.chm.

Once installed you can also use it inside of the SSMS – Browse to %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard folder and open the performance_dashboard_main.rdl report.

clip_image002

Getting Started with the Performance Dashboard Reports in SSRS

To get it to work you need to run the SETUP.SQL query in the Master DB. Copy all files from the install directory to your SSRS reports directory. And upload them to the SSRS Reporting Portal.

clip_image004

Create a Shared Data Source in the Report Server !

clip_image006

clip_image008

Once the reports are using the correct data source you can run the main Report to test it.

clip_image010

clip_image012

clip_image014

SharePoint Database Health Dashboard – Bonus

If you are running SSRS in SharePoint Integrated Mode, than you have a nice interface to monitor your database Health.

Just publish the SSRS report to a SharePoint library

image

And there you go all the data is available per content database.

image

Enjoy !!

Advertisements

Leave a Reply

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: