Wait stats: TOP 20 cap drops poison waits and usual suspects

[erikdarlingdata]

Problem

Both Dashboard and Lite have a wait type selection system that tries to default-select three categories:

1. Poison waits (3): THREADPOOL, RESOURCE_SEMAPHORE, RESOURCE_SEMAPHORE_QUERY_COMPILE
2. Usual suspects (6+): SOS_SCHEDULER_YIELD, CXPACKET, CXCONSUMER, PAGEIOLATCH_SH, PAGEIOLATCH_EX, WRITELOG, plus PAGELATCH_* prefix matches
3. Top 10 by total wait time

That's ~12+ waits before the top 10 even starts. The app-side picker (GetDefaultWaitTypes) hard-caps at 20 total, so the top 10 gets squeezed down to 8 or fewer.

Dashboard-specific issue

The SQL view report.top_waits_last_hour has TOP (20) hardcoded. This means:

• Only 20 wait types ever leave the database
• If poison waits (e.g. THREADPOOL) are low-volume, they're not in the top 20 by wait_time_ms and never reach the client at all
• The app-side picker can't select what it never received

Lite-specific issue

Less severe — the collector stores all waits and GetDistinctWaitTypesAsync has no limit, so the picker sees everything. But the display query GetWaitStatsAsync has LIMIT 50 and the default selection cap is still 20.

Proposed fix

Dashboard

• report.top_waits_last_hour: Change from simple TOP (20) to a UNION approach:

  -- Top N by wait time
  UNION
  -- Poison waits (always include if they have any delta)
  UNION
  -- Usual suspects (always include if they have any delta)

  Or just bump to TOP (50) and let the client-side picker handle selection.

Both apps

• Bump the default selection cap from 20 to 25-30
• Or restructure: always guarantee poison + suspects are selected, then fill remaining slots (up to cap) from top N

Files involved

• install/47_create_reporting_views.sql — report.top_waits_last_hour view (TOP 20)
• Dashboard/Helpers/TabHelpers.cs — GetDefaultWaitTypes() (cap 20)
• Lite/Controls/ServerTab.xaml.cs — GetDefaultWaitTypes() (cap 20)
• Lite/Services/LocalDataService.WaitStats.cs — GetWaitStatsAsync() (LIMIT 50)