SQL Server DBA Dashboard

The “SQL Server Agent Failures” chart shows a summarized view of the number of SQL Agent job failures in the last 7 days. From the my “SQL Server DBA Dashboard” HOME page above you can see that there is a job failure that occuring on 10/28 (indicated by the red bar). By clicking on this red bar the report below is displayed. This report can be used to determine more about what jobs failed on 10/28 and why.

Here you can see the detailed information about my job failure. From this report you can see that stored procedure “xxx” could not be found. Since this report contains more information than can be displayed on my screen a scroll bar is shown at the bottom. This scroll bar can be used to scroll this report to the right so you can see the additional information in the report.

The “Backups in Last 24 Hours” chart has a large chunck of red in the pie chart. This indicates that some databases didn’t get backed up in the last 24 hours. When I click on the “Backups in Last 24 Hours” pie chart on the HOME page of the “SQL Server DBA Dashboard” I get the following report:

This report shows the last backup date and backup type for each databases. Those that have not been backed up in the last 24 hours are display with “red” text. As you can see I have not backed up the AdventureWorks, model, and Test databases reciently, plus the model database has never been backed up.

The last chart on the “SQL Server DBA Dashboard” HOME page shows disk space utilization across all the disk drives on the server where the instance is installed. This chart summorized the amount of free space and used space for each disk drive in a single chart. This information gives you a quick heads up on your disk space utilization so you can determine if any corrective action is needed.

The last item on the right hand pane of the “SQL Server DBA Dashboard” HOME page is a set of “Performance Statistics”. These performance statistics give you a high level view of I/O, memory, CPU and disk space usage. You can use these performance statistics as high level indicators to determine if there are any performance issues you should be concerned about.

On the left pane of the “SQL Server DBA Dashboard” a number of different reports are provided with a click of the mouse. These reports fall into two different categories: Server and Database. “Server” type reports can be found in within the “Server Wide Information” section, where as the “Database” type reports are located in the “Database Information” section. Below is a list of the current reports supported by version 1.0 of the “SQL Server DBA Dashboard”:

Server Information Reports:

  • Top 100 Statements by IO
  • Top 100 Statements by CPU
  • Top 100 User Stored Procedures by Use Count
  • Top 100 Statements by Duration
  • I/O Usage by Database
  • Last Backup for Each Database
  • Failed SQL Agent Jobs
  • Failed SQL Agent Jobs in Last 24 Hours
  • SQL Agent Jobs Run in the Last 24 Hours
  • SQL Agent Jobs Run in the Last Week
  • SQL Agent jobs Run in the Last Month
  • SQL Agent Job Execution Statistics
  • Missing Indexes
  • Instance Information
  • Database Information reports:

  • Record Counts by table
  • Tables without Primary Keys
  • Tables without Clustered Indexes
  • Tables Without any Indexes
  • Unused Indexes
  • List of Backups
  • ·         Index Fragmentation

    Since my dashboard is built using the “Custom Report” feature of SQL Server 2005 the “SQL Server DBA Dashboard” code is able to determine what node within the Object Explore you are on when the the dashboard is opened up. Depending on the node you are on the “Database Information” item may or may not be displayed. If you are bringing up the “SQL Server DBA Dashboard” from any one of the different items within a database node then the “Database Information” will be display, otherwise it will not be displayed. I’m not going to go through all these reports but lets look at a couple just to give you a feel for how the reports look and how to navigate back and forth between the reports and the HOME page of the dashboard.

    You can bring up any report by just clicking on the hyperlink in the left pane. Below is the report behind the “Top 100 Stmts by I/O” hyperlink:

    Here you can see the first three statements based on “Tot IOs”. Note that each column in this report can be used to reorder the report rows. This can be done by just clicking on the column heading that you want the rows to be sorted on. The first click will sort the rows in ascending order, and then if you click the column again they will be sorted in descending order. As you can see, the above report contains additional columns to the right, and additional rows below the second row. To see these additional columns or row use the scroll bars on the bottom and left side of the dashboard. Most of the “SQL Server DBA Dashboard” reports support sorting, and all reports will have scroll bars should the output be larger than a single page.

    To navigate back to the HOME page of the dashboard you can either use the back arrow in the tab of the report or click on the “HOME” hyperlink in the top left hand corner of each report. Note that there seems to be a bug when you use the back arrow. If you have multiple query tab windows open and also have one of the “SQL Server DBA Dashboard“ custom reports open, then the back arrow doesn’t always take you to the “SQL Server DBA Dashboard” window, sometimes instead it will take you to one of the other tabbed query windows. This small feature is why I put a “HOME” hyperlink on each one of the dashboard reports. This “HOME” hyperlink will redisplay the “SQL Server DBA Dashboard” HOME page.


    Leave a comment

    Your email address will not be published.