MSFT released a set of Performance Dashboard Reports post the release of Service Pack 2 for SQL Server 2005. You can use the SSMS (SQL Server Management Studio) client tool to look into the output of these reports which need to be categorized under the “Custom Reports” category. This feature was one of the new features that were introduced along with SP2. You do not need to have Reporting Services installed in order to use the Performance Dashboard Reports – these are just additional reports that can be viewed in addition to the ones that are already provided in SSMS. These reports are a welcome addition to the DBA/Database Developer arsenal for troubleshooting performance issues with the instance – you can find out the queries consuming the most CPU or causing the IO bottlenecks as well as missing indexes recommendations from the optimizer, blocking and latch contentions etc.. More on those features later on – firstly, if you have not already downloaded and installed the Dashboard Reports, you can do so from this link from MSFT:
The requirement for running these reports is that the Service Pack 2 must be installed on the instance that you wish to monitor. Once you have downloaded the files, you then need to run the Setup.sql file in SSMS to create the procedures, functions and views in the MSDB database. This needs to be done on every instance that you wish to moniitor. In this blog post, we will go over the installation of the Dashboard Reports and how to view them.
Once you have downloaded the msi package for the install (SQLServer2005_PerformanceDashboard.msi) and have installed it (the installation is a series of very simple steps), you will be ready to run the setup.sql script file which will be put in the same folder where the Performance Dashboard reports are installed. One thing to note here is that when you are doing the install, if you have a lot of instances that you want to monitor in your environment, you should/could chose to store the reports on a file share instead of the local drives. That way, you will not need to install this on all those N number of instances and can share them from a common place. Once the install is done, pick up the setup.sql script file and run it in SSMS on all the SQL Server instances that you want to monitor. The script then runs in the MSDB database and creates a set of stored procedures, views and functions –> these are the ones that are used by the Performance Dashboard Reports in order to build up the display and those DMVs and DMFs are pretty powerful and a good way to learn the system internals as well.
Once you are done with running the script, you can navigate to SSMS, connect to the instance, right click on the database that you want to monitor, select Reports, then Custom Reports and then pick up the “Performance_Dashboard_Main.rdl” report from the file-share or the local directory where you had done the install. Screenshot for that is shown below.
Once you select it, the main report that will appear will be like the one shown below. SSMS will remember this report and it will be available for you to look into at a later stage as well rather than requiring you to upload them again and again.
In the future blog posts, we will cover the reports themselves and how they can be used and which DMVs/DMFs they are based on.