Using SQL Server 2012 Performance Dashboard Reports

In this post I would like to talk about various reports ( SQL Server 2012 Performance Dashboard Reports ) that are available to us (through Microsoft Download) and their purpose is to give us an overview of the overall performance of a SQL Server instance.Basically they can help us identify where bottlenecks (CPU,IO) can be found.These reports identify where blocking occurs (waits-latches).You do not have to install SQL Server Reporting Services in order to use these reports.They retrieve all the data they present us through DMVs.

You can download these reports here. The installation process is straight forward and you just need to follow the steps of the installer.

Once these reports (these are simply Reporting Services report files) have been installed we can use them through SSMS (Custom Reports).

There are Standard Server reports available to us.Let’s have a look at what standard reports are available to us at the server level.We have reports on queries performance (filtered by IO,CPU), transactions that cause blocking,locking,memory consumption on the server e.t.c.

Please have a look at the picture below

I have chosen the first report (Server Dashboard) – This report provides overview data about the SQL Server instance and its configurations.

Have a look at the picture below

There are Standard reports at the database level as well.We can have Disk Usage reports,backup and restore events reports and much more.

Have a look at the picture below

For this example I have chosen the Index Usage Statistics report.

Have a look at the picture below

Let me show you how to use SQL Server 2012 Performance Dashboard Reports after you install them. You just right- click on the server in the Object Explorer, then choose Reports from the context menu and then Custom Reports.

Have a look at the picture below

Then in the Open File Dialog box choose performance_dashboard_main report file (you must know the folder where all these reports are kept- e.g “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard” folder)  and click Open to display the report.

Please have a look at the picture below that shows the output of this particular report.

Have a look at the rest of the Custom Reports (Waits,IO Statistics) by selecting the links from the main report.

Hope it helps!!!

About these ads