A health dashboard should answer a few urgent questions before anyone starts scrolling. A simple dashboard from DMVs can show activity, waits, storage pressure, and recent backup state without becoming another workload.
Pick Questions Before Building a Dashboard From DMVs
A useful dashboard starts with operational questions: Is the instance reachable? Are databases online? Are sessions blocked? Did backups complete? Is storage capacity changing? Choose a small set that leads to action. A grid of every DMV value is less helpful than a few labeled measures with clear time windows. A dashboard from DMVs should help someone decide where to look next.
I start with the on-call DBA’s first five minutes. If a panel cannot change a decision in those minutes, it belongs on a detail page or in a scheduled report. Ask what the reader would do when a value is red. If nobody can answer, the panel needs a better definition or no place on the screen.
Collect Active Requests Lightly
sys.dm_exec_requests gives a snapshot of current work. Count user requests and blocked requests without retrieving every SQL text or plan on each refresh. Text and plans belong in drill-down views. A dashboard that pulls large XML plans every few seconds can create its own performance complaint.
The query below returns a small current summary. It is not a historical trend. Capture the timestamp and store only the columns you need. If the value rises, inspect individual requests with a separate query.
SELECT SYSDATETIME() AS sample_time,
COUNT(*) AS active_requests,
SUM(CASE WHEN blocking_session_id > 0 THEN 1 ELSE 0 END) AS blocked_requests
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID;Show Database State
A database outside ONLINE deserves attention, but its expected state matters. A planned restore, an intentionally offline archive, and an unexpected SUSPECT database are not the same event. Let the dashboard list the database and state rather than displaying only a count. Keep approved maintenance exceptions in the monitoring configuration, with an expiration date.
I have seen a red count ignored for months because one old database was intentionally offline. That teaches everyone to ignore the panel. Separate expected from unexpected states and retain the underlying row. The simple query is cheap; the interpretation requires the operations record.
SELECT name, state_desc, user_access_desc
FROM sys.databases
WHERE state_desc <> N'ONLINE'
ORDER BY name;Include Recovery Evidence
Show the latest backup times by database and type, but compare them to the actual recovery plan. A database in FULL recovery needs appropriate log backups for point-in-time recovery. A development database can have a different schedule. The dashboard should flag a missed expected backup, not an arbitrary age that fits no service.
I keep backup job failure and restore-test status separate. A recent backup row is not a restore test. If you have no tested restore evidence, state that clearly. The dashboard should not turn partial evidence into a green recovery badge. Operations teams make better decisions when the limits are visible.
Treat Waits as Trends
Cumulative wait statistics grow since the last reset or service start. A raw total is poor for a live dashboard. Sample and calculate deltas over a known interval, excluding categories your team has reviewed as background waits. Keep the interval and reset time in the data. Current waiting tasks can provide a live view for an incident, but a fleeting wait is not a trend.
I have watched dashboards rank a wait type simply because the server had been up longer. That is a calendar, not a diagnosis. Compare rates and workload context. An increase in a wait category should lead to a targeted investigation, not an automatic setting change.
Refresh a Dashboard From DMVs at a Sensible Pace
Set refresh intervals by how quickly the underlying fact changes and how expensive collection is. Active requests can refresh more frequently than backup history. File allocation and configuration change slowly. Reuse snapshots rather than running the same query separately for every viewer. Measure the dashboard’s own query cost on a representative instance before broad rollout.
I keep collection read-only and limit row counts. If the dashboard becomes unavailable, the instance should not depend on it for core operations. Alerts for critical failures need an independent route. A pretty page that must stay open to detect a failed backup is a fragile monitoring design.
Design Useful Drill-Downs
Each headline should lead to a query or page that explains the underlying rows. A blocked-request count should open the sessions and blocker IDs. A database-state alert should open the database names and recent error messages. A backup panel should show the specific missing type and schedule. Without that path, the dashboard is an alarm bell with no address.
I label units and sample times prominently. CPU percentage, milliseconds, pages, and megabytes cannot be compared when the units are hidden. The goal is clear, calm reading under pressure. Nobody should need to reverse-engineer a metric while an application owner waits on the phone.
Check Permissions and Data Handling
DMVs require appropriate permissions, which vary by SQL Server version and deployment. Grant the collection identity the least privilege needed for the chosen queries. SQL text and session details can contain sensitive data; avoid collecting them for the main board unless necessary. Protect stored history and define retention.
I test the dashboard from DMVs using its service identity, not my administrator account. A panel that works only for sysadmin is unfinished. If one query fails, show a helpful error state and keep other panels visible. A blank green box is the worst failure mode because it looks like success.
Review the Dashboard From DMVs With Users
After a few incidents, ask which panels helped and which ones sent people in circles. Remove or revise metrics that do not lead to action. Update thresholds when workload patterns change. Test that an expected maintenance event does not create confusing alerts. The dashboard is an operational tool, so it should evolve with the service.
What would your team need to see in the first minute of a slowdown? Put that evidence first. Keep long analysis elsewhere. A small dashboard that earns trust beats a wall of charts that everyone has learned to walk past.
Related reading on this blog: AI Built SQL Server Wait Statistics Dashboard in Minutes and DMVs to Detect Performance Problems in SQL Server: Notes from the Field #135.
A DMV dashboard is not a wall of numbers, it is a short path from signal to action.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
