Database Health Beta 9.3 Released Today

Today I had the pleasure of releasing Beta 9.3 of the Database Health Monitor. It has been a whole week since the last beta update, and here is the outline of the new features and bug fixes.

Download at DatabaseHealth.com

New Features

  • New report, Partitioned Tables. This new report shows all of the partitioned tables in a database. Double clicking for drill down shows the new Partitioned Tables Advisor that shows details on the number of partitions, rows in each partition and the range for each partition.
  • The entire color scheme has been updated, not so blue.
  • Added double click action on the one time use query report.
  • The duplicate indexes chart has been migrated over to the new report format and colors.

Bug Fixes

  • Performance improvements and bug fixes on the table size advisor. It is still a bit slow on really huge tables, but it works well on most.
  • Fixed the drilldown when double clicking the chart view on the disk space report.
  • Improved exception handling on the needs parameters report.
  • Increased query timeout on the index fragmentation report.
  • Improved the column layout and sizing on the following reports:
    • Missing Indexes Report
    • Unclustered Tables Report
  • Improved error handling on all background threads when attempting to connect to a database. Better handling when the connection fails.
  • Fixed a bug on the plan cache panel not linking to the right report.
  • The enter key now does the same thing as double clicking for drilldown on all grids in the application.

Other Notes

  • Based on improved crash reporting in beta 9.1 and 9.2, I was able to learn more about the common problem areas in the application. This lead to improved exception handling in many of the areas where a database connection is established, along with better error messages when a connection fails for some reason.

 

The Partitioned Tables Report was a new addition to the Database Health monitor with this release.

The partition tables report shows a list of all the partitioned tables in a database. As shown here, there is just one partitioned table in this test database.

PartitionedTables2

Double clicking on the table [dbo].[orders] brings up the Partitioned Table Advisor which shows the number of rows in each partition, the percentage of the table represented by that partition, and the range items for the partition. In this example the partitions are split over date ranges.

 

 

PartitionedTablesAdvisor

I plan to keep the updates coming for some time.  Every week I am learning something new to help make the product even stronger.

Download at DatabaseHealth.com

-Steve Stedman

Now that Database Health Beta 2 is out, whats next…

Now that Beta 2 of the Database Health Project is out, and people are using it, I am going to focus my attention on 2 areas.

  1. Fixing any bugs that are reported in Beta 2 of the Database Health Project.
  2. Adding new features, specifically related to offline monitoring or history data.

Currently all of the reports in the database health project report on what SQL Server can tell you right now.  What happens when somebody reports that the server was slow at 4:00pm yesterday, how can you track that down. Well, the plan if all goes well with the next beta of the Database Health Project there will be a way to configure a database on a monitoring system that will check up on your SQL server occasionally and record any problems.  This will allow you to be able to find out what query or what wait type caused the system to be slow at some point when you weren’t watching the database.

This is going to be very cool!

So far I have the core tracking and monitoring features working, now I just need to work on the reporting so that it is quick and easy to understand the data.

For now, please try out Beta 2 of the Database Health Project, it is a free download.

-Steve Stedman

Finding the contents of the Plan Cache

I often get asked “how do I find the contents of the SQL Server Plan Cache?”   My first response is just use the Database Health Reports and you can get this through a nice user interface.  But if you still want to get at the Plan Cache contents, I use the following query.

SELECT UseCounts,
       RefCounts,
       CacheObjtype,
       ObjType,
       DB_NAME(dbid) as DatabaseName,
       SQL,
       sqlbytes
  FROM sys.syscacheobjects
 WHERE dbid = db_id();

Again, the Database Health Reportsgives you a much better UI presentation of the conents of the plan cache.

Enjoy the query and please try out the Database Health Reports.

Beta 2 of the Database Health Reports Released

Beta 2 of the Database Health Reports has just been released.  The new Beta contains several bug fixes, and several new features.  Here is a breakdown of what is new or changed since the last beta.

New Features

Bug Fixes

  • Performance tuned the Page Reads and Page Writes reports which were having some difficulty on larger databases.
  • Fixed bug with the Plan Cache Report which was only showing data for the master DB originally, now it shows for the appropriate database.
  • Performance tuning on the Table Size Advisor dialog. There is a lot going on with this, it may take some time, but it is faster than before.
  • Performance tuning on the Fragmented Indexes Report.
  • Improved error handling on connection failures such as invalid password or a bad username.
  • Removed backup status for TempDB as this really didn’t make any sense. Why would anyone backup TempDB.

Download the free Beta 2 version and give it a try.

Finding tables that don’t have a clustered index.

Although clustered indexes are not required, and there are many cases where you should not use a clustered index, quite often clustered indexes can be overlooked when creating a table.  This is actually more likely if you are inheriting a database from someone else who didn’t know enough about indexes.  In these cases adding the right clustered index can dramatically increase performance on queries.

As part of the Database Health Reports, I have added the Unclustered Tables report to report on those tables in your database that do not have a clustered index on them.  From that point you can analyze each of those tables and determine if it should be clustered.

Take a look give it a try.   This report will be in Beta 2, which will be available for download on October 1st 2012.

It is finally here. Beta 1 of the Database Health Reports for SQL Server.

In October last year I started work on the Database Health reports project.  It started out as a series of SSRS reports that I released and updated in December 2011 to March 2012.  After that it became clear that the SSRS reports weren’t going to do what I was looking for so I started on the Database Health reports version 2.0 which migrated all the original reports into an application and added much more functionality than I had before.

Today (September 1, 2012) I have released the Beta 1 of the Database Health reports project.

Please give it a try and let me know how it works for you.  See the Database Health website for more details on this project.

Here is a screenshot of the database overview page.

Enjoy!