[FEATURE] Add new Feature to Both lite and dashboard version by fetching top 20 queries for specific wait type

[nashalj]

Which component(s) does this affect?

• Full Dashboard
• Lite
• SQL collection scripts
• Installer
• Documentation

Problem Statement

when you see the monitoring of wait stats tab, i think it is good to have an option to see what queries actually caused this specific wait type in That time period, because it's hard to find out later.

Proposed Solution

i think by adding an option in wait stats tap, for example an context menu on wait types checkbox list to fetch wanted data is good enough to have the all in one place for investigation the problems,

Use Case

Add an context menu on wait types checkbox list to fetch desired Data in specific period of time

Alternatives Considered

No response

Additional Context

No response

[erikdarlingdata]

Thanks for the suggestion — this is an interesting idea but a tough one to implement well. The core challenge is that the data we collect today doesn't connect wait types to specific queries:

• Wait stats (sys.dm_os_wait_stats) are server-wide aggregates — they tell you what the server waited on, but not which queries caused it
• Query stats (sys.dm_exec_query_stats) have execution metrics (CPU, reads, duration) but no wait type breakdown
• Query Store runtime stats have per-query metrics but not per-query wait categories

So there's no join key today between "wait type X spiked" and "these queries caused it."

There are a couple of DMVs that could bridge this gap — sys.dm_exec_session_wait_stats gives per-session wait breakdowns that could be correlated with running queries, and sys.dm_os_waiting_tasks shows what each session is actively waiting on right now. We'd need to think carefully about how to collect and correlate that data in a way that's useful without being too expensive.

How would you envision connecting those dots? For example:

• Would periodic sampling of active sessions + their current waits be sufficient?
• Or are you looking for something more like "over the last 4 hours, these queries accumulated the most time in PAGEIOLATCH_SH"?
• Do you use Query Store on your servers? (SQL 2017+ has sys.query_store_wait_stats which groups waits into categories per query, though not individual wait types)

Understanding your workflow would help us figure out the right approach.

[erikdarlingdata]

Correction to my previous comment — we already collect from sys.dm_os_waiting_tasks in both editions (Full and Lite), which gives per-session wait type data. So the building blocks for this correlation are closer than I suggested. The question is how best to surface it in the UI and whether the current collection granularity is enough for your use case.

[erikdarlingdata]

Hey @nashalj — just checking in on this. We asked some follow-up questions above about how you'd want to use this feature but haven't heard back. If you have a chance to reply with your use case, that would help us figure out the right approach. Otherwise we'll close this out in a couple weeks to keep the issue list tidy. You're always welcome to reopen if you want to revisit later.

[erikdarlingdata]

Implementation Progress

What's done

• Right-click context menu on wait stats chart → "Show Queries With [WAIT_TYPE]" drills into a ±15 minute window around the clicked data point
• Wait type classification engine (Resource, Chain, Uncapturable, Fallback categories)
• Blocking chain walker for LCK_M_* waits (shows head blockers, not victims)
• Both Lite and Dashboard implemented
• Builds and launches clean

Issues found during testing

Data collection gap for short-duration waits: Wait types like LATCH_EX, SOS_SCHEDULER_YIELD, and PAGEIOLATCH_* are too brief to appear in query snapshots (sp_WhoIsActive / dm_exec_requests capture point-in-time state). Wait stats accumulate thousands of microsecond waits into large ms/sec values, but at snapshot time the query is already on a different wait or running. Result: drill-down shows empty for the most common wait types.

Fix: For short-duration waits, don't filter by wait type — show all active queries in the time window sorted by the correlated metric (CPU for SOS_SCHEDULER_YIELD/LATCH_EX, reads for PAGEIOLATCH_, etc.). Only long-duration waits like LCK_M_ should filter by wait type.

Remaining polish

• Classification fix: short-duration waits should show ALL queries sorted by correlated metric, not filter by wait type
• Clear filter indicators in the summary bar (time window, filter mode)
• Column parity with Active Queries grids (missing login, host, program, open tran count, context switches, etc.)