Skip to content

Added exclusions for sp_server_diagnostics and WAITFOR wait types to GetLongRunningQueriesAsync() method.#362

Merged
erikdarlingdata merged 9 commits intoerikdarlingdata:devfrom
HannahVernon:dev
Mar 2, 2026
Merged

Added exclusions for sp_server_diagnostics and WAITFOR wait types to GetLongRunningQueriesAsync() method.#362
erikdarlingdata merged 9 commits intoerikdarlingdata:devfrom
HannahVernon:dev

Conversation

@HannahVernon
Copy link
Contributor

What does this PR do?

Adds exclusions to GetLongRunningQueriesAsync() to exclude sp_server_diagnostics and WAITFOR wait types. Adds parameters to GetLongRunningQueriesAsync() for future use to allow precise control over exclusion of those wait types, along with an optional parameter to control the maximum number of long running queries returned to the caller.

Which component(s) does this affect?

  • [✔] Full Dashboard

How was this tested?

Tested against SQL Server 2022 with sp_server_diagnostics wait types caused by Windows Server Failover Cluster monitoring, and via a manual query with WAITFOR DELAY '00:30:00';

Checklist

  • [✔] I have read the contributing guide
  • [✔] My code builds with zero warnings (dotnet build -c Debug)
  • [✔] I have tested my changes against at least one SQL Server version
  • [✔] I have not introduced any hardcoded credentials or server names

…_DIAGNOSTIC wait types, and WAITFOR wait types.
…to allow future configurability on the number of long-running queries returned. Added optional parameter to control display of WAITFOR types in future.
…clusions

Feature/long running query exclusions
@HannahVernon HannahVernon changed the title Dev Added exclusions for sp_server_diagnostics and WAITFOR wait types to GetLongRunningQueriesAsync() method. Feb 27, 2026
…c() method. Removed System.Collections.Generic using statement as it is unnecessary.
…KUPTHREAD-to-long-running-queries

Added exclusion for backup-related waits to GetLongRunningQueriesAsync() method.
Copy link
Owner

@erikdarlingdata erikdarlingdata left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks for this — filtering by r.wait_type instead of query text is a much better approach, and the sp_server_diagnostics exclusion is a real need for WSFC/AG environments.

A few things to address:

WAITFOR RECEIVE regression

The old code filtered t.text NOT LIKE N'%waitfor receive%' which caught Service Broker waits. The new r.wait_type <> N'WAITFOR' doesn't cover that — Service Broker uses BROKER_RECEIVE_WAITFOR as its wait type. Please add that to the exclusions so we don't regress.

Add XE_LIVE_TARGET_TVF exclusion

Please also exclude XE_LIVE_TARGET_TVF — Extended Events live target reads show up as long-running and aren't actionable.

Unused parameters

The excludeSpServerDiagnostics, excludeWaitFor, excludeBackupWaits, and maxLongRunningQueryCount parameters aren't used by any caller. Let's keep the method signature simple — just apply the exclusions unconditionally. If we need configurability later, we can add it then.

Minor code issues

  • if (maxLongRunningQueryCount <= 5) clamps to minimum 5 — if the intent is to prevent invalid values, this should be <= 0 or < 1
  • string LongRunningQueryCount — PascalCase for a local variable (should be camelCase per C# conventions)
  • Trailing }; after the if block (semicolon unnecessary)
  • The //using System.Collections.Generic; comment-out in LandingPage.xaml.cs is unrelated to this PR — please revert that file

Overall the direction is good, just needs some cleanup. Thanks!

…dded miscWaitsFilter to exclude XE_LIVE_TARGET_TVF waits. Removed unused parameters. Corrected minimum value for maxLongRunningQueryCount (minimum 1 instead of 5).
…KUPTHREAD-to-long-running-queries

Feature/add exclusion for backupthread to long running queries
@HannahVernon
Copy link
Contributor Author

Requested changes have been made.

Copy link
Owner

@erikdarlingdata erikdarlingdata left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks good — all the requested changes from the first review are addressed. The wait_type filtering approach is solid.

A couple minor style nits (non-blocking):

  1. String interpolation is unnecessary — since the four filter strings are always applied unconditionally, they could just be inlined directly into the SQL constant. The separate variables + @$"" interpolation adds indirection without benefit. Not a blocker.

  2. SP_SERVER_DIAGNOSTICS LIKEr.wait_type NOT LIKE N'%SP_SERVER_DIAGNOSTICS%' could be r.wait_type <> N'SP_SERVER_DIAGNOSTICS' since that's the exact wait type name. Minor.

Approving as-is — these can be cleaned up later if desired.

@HannahVernon
Copy link
Contributor Author

Regarding the SP_SERVER_DIAGNOSTICS wait, I checked the docs and don't see a matching discrete wait type specifically named SP_SESRVER_DIAGNOSTICS - there are several related wait types listed. Perhaps I'm missing something or looking at the wrong docs page?

I'm in the process of adding similar exclusions to the Lite product - assuming the same wait types should be excluded there?

@erikdarlingdata
Copy link
Owner

@HannahVernon do you want to piggyback this PR for the Lite changes too, or handle that in a separate one?

WRT the waits, I'd only ever seen it sawing away on SP_SERVER_DIAGNOSTICS_SLEEP. Didn't realize there were a bunch of others.

@HannahVernon
Copy link
Contributor Author

I already setup a separate branch for Lite.

@HannahVernon
Copy link
Contributor Author

Should i add the specific sp_server_diagnostics waits using an IN (...) construct? Or are we ok matching all waits including future ones that match the pattern?

@erikdarlingdata erikdarlingdata merged commit c51722f into erikdarlingdata:dev Mar 2, 2026
3 checks passed
@erikdarlingdata
Copy link
Owner

@HannahVernon Fine as-is, thank you again for the contribution! You will be remembered forever as an Open Source Heroine.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants