SQL Server DBA Dashboard

February 29, 2008
SQL Server DBA Dashboard
By Gregory A. Larsen

Last month in my article (Building Custom Reporting Services Reports for SQL Server Management Studio ), I discussed that with SQL Server 2005 SP2 Microsoft implemented a new feature called “Custom Reports”. Custom Reports are Reporting Services report definition files that can be rendered within SQL Server Management Studio (SSMS). With Custom Reports, you can enhance the reporting capabilities within SSMS. In this article, I show a tool I call the “SQL Server DBA Dashboard” that provides a number of different Custom Reports that can be used to monitor activities within a SQL Server instance.

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:

Here you can tell that job “TestForFailure” failed because it couldn’t find stored procedure “xxx”. This report is wider than a single screen. Reporting Services creates a scroll bar to allow you to scroll to the right or left, and even down and up if a report is bigger than your current screen setting. Also on each drill down report, there is a “HOME” hyperlink in the upper left hand corner of the report that allows you to navigate back up to the HOME page of the dashboard.

By a click of the mouse button, you can use the “SQL Agent Job Failures” graph to drill down and show you all of the job failure report information for any day that had a job failure. This allows you a quick method to review all of your job failures in a single report of a given day. Note there are more job failure reports available via the hyperlinks under the “Server Wide Information” section.

In the SQL Server DBA Dashboard home page above, the “Backups in Last 24 Hours” had a small slice of the pie chart, which is red. This red slice represented databases that didn’t have a current backup (no backups in last 24 hours). If you wanted to see a more detailed report of what databases are missing backups you can click on the “Backups in Last 24 Hours” graph and the following screen will be displayed:

Here you can see the TEST and TEST_REP databases are the ones that are missing database backups.

In addition to the SQL Server DBA Dashboard displayed above, there is also an additional dashboard specifically target for programmers. It is called the “SQL Server Programmer Dashboard” and can be displayed by referencing the “Programmer_Dashboard_Main.rdl” file when bringing up a Custom Report. The programmer dashboard is a stripped down version of the DBA dashboard that only requires “ VIEW SERVER STATE ” permissions. This programmer dashboard allows programmers access to use this dashboard tool without out giving them “sysadmin” permission. Here is what the programmer dashboard looks like:

With the programmer dashboard, the programmers can get performance information regarding the TSQL code they have written. The programmer dashboard just contains a few less hyperlinks then the DBA dashboard.

Both of the dashboards have a menu of hyperlinks on the on the left. These hyperlinks are broken up into two different groups: Server Wide Information and Database Information. The “Server Wide Information” section contains hyperlinks that allow you to drill down and get detailed reports at the server level. Meaning the reports contain information about all databases, or just server information like SQL Agent Job information. The “Database Information” hyperlinks produce reports for only the database that is in focus when you launch either one of the main dashboard custom reports (more on this later on).

The server wide and database information hyperlinks allow DBAs and programmers an easy navigation mechanism to drill down and get performance related report information. There are a number of different “TOP 100” reports that can be used to help identify those queries that use excessive CPU, I/O or run a long time. By looking at these reports, DBAs and programmers can narrow down the statements, or store procedures that they should work on to improve performance of their application. The only difference between the Server Wide “TOP 100” reports and the Database Information “Top 100” report is the data that is contained in the report. The “Server Wide” reports show the worst performing statements across all databases, even system databases. But the “Top 100” reports under the Database Information section only identify the top 100 resource consuming queries for a specific database. Below is the Top 100 Report based on I/O usage using the Server Wide link.

Click for larger image

The top 100 reports are wide reports so you need to scroll back and forth to see the whole report. The image above was created by pasting two screen shots together so you could see the entire report.

Each top 100 report contains the same columns. You can use the “CNT” column to see how many times a particular query has been executed. From these reports you can see the: Total IOs, Tot Logical Reads, Avg Logical Read, Tot Logical Writes, Avg Physical Reads and last time the statement was executed. You can use this information to determine which statements you should look at when trying to improve the performance of your applications. The statistical information for these TOP 100 reports is obtained by using the “sys.dm_exec_query_stats” DMV.

SQL Server now keeps track of when columns are used to constrain the results of a query that are not part of an index. The “sys.dm_db_missing_index_groups” , “sys.dm_db_missing_index_group_stats” and “sys.dm_db_missing_index_details” DMVs provide information about these columns. With the information in these DMVs the SQL Server DBA dashboard tool is able to suggest some indexes that are missing. Both the DBA and programmer dashboard tool have a “Missing Index” report. This report provides a CREATE INDEX statement for those potential missing indexes. SQL Server also calculates and estimates improvement should the suggested index be implemented. Reviewing the missing index report can help DBAs and programmers improve performance of application queries.

Another report that DBAs and programmers will find useful is the “Unused Index” report. This report identifies those indexes that have not been used since the last time SQL Server started. This report is useful to help optimize your database design. Below is a screen shot of the “Unused Indexes” report:

Performance Counter Thresholds

The Performance Counters section of the SQL Server DBA Dashboard home page shows a number of different counters. These counts will be displayed in different colors if their values are within different threshold ranges. Each counter, except the memory counters has been assigned a RED and a YELLOW threshold value. Anytime a counter value crosses one of its threshold boundaries it will be displayed either in bold YELLOW or RED font depending on which threshold boundary is crossed. This allows the DBA to quickly identify when some component of SQL Server might be tight on resources, and likely to start causing performance problem. The SQL Server DBA Dashboard home page below shows some performance counters that have crossed over into the RED or YELLOW threshold ranges:

These thresholds values are customizable. By customizing the performance counter RED and YELLOW threshold values to match your environment, you can use the SQL Server DBA Dashboard tool to easily identify when your environment might be under some resource pressures.

How the DBA Dashboard Identifies SQL Server Instance and Database to Run Against

The SSMS Object Explore node information is used by both dashboards to determine which instance of SQL Server and what database from which to gather information for the reports. When you use the Custom Report item to invoke either the SQL Server DBA Dashboard, or the SQL Server Programmer Dashboard, SSMS passes the SQL Server instance name and database name to the dashboard code based on the Object Explorer node you are on. Once either dashboard is up and running you cannot change the server instance or database context in which the reports will run. If you want to use a different database, or SQL Server instance you will need to go back to the Object Explorer pane and select a new database and/or server instance, and then bring up a dashboard report using the Custom Report item. You can have multiple dashboards open simultaneously displaying different databases and/or server instance information.

How to Obtain and Install the SQL Server DBA Dashboard

The “SQL Server DBA Dashboard” code is easy to install into any environment provided you are running SQL Server 2005 SP2. The code has been packaged up into an “msi” file to help simplify the sharing of this code with others. If you are interested in obtaining this code, you can download it from “SQL Server DBA Dashboard Version 1.1” link under the “Products” menu item at: http://www.sqlserverexamples.com/.

Support for the SQL Server DBA Dashboard

Support is available for the SQL Server DBA Dashboard, although it is only email support. Keep in mind it may take a few days to for a response to your email. In addition to support, you can also send email to request enhancements. This is the second version of the dashboard, the next version is being planned. So if you would like to see additional features you are encouraged to submit them.

Another Free Valuable Tool

I hope you see the value of using this free dashboard tool. It allows DBAs and programmers an easy way to get at performance data and other server information to help them better manage their SQL Server database environment. Download load the SQL Server DBA Dashboard and see for yourself the kinds of reporting tool that can be built using the new Custom Reporting options within the SP2 version of SQL Server 2005.