SQL Server DBA Dashboard

Microsoft added a reporting feature to SQL Server Management Studio (SSMS) when they rolled out SQL Server 2005 SP2. This new reporting option is called “Custom Reports”. Now DBA’s and developers can expand the reporting capabilities within SSMS by building their own custom reports. Custom Reports are just Reporting Services reports rendered within SSMS. This new reporting capability doesn’t even require Reporting Services to be installed. In my last article titled “Creating Custom Report for SSMS” (http://sql-server-performance.com/articles/dba/custom_reports_ssms_p1.aspx), I discussed how to build Custom Reports and discussed Microsoft’s “Performance Dashboard” product, a tool built using the custom reporting capabilities within SSMS. I’m going to expand on my discussion of custom reports by showing you a tool I built called the “SQL Server DBA Dashboard” that uses the new SP2 reporting feature.

What is the “SQL Server DBA Dashboard”?

My “SQL Server DBA Dashboard” reporting tool is a homegrown report console that provides reporting and monitoring capabilities for a single instance of SQL Server 2005. The “SQL Server DBA Dashboard” utilizes the “Custom Report” feature implemented with SQL Server 2005 SP2. My dashboard has a HOME page that contains some high level charts and information about the instance of SQL Server which the dashboard is run against. From the “SQL Server DBA Dashboard” HOME page you can drill down to obtain additional more detailed information to get a better picture of what is and has been happening on the instance of SQL Server being monitored. Let me give you a guided tour of my dashboard.

Guided Tour of “SQL Server DBA Dashboard”

The “SQL Server DBA Dashboard” is made up of a series of Report Definition Language (rdl) files. Each rdl file is a SQL Server Reporting Services report definition that was built using the SQL Server 2005 Business Intelligence Development Studio and is stored in a file directory accessible by SSMS. Each report executes one or more stored procedures to obtain the SQL Server information necessary to populate the given report. Additionally I created one SQL Server Agent job to gather snapshots of information at routine intervals. The snapshot data is stored in a table within a SQL Server database. The stored procedures and snapshot data are stored in a database name “SSE_DBA_Dasboard”. Now that you have a basic idea of the architecture of the “SQL Server DBA Dashboard”, let me give you a test drive that will show in more detail what is available within the dashboard.

To bring up the HOME page of the “SQL Server DBA Dashboard” you use the “Custom Report” option within SSMS. The “Custom Report” option is found by right clicking on any node within the SSMS Object Explorer pane, and hovering over the “Report” item. By clicking on the “Custom Report” and then browsing to where the “SQL Server DBA Dashboard” rdl files are stored I can open the main dashboard rdl file. Doing this brings up the “SQL Server DBA Dashboard” HOME page, which is shown below:

Here you can see I have some charts and performance counters in the right pane of the dashboard, while the left pane contains a number of hyperlinks to some Server Wide and Database specific reports.

In the left hand pane there are 7 different high level charts showing: CPU utilization, Signal Wait Percentage, Tasks Wait Percentage, Tasks Waiting for Scheduler, Blocked Processes, SQL Agent Job Failures, Backups in Last 24 hours and Disk Space Utilization. Each one of these charts, with the exception of the “Signal Wait Percentage”, “Tasks Waiting For Scheduler” and ”Disk Space Utilization” can be used to drill down for more detailed information related to the specific chart. Also included on this Dashboard HOME page are some performance counter statistics.

The CPU Utilization chart show the amount of CPU used on the machine. If I mouse over the bars in the small graph on the “SQL Server DBA Dashboard” Home page, and then click on the left mouse button when the little hand is displayed I can bring up the following expanded CPU Utilization graph:

The CPU is broken down into two different stacked bars, where one portion of the bar shows “SQL CPU” usage and the other portion of the bar shows “Other CPU” usage (CPU used by processes other than SQL Server). The small graph and the above graph show the CPU consumption for the last 60 time intervals.

The next chart “Signal Wait Percentage” shows the amount of time that executing T-SQL statements (statements currently in a thread) are ready to consume CPU but have to wait because the CPU is currently working on another task. If the percentage of waiting for all threads exceeds 25% this could indicate a CPU bottleneck. There is no expanded/drilldown chart for this graph.

The “Tasks Waiting for Scheduler” chart show how many tasks are waiting to be scheduled as indicated by using the “sys.dm_os_schedulers” Dynamic Management View. This chart represents the number of tasks that are waiting for a run-able queue, meaning they are waiting for CPU. If you constantly see that tasks are waiting for a scheduler then your system might have a CPU bottleneck.

If there are blocked processes on the instance being monitor the “SQL Server DBA Dashboard” will show this in the “Blocked Processes” chart. If the pie chart shows any “red” slices then there are blocked processes on the instance being monitored. A detailed report of the blocked processes can be displayed by clicking on the pie chart.