Database Health Monitor Version 2 Almost Done

Download PDF

Over the last 4 years I have been working on the Database Health Monitor. I am about a week away from releasing Version 2, which will finally be out of the beta process. In preparation for the release I am sharing some of the history of this program.

In 2011 version 1 was released as a set of my favorite monitoring queries that had been formatted and made available through SSRS. As people started to try it out in late 2011 and early 2012, I quickly discovered that the process for distributing shared queries via SSRS reports wasn’t very easy for the people trying to use them. Many people quickly discovered the difficulty of installing these reports. The set of reports looked something like this.

OnetimeUseQueries

 

There were 10 reports that were linked from the top panel in the SSRS user interface. Although SSRS has some great features, it was challenging to switch database connections and monitor multiple servers.

In April of 2012 I gave up on the concept of using SSRS reports, and converted all the queries into a windows application and started enhancing the program. In September of 2012, I finally had the Beta 1 release of Database Health Reports out. It was a bit rough in the beginning, but from 2012 to 2015 through a series of almost 20 beta releases labeled names like Beta 1, Beta 8.2, Beta 10.1, and so forth I kept enhancing the program. Here is an early screenshot of somewhere around Beta 1 or Beta 2.

IndexFragPanel2

Late in 2012 I release a new feature in the program called “Historic Reporting” which installs a small agent on a monitoring server and keeps track of wait stats, cpu, plan cache and other stats over time. This is logged to a small database that allows you to go back to a point in time and see what was happening on your SQL Server. So when it is Thursday afternoon and someone says “The database was really slow on Tuesday morning”, you have the ability to see what was running, and what was actually going on at the point it time is was reported to be slow.

In 2014 I stumbled a bit and lost interest. It didn’t seem like many people were interested in the program, until the beginning of October when the previous beta expired. Boy did I get a lot of email from people who wanted to use the program and could no longer use the program since the beta had expired and there was no new beta. This was the catalyst that got things going again. I quickly released another beta to keep these current user base happy, and moved on to adding more features, fixing bugs, and driving towards a completing version 2.0.

The name was changed from Database Health Reports to Database Health Monitor in 2014.

From October 2014 to June 2015 I added many new features, fixed bugs and made the program much more robust. I tried out some things like crowd funding, which turned out to not work so well. If you were one of those people who contributed to the total of $180 that I received from the crowd funding campaign, thank you. That wasn’t enough to fund the development through crowd funding, so I shifted gears, and decided to make it a free program available to the SQL Server Community to give back for everything I have learned from others.

After deciding to make this a free product, I worked through 2015 until now to get it ready for final release, and continued to improve the program with new reports, new icons, and overall improvements. When I say free, I really mean free.

New in the full (non beta) release of Version 2.

Some of the most recent additions that will be part of the Version 2 release include the following:

  • Removed Beta 3 month expiration.
  • Added the Database I/O overview panel.
  • Added the I/O by database instance level report.
  • Database overview reports on databases set to autoshrink.
  • Added a check for those using Veeam backups. There is a configuration where the Veeam backup breaks the log restore chain. This is now shown in the errors and warnings page.
  • Filtered snapshot backups from the backup restore chain.
  • Backup report now differentiates copy only backups from normal backups.
  • Added ctrl+a for select all on the historic query advisor.
  • Fixed a bug in the log restore chain script in the backup report.
  • Added the database CPU heat map to show the average CPU load hour by hour for the last 7 days.
  • Added the instance level linked servers report to display linked servers.

My favorite of those is the CPU heat map, which allows you to see hot spots over the last week and to determine when your SQL Server is under the most CPU contention, or when it is the most idle. The report is called “CPU Load by Hour of Day” and looks like this.

CpuHeatMap

 

With this report you can easily figure out where the most load is from OLTP, or ETL loads, or whatever may be causing a cpu load on your server. For each hour of the day you can see the average CPU load.

So stick around and be ready to try out the full release of version 2 in the next week after the long awaited 5 years of development.  Enjoy.

 

Detailed Timeline:

  • June 1, 2015 – Beta 10.2 Released. Improved performance on historic reporting. Allows for multiple connections to different reporting database. Automatically detects if a server is already configured for historic reporting.
  • May 6, 2015 – Beta 10.1 Released. Removed the crowd funding, and added a couple new reports, the Error Log Report and the Suspect Pages Report. Many bug fixes.
  • January 21, 2105 – Beta 10 Released.  New icon, crow funding links, added filegroup and partitioning key into the partitioning report. Many bug fixes.
  • December 21, 2014 – Beta 9.4 Release, improved overall threading and background processing.
  • December 14, 2014 – Beta 9.3 Release, more bug fixes and more new Partitioned tables reports.
  • December 7, 2014 – Beta 9.2 Release, more bug fixes and more new reports.
  • November 30, 2014 – Beta 9.1 Release, more bug fixes and more new reports.
  • November 23, 2014 – Beta 9 Release, bug fixes and a couple of new reports.
  • October 5, 2014 – Beta 8.2 Release.
  • January 19, 2014 – Beta 8.1 Release – bug fixing.
  • December 29th, 2013 – Beta 8 Release – most of the charts rewritten, eliminating extra components.
  • Sept 5th, 2013 – Second Release of Beta 7 with a new feature to filter technical debt by debt type.
  • Sept 2nd, 2013 – Release of Beta 7 including SQL Technical Debt Analysis, and many performance improvements.
  • July- August 2013 – Working on Beta 7 – Adding Technical Debt Reporting.
  • June 3rd, 2013 – Released Beta 6 of the Database Health Reports.
  • May 21st, 2013 – 600th download of the Database Health Reports. Going strong, thanks everyone.
  • April 29th, 2013 – 500th download of Database Health Reports, lots of great feedback.
  • March 27th, 2013 – The 400th download of the Database Health Reports.  Thanks everyone who is using the product.
  • March 3rd, 2013 – Beta 5 Released with a new report for Big Clustered Indexes.
  • February 17th, 2013 – The 300th download of the Database Health Reports.  Thanks everyone who is using the product.
  • January 10th, 2013 – Beta 4 of the Database Health Reports released.
  • November 18, 2012 – Release of the historic waits reporting, in Beta 3.
  • November 1, 2012 – Cleanup on the historic waits reporting, preparing for Beta 3.
  • October 29, 2012 – Refined the 5 dimensions of drilldown, making it work smoother.
  • October 28, 2012 – Overall performance tuning on the database health application, getting cleaned up for Beta 3.
  • October 27, 2012 – Added the 5 dimensions of drill down into the historic charting.
  • October 24, 2012 – Spending some time fixing a few reports that crashed on SQL Server 2005.  I think we are good with SQL Server 2005 now.
  • October 20, 2012 – Added wait time monitoring, focusing on the queries that have the most waits.
  • October 14, 2012 – Finally have the preliminary historic reporting working.
  • October 11, 2012 – Major re-design of the application to focus now on historic reporting, rather than just the real time snapshot.
  • September 30, 2012 – Release of Beta 2.  First download 15 minutes after posting.
  • September 23, 2012 – Additional feedback from beta testers.  More fixes and adding the Connections Advisor as part of Beta 2.
  • September 4, 2012 – First round of feedback from the beta testers.  So far so good, doing what I can to fix everything and add new features.
  • September 3, 2012 – Started working on the new features that will eventually be part of the Beta 2 release of the Database Health reports application.
  • September 2, 2012 – First International download of the Beta 1 release of the Database Health reports application 20 minutes after release.  It was downloaded from France.
  • September 1, 2012 – First download of the Beta 1 release of the Database Health reports application 20 minutes after release.
  • September 1, 2012 – Beta 1 release of the Database Health reports application.
  • August 2012 – Final testing on the Database Health reports application and final prep for the beta release.
  • July 2012 – Started testing on the Database Health reports application internally and preparing for the beta release.
  • April 2012 – Gave up on the SSRS version of the database health reports and began working on an the full Database Health application.
  • March 18, 2012 – Added 2 new reports Top Cache Queries and Top CPU Queries.
  • March 16, 2012 –  A little more clean up and final polishing for SQL Saturday Vancouver tomorrow.
  • March 11, 2012 – Minor UI Fixup.  Added included columns into the Exact Duplicate Indexes report.  Overall cleanup preparing for the SQL Saturday presenation of the SQL Server Health Reports next weekend.
  • March 03, 2012 – Major UI Fixup.  Standard header on all pages, linking from any report to any other, ability to change the database from within the report, Updated help links, added Top IO report, fixed several bugs.
  • January 08, 2012 – Fixed some layout issues in the Index Fragmentation Report, and cleaned up some general layout issues in the rest of the reports.
  • December 10, 2011 – Fixed a couple of bugs in the backup set report with the line sizes in the chart.  Added the newStatistics report.
  • December 08, 2011 – Released the backup set report
  • December 07, 2011 – Released the database size report
  • December 03, 2011 – Added  CPU Utilization Report
  • November 27, 2011 – Added Duplicate Index Report and Exact Duplicate Index Report
  • November 26, 2011 – Added Long Running Queries Report
  • November 19, 2011 – Created Initial Version using SSRS reports
  • February to October 2011 – Initial planning – determining what queries would be useful in the Database Health Reports.
Tagged with: , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.