Move past “something is under pressure” to “this database, this application, this query is the cause.” This guide is for teams who already use SQL Diagnostic Manager every day but haven’t yet turned on dimensional wait analysis — breaking the same wait data down by database, application, user, session, and query instead of reading it as a single instance-level number.
Why This Matters
SQL Diagnostic Manager already collects wait statistics continuously and agentlessly for every monitored instance. Most teams only ever look at the instance-level aggregate; a useful health check (“something is under pressure”) that can’t answer the more useful question: what, specifically, is causing it.
Because SQL DM is already collecting this data, the dimensional breakdown isn’t a separate feature to license or configure; it’s a different lens on data you’re already gathering. Pivoting the same wait numbers by database, application, user, session, and query turns wait statistics from a symptom indicator into an actual diagnostic path, and lets a DBA hand a finding directly to the owning team instead of a vague “the database is slow” ticket.
When to Use It
Situation
What to do
General “the server is slow” reports with no clear cause
Start at the instance level to confirm a genuine wait-time problem and identify the dominant wait type.
A shared instance hosts multiple databases
Break the same wait type down by database to isolate which database is actually under pressure.
Two teams are debating whose system is at fault
Break it down further by application and user to identify which client, service account, or team is generating the load.
You need a specific, assignable fix, not just a diagnosis
Drill to the session or query level, using duration and count together rather than either alone.
Proactive capacity or consolidation planning
Review which applications or databases are the heaviest wait-time consumers on a shared instance on a regular cadence, not just during incidents.
Before You Set Up
Query Monitor and Query Waits are off by default. Dimensional wait analysis depends on query-level wait collection, which is enabled per monitored instance on the Wait Monitoring tab; it isn’t on out of the box.
Choose a collection method deliberately. DMV-based collection is the recommended default for most environments; it’s the most efficient option. Extended Events or Query Store are available as alternates on servers where they fit existing practice better, but enabling Extended Events for Query Wait collection is known to raise CPU utilization on the monitored server dramatically, so treat it as an exception, not a default.
Decide how long to leave it running. IDERA’s guidance for the related Wait Monitoring tab is to enable it for the specific diagnostic window you need and disable it once the diagnosis is complete; the same discipline applies to leaving Query Waits collection on indefinitely versus turning it on to investigate a specific issue.
Filter out the noise before you rely on the view. The Advanced Query Wait Statistics Configuration window lets you exclude specific applications, databases, and SQL text, and exclude SQL DM’s own queries; from the collection, plus cap the number of rows collected at once. Exclude always takes precedence over Include.
The Query Wait Statistics report needs data in hand first. Running the report requires Query Monitoring to already be enabled and enough query data collected for the period you’re reporting on; it isn’t useful as the very first step.
Part 1: Step-by-Step in the Desktop Console
Four phases. Phase 1 is one-time configuration per instance. Phase 2 is optional cleanup. Phase 3 is the day-to-day dimensional breakdown workflow. Phase 4 is the recurring report to run proactively.
Phase 1: Enable Query Monitor and Query Waits
Do this once per monitored instance, before you need the dimensional breakdown for an active incident.
In the SQL Diagnostic Manager desktop console, find the instance in the Servers sidebar.
Right-click the instance and select Properties.
In the Monitored SQL Server Properties window, click the Wait Monitoring tab.
Enable Query Monitor and Query Waits collection for the instance. This is what powers the Query Waits view and the Query Wait Statistics report; without it, only the instance-level wait totals are available.
Choose the collection method (DMV-based is the default and most efficient choice for most environments; see the consideration above before selecting Extended Events).
The Wait Monitoring tab of the Monitored SQL Server Properties window, where query-level wait collection is enabled and the collector can be viewed, started, stopped, and refreshed.
Phase 2: Filter What You Collect (Optional)
Do this if the environment is noisy; a shared instance with many databases, applications, or ad hoc query traffic: so the Query Waits view stays focused on what matters.
From the Wait Monitoring tab (or from the Configure button on the Query Waits view), open Filter Options.
Enter the applications, databases, or SQL text to exclude from collection, separating multiple items with a semicolon (;) and using a percent sign (%) as a wildcard.
Optionally exclude SQL Diagnostic Manager’s own queries from the results, and set a limit on the number of rows collected at a time.
Click OK. Remember that Exclude always takes precedence over Include when both are set.
Phase 3: Break Down Wait Data by Dimension
This is the workflow to run during an active investigation, once Query Waits collection has been on long enough to gather representative data.
Select the appropriate SQL Server instance, click the Queries tab, then click Query Waits.
The view opens showing the queries on the instance with the longest wait times, by default charted over time.
Use the chart at the bottom of the view to pivot the same wait data by Statements, Applications, Databases, Clients, Sessions, and Users without leaving the view or exporting to another tool.
Toggle between viewing waits as they occur over time or by total duration, color-coded by query, to separate a single runaway query from evenly distributed load.
Filter by wait type category: Backup, I/O, Lock, Memory, Non-I/O Page Latch, Non-Page Latch, Transaction Log, or Other; to focus on the category driving the current incident.
A practical pattern teams follow once they’re comfortable with the view:
Confirm a genuine wait-time problem exists at the instance level and note the dominant wait type.
Break that wait type down by database to isolate which database on a shared instance is under pressure.
Break it down further by application and user to identify which client, service account, or team is generating the load.
Drill to the specific query or session responsible, using duration and count together.
Hand the finding: database, application, user, and query, together, to the owning team.
Recheck the same wait signature after the fix ships, to confirm the root cause was addressed rather than masked.
Phase 4: Run the Query Wait Statistics Report
Use this for a routine, proactive check rather than only reacting to incidents.
Open the Server analysis reports and select Query Wait Statistics.
Select the wait type categories to analyze; Backup, Excluded, I/O, Lock, Memory, Non-I/O Page Latch, Non-Page Latch, Transaction Log, or Other.
Confirm Query Monitoring has been enabled and has collected sufficient data for the period you’re reporting on; the report depends on that data already existing.
Run this report on a routine basis, such as weekly, to proactively surface potential query-driven performance issues before they become incidents.
Part 2: Step-by-Step in the Web Console
The web console’s single-instance dashboard mirrors the desktop Queries tab, so the same dimensional breakdown is available without opening the full desktop client.
Open the single-instance dashboard for the server and click the Queries tab.
The Queries tab contains four sub-tabs: Signature Mode, Statement Mode, Query History, and Query Waits.
Click Query Waits to see the queries on the instance with the longest wait times; the same view and fields as the desktop console’s Query Waits view.
As of SQL Diagnostic Manager 14.1, the web console’s Query Monitor and Query Waits screens carry all of the filter options available in the desktop client, with the exception of the time range option.
The single-instance dashboard’s Queries tab in the web console, showing the Signature Mode, Statement Mode, Query History, and Query Waits sub-tabs.
Reference: Wait Type CategoriesThese are the wait type categories available when filtering the Query Waits view or running the Query Wait Statistics report:
Backup
I/O
Lock
Memory
Non-I/O Page Latch
Non-Page Latch
Transaction Log
Other
Excluded (report only)
Source Documentation
This guide draws directly from the following IDERA wiki and blog sources:
