Running Queries -Collect DOP & option to collect task level wait #1637
Conversation
Add option to collect top 3 waits from sys.dm_os_waiting_tasks. trimble-oss#1632
There was a problem hiding this comment.
Pull Request Overview
This PR adds functionality to collect the degree of parallelism (DOP) and provides an option to collect task-level waits from sys.dm_os_waiting_tasks for running queries.
Key Changes:
- Added
CollectTaskWaitsconfiguration option to enable collecting top 3 task waits - Introduced new database columns for storing task wait information (type and time for top 3 waits) and DOP
- Updated UI to include task waits and DOP display in the running queries view
Reviewed Changes
Copilot reviewed 17 out of 18 changed files in this pull request and generated 1 comment.
Show a summary per file
| File | Description |
|---|---|
| DBADashServiceConfig/ServiceConfig.resx | Reorganized resource file entries (no functional changes) |
| DBADashServiceConfig/ServiceConfig.cs | Added UI controls and configuration for CollectTaskWaits option |
| DBADashGUI/Performance/RunningQueries.cs | Added DOP and TaskWaits columns to the running queries grid display |
| DBADashDB/dbo/Views/RunningQueriesInfo.sql | Added task wait concatenation logic and DOP column to view |
| DBADashDB/dbo/User Defined Types/RunningQueries.sql | Added columns for task wait types/times and DOP |
| DBADashDB/dbo/Tables/RunningQueries.sql | Added storage columns for task waits and DOP |
| DBADashDB/dbo/Stored Procedures/RunningQueries_Upd.sql | Updated insert statements to include new task wait and DOP columns |
| DBADashDB/dbo/Stored Procedures/RunningQueries_Get.sql | Added TaskWaits and DOP to result set |
| DBADashDB/dbo/Stored Procedures/RunningQueriesForSession_Get.sql | Added TaskWaits and DOP to session queries result set |
| DBADashDB/dbo/Stored Procedures/RunningQueriesForJob_Get.sql | Added TaskWaits and DOP to job queries result set |
| DBADashConfig/Options.cs | Added CollectTaskWaits command-line option |
| DBADashConfig/Helper.cs | Added CollectTaskWaits to configuration helper |
| DBADash/SQL/SQLRunningQueries.sql | Added SQL logic to collect top 3 task waits and DOP from DMVs |
| DBADash/DBImporter.cs | Added backward compatibility for new columns in data import |
| DBADash/DBCollector.cs | Passed CollectTaskWaits parameter to collection query |
| DBADash/DBADashSource.cs | Added CollectTaskWaits property to source configuration |
| DBADash.Test/DBADashConfig.cs | Updated test to include CollectTaskWaits parameter |
Files not reviewed (1)
- DBADashServiceConfig/ServiceConfig.Designer.cs: Language not supported
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
| CONCAT(Q.task_wait_type_1 + ' (' + CAST(Q.task_wait_time_1 AS NVARCHAR(260)) + 'ms)', | ||
| ', ' + Q.task_wait_type_2 + ' (' + CAST(Q.task_wait_time_2 AS NVARCHAR(260)) + 'ms)', | ||
| ', ' + Q.task_wait_type_3 + ' (' + CAST(Q.task_wait_time_3 AS NVARCHAR(260)) + 'ms)') AS TaskWaits, |
There was a problem hiding this comment.
Using the + operator for string concatenation with NULL values will result in NULL for the entire expression. When any of the task_wait_type columns are NULL (which they will be when fewer than 3 wait types exist), the entire TaskWaits result will be NULL. Use CONCAT() properly without the + operators, or handle NULLs explicitly with ISNULL() or COALESCE().
| CONCAT(Q.task_wait_type_1 + ' (' + CAST(Q.task_wait_time_1 AS NVARCHAR(260)) + 'ms)', | |
| ', ' + Q.task_wait_type_2 + ' (' + CAST(Q.task_wait_time_2 AS NVARCHAR(260)) + 'ms)', | |
| ', ' + Q.task_wait_type_3 + ' (' + CAST(Q.task_wait_time_3 AS NVARCHAR(260)) + 'ms)') AS TaskWaits, | |
| CONCAT( | |
| Q.task_wait_type_1, ' (', CAST(Q.task_wait_time_1 AS NVARCHAR(260)), 'ms)', | |
| ', ', Q.task_wait_type_2, ' (', CAST(Q.task_wait_time_2 AS NVARCHAR(260)), 'ms)', | |
| ', ', Q.task_wait_type_3, ' (', CAST(Q.task_wait_time_3 AS NVARCHAR(260)), 'ms)' | |
| ) AS TaskWaits, |
There was a problem hiding this comment.
This is incorrect. It's desirable for the expressions concatenated with + to return NULL as each represent an expression for one of the top 3 wait types. Each of these 3 expressions are concatenated with CONCAT which doesn't result in a NULL value. e.g.
If any part of this is NULL, it should be NULL
Q.task_wait_type_1 + ' (' + CAST(Q.task_wait_time_1 AS NVARCHAR(260)) + 'ms)'
Same here:
', ' + Q.task_wait_type_2 + ' (' + CAST(Q.task_wait_time_2 AS NVARCHAR(260)) + 'ms)'
and here:
', ' + Q.task_wait_type_3 + ' (' + CAST(Q.task_wait_time_3 AS NVARCHAR(260)) + 'ms)')
But these are combined with CONCAT so we don't get NULL if we don't have 3 task waits.
1 participant
Add option to collect top 3 waits from sys.dm_os_waiting_tasks. #1632
Collect DOP. #1630