What is the SQL Server DBA Dashboard?

The SQL Server DBA dashboard tool is both a DBA and a TSQL programmer tool. The main goal of this tool is to allow DBAs and programmers to quickly identify performance, SQL Agent and disk space issues associated with a single instance of SQL Server. The dashboard tool can be installed and run against any SQL Server 2005 SP2 or SQL Server 2008 instance.

The dashboard provides 39 different performance and disk space related reports with a single click from the main dashboard page. These different reports provided information in the following areas:

• Identifies the worst performing TSQL Statement and Stored Procedures, based on different criteria like CPU, I/O and elapsed time
• Performance counter measurements
• Shows summarized and detailed SQL Server Agent Job failures
• Displays CPU consumption overtime
• Reports on Index Fragmentation
• I/O usage by database
• Tracks backup history
• Tables without indexes
• Unused and missing Indexes

There are two different dashboards within the SQL Server DBA Dashboard. There is one dashboard for DBAs and another one with a subset of reports targeted for SQL Server programmers/developers. The main reason for having two different dashboards has to do with permission. The programmer/developer dashboard only requires “VIEW SERVER STATE” permissions; where as the SQL Server DBA Dashboard requires “sysadmin” permissions.

Architecture/Components that make up the SQL Server DBA Dashboard:

The dashboard is made up of a single DLL, a database (SS_DBA_Dashboard), a SQL Server Agent job and a series of Reporting Services RDL files. As already stated the SQL Server DBA Dashboard contains two different dashboards, one for DBAs and another for programmers.

The DLL file contains most of the code used to obtain the information displayed in the dashboard reports. The DLL assembly is used to create external stored procedures in the SS_DBA_Dashboard database. In additions to SPs being created in this database there are a couple of tables to hold a small number of statistical records. This database should grow very little, if any after the first use of the dashboard.

The DLL and database needs to be installed on each SQL Server instance you want to monitor with the dashboard tool. The RDL files can be stored in a single location or multiple locations. The RDL files are designed to only to be executed from within SQL Server Management Studio. In fact, only two RDL files “DBA_Dashboard_Main.rdl” and “Programmer_Dashboard_Main.rdl” files should be referenced when bringing up a Custom Report from within SSMS. These two RDL files pass parameters to the other RDL files, so if you try to invoke the other RDL files directly from SSMS they will fail.

Any instance where the DLL and SS_DBA_Dashboard have been installed can be monitored using the two dashboards. The two main RDL files mentioned above can be rendered on any client computer that has SSMS installed. The only requirement is that the client computer be running SQL Server SP2, the user/computer can access the RDL files, a valid registration can be made between the client computer and the server being monitored with SSMS, and the user using the dashboard has the permission to execute the SPs in the SS_DBA_Dashboard database.

Quick Tour of SQL Server DBA Dashboard

Below is the HOME page for the “SQL SERVER DBA Dashboard” tool. This dashboard is displayed by asking to render the “DBA_Dashboard_Main.rdl” file using the Custom Report option in SSMS. This RDL file is known as the “SQL Server DBA Dashboard” and is targeted for DBAs, since this RDL requires “sysadmin” permissions to run. From this HOME page, the DBA can see some high-level performance and space information in the right pane, plus they have hyperlinks on the left to drill down to more detailed reports.

From this single main report, a DBA can quickly identify potential problems with their database instance. From this page, you can identify the following:

• Current CPU usage and CPU usage trend
• The number of SQL Agent job failures that have occurred in the week
• The current value for a number of different performance counters
• Whether or not there are any blocking processes
• Whether or not all your databases have been backup within the last 24 hours
• How much free and used disk space you have by drive
• I/O usage consumed by the top 5 database I/O consumers

With a quick glance at the screen shot above you can quickly see that CPU utilization spiked to 100 % for a short period of time, there were a couple of job failures a few days ago, there are no blocking processes, and a small percentage of databases do not have backups, the tempdb has over 6 MB of free space and the “C” drive is about 2/3 full .

A number of the graphs on the “SQL Server DBA Dashboard” home page allow you to drill down and get additional information. For example if you where to click on the red bar for the 1/28/2008 date on the “SQL Agent Job Failures” report the following detailed SQL Agent Job failure report would be displayed:

Full article...