"The SQL Server is slow..." - Try starting with the free SQL Server 2012 Performance Dashboard Reports
SQL Server 2012 Performance Dashboard Reports are Custom Reports which Microsoft has developed to quickly give an overview of the overall performance of SQL Server 2012 instance. Keep in mind that in order to use the Performance Dashboard Reports you don’t need to install SQL Server Reporting Services. In this tip we will take a look at how to install and use these built-in SQL Server Reporting Services Reports.
SQL Server 2012 Performance Dashboard Reports can be downloaded from the following link: http://www.microsoft.com/download/en/details.aspx?id=29063. The Microsoft Documentation states that “The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. The Dashboard reports basically retrieve information from SQL Server Dynamic Management Views and hence there is no additional overhead when using these reports.”
Common performance problems that the Performance Dashboard Reports may help to resolve include:
- CPU bottlenecks
- What queries are consuming the most CPU?
- IO bottlenecks
- What queries are performing the most IO operations?
- Index recommendations generated by the query optimizer
- What are the potential missing indexes?
- What blocking is occurring?
- Latch contention
- What are the volume of latches?
Once you have successfully downloaded the SQL Server 2012 Performance Dashboard Reports then the next step will be to install the reports. Follow the steps below to complete the installation process.
Note that there's more to just installing the MSI to use these reports (there's a setup.SQL you need to run per server and you need to select the main dashboard report manually). Read the above post as it covers it all in easily to read details.
Since I'm pushing for SQL Server 2012 at my day job, I thought I should mention these reports. Sure we've got production cross-server monitoring tools and all, but sometimes you just need some simpler. And these will be great for our Dev/QA/Stage servers.
Here's a snap of the Performance Dashboard report (reporting against a SQL Server running on my notebook);
And a few snaps of a few of the other reports;
As you can kind of see, much of the data is linked, so you can click down to additional details. And remember, this is all free...