Version 1458 is now available. The headline addition is the new tray icon and Windows service, which lets you set up historic monitoring on servers where SQL Server Agent isn’t available to schedule the collection. Alongside that, there’s a new least-privilege role for granting access to monitoring data without granting sysadmin, several alerting enhancements, new and faster QuickScan checks, and a set of additions to the Query Loading Ramp load testing feature.
New: Tray Icon and Windows Service
Database Health Monitor collects historic data on a schedule and writes it to the DBHealthHistory database. That data covers CPU, waits, IO, blocking, index usage, backup history, and more. Traditionally that scheduling has been handled by SQL Server Agent jobs on the monitored instance.
The Windows service, new in this release, is the alternative. It runs on a machine of your choosing, connects out to your SQL Servers, and performs that same scheduled collection itself on its own internal schedule of 30 second, 15 minute, and longer-interval jobs. The tray icon is the local companion to the service: a single tabbed dialog covering server configuration, collection history, and settings, so you can manage collection without opening the full application. The history tab supports filtering, F5 refresh, and a clear-history option.
The reason this matters: SQL Server Express has no SQL Server Agent, and there are plenty of other situations where Agent isn’t available to you. That might be a locked-down instance where you can’t create jobs, an instance where another team owns the Agent, or a managed platform with its own restrictions. In those cases there’s no way to schedule collection on the instance itself. Running the Windows service means historic monitoring can be set up against those servers anyway, with all of the collection scheduled and executed externally. One service can cover a group of servers, which also means you aren’t deploying and maintaining jobs on every instance you monitor.
Optional service watchdog
The service and tray icon also include an optional watchdog that detects a stopped SQL Server or SQL Agent and automatically restarts it. This was built with older SQL 2012 servers in mind, where an unnoticed stopped service can mean hours of missing data.
The watchdog is off by default and must be explicitly enabled to do anything. If you turn it on, keep one thing in mind: when you need to stop SQL Server for planned maintenance, stop the Database Health Monitor service as well, or the watchdog will start SQL Server back up for you mid-maintenance.
Least-Privilege Access with [DatabaseHealth_Viewer]
This came up repeatedly from customers who wanted to let a developer, a manager, or a junior DBA look at monitoring data without granting sysadmin.
Version 1458 adds a [DatabaseHealth_Viewer] role that sets the appropriate permissions on master, msdb, and dbhealthhistory. To assign it, use the new [AddDBHealthViewer] stored procedure. Read access to the health data, and nothing beyond it.
Alerting Enhancements
Alerting picked up several additions this release, aimed at making alerts easier to identify, filter, and follow up on.
- Long Running Query alerts are now combined into a single email rather than one per occurrence, and the email includes a count of long running queries along with the full query text.
- Alert emails now include the alert number and alert name in the subject line, which makes them easier to identify at a glance and easier to sort with inbox rules.
- A new Email Alerts Overview panel shows how many alerts are enabled on each server, so you can quickly see which servers are configured and which still need attention.
- Alert history improvements: a 1 day filter option, a selected timeframe that sticks after a refresh instead of resetting to the default, the ability to delete alert history, and a double-click option on the alerting reports to open the alert detail.
- More detail in the Blocking Query email.
Two alerting bugs were also fixed: alert #5008 was sending duplicate emails, and alert #5006 was ignoring both its custom exclusions and its “greater than” value filter.
QuickScan Additions and Speedups
- New check #190 alerts when DBCC CHECKDB has found allocation or consistency errors. If corruption has been detected, that should reach you the same day rather than at the next manual review.
- New check for entries in the DBHealthHistory.dbo.ErrorLog table.
- Urgent items now sort to the top of QuickScan results, so the items needing attention are the first ones you see.
- Check #66 (sysadmin owned databases) was slow on servers with slow Active Directory. It has been refactored to make far fewer AD calls, and the improvement on those servers is significant.
- Check #71 no longer calls suser_sname and is_srvrolemember, and check #13 is now excluded on RDS.
Other Fixes and Maintenance
- Fixed a bug preventing the technical debt report from being displayed.
- Fixed DBHealthHistory databases being created with AUTO_CLOSE ON, which can cause performance issues and temporary unavailability after the last connection closes.
- Fixed handling of servers where the ssisdb database exists but its SSISDB catalog does not.
- Fixed a possible null reference in CPU logging.
- Fixed the IO By Hour and Deadlocks By Hour database-level reports failing when connecting to a server by an alias other than its internal server name.
- Fixed a missing space in the message for QuickScan check #214, Agent Job Runs at Startup.
- Extended the timeout on the service’s 15 minute job, and added per-procedure log retention so the 30 second checks no longer push the 15 minute logs out too quickly.
- Made several historic database upgrade steps RDS safe.
- Added indexes and columns to the DBHealthHistory.dbo.ErrorLog table.
Query Loading Ramp: Load Testing Additions
The Query Loading Ramp feature received a substantial set of additions this release. If you haven’t seen it in action, we walked through it on the Stedman SQL Podcast: Query Loading Ramp demo.
Breaking point finder. Rather than picking a load level and running it, you can now let the tool ramp until it finds the point where the server stops keeping up. That number is the one you can take into a capacity or hardware conversation.
Live P95 latency trend chart. A new full-width chart panel on the third row tracks 95th percentile latency as the test runs, and the same data now appears in a latency section of the HTML report. Averages hide the queries your users actually notice; the 95th percentile doesn’t.
Peak Plans. At the moment the test drives CPU to its highest point, you can now display every query that was running along with its execution plan. When you’re working out what caused the server to fall behind, this is usually where the answer is.
Run-phase-only average query time. Ramp-up queries no longer pull the mean around. The average you see reflects the steady-state portion of the run.
Think time jitter. A new jitter percentage in the config dialog is applied per session, so simulated users don’t all fire in lockstep. Real workloads aren’t evenly spaced, and now the simulated one isn’t either.
Command-line / headless mode. Load tests can be launched without the UI, which makes them schedulable. Run one against a QA environment after each deployment and compare results over time.
Reporting. There’s a per-query result summary in the report, and you can export the UI results directly to an HTML report to share with a client or attach to a change ticket.
Getting Version 1458
Download the latest build from the Database Health Monitor download page and install over your existing version. The DBHealthHistory database will upgrade itself on first connection.
Stay on the lookout over the next few weeks for some exciting new announcements for Database Health Monitor. While this release was getting polished we have been working hard on some good looking new features that you will not want to miss.