Examine PerfStatsAnalysis.sql for any hard-to-catch bugs/issues using GitHub Copilot

[PiJoCoder]

[PiJoCoder]

Bug Fixes in PerfStatsAnalysis.sql

Bug 1 — AND/OR operator precedence in DataSet_WaitStats_BlockingChains (High)

Problem: Missing parentheses caused incorrect AND/OR precedence. The WHERE clause was evaluated as "(duration > 0 AND blocking_start in range) OR (blocking_end in range)", which returned rows with zero blocking duration whenever blocking_end fell within the time range.
Fix: Added parentheses to group the two BETWEEN conditions together under the OR, so the blocking_duration_sec > 0 filter applies to both conditions.

---

Bug 2 — Missing DROP guard before DataSet_WaitStats_WaitStatsTopCategoriesOther (Medium)

Problem: Every other stored procedure in the script has an IF OBJECT_ID / DROP guard before the CREATE PROC statement. This procedure was missing one, causing the script to fail on re-execution with "object already exists."
Fix: Added the standard IF OBJECT_ID(...) IS NOT NULL DROP PROC guard before the CREATE statement.

---

Bug 3 — Integer overflow in @avail_cpu_time_sec (High)

Problem: The variable @avail_cpu_time_sec was declared as INT in both DataSet_WaitStats_WaitStatsTop5Categories and DataSet_WaitStats_WaitStatsTopCategoriesOther. A downstream calculation multiplies this by 1000, which overflows INT (max ~2.1 billion) for high-core-count servers with long collection intervals. For example, 96 cores × 86400 seconds × 1000 = ~8.3 billion, exceeding INT range.
Fix: Changed both declarations from INT to BIGINT.

---

Bug 4 — Division by zero in usp_SmallSampledStats (Medium)

Problem: The expression that divides rows_sampled by the rows column fails with a divide-by-zero error when rows is 0 (empty tables that still have statistics objects).
Fix: Wrapped the rows column with NULLIF(..., 0) so division by zero returns NULL instead of an error.

---

Bug 5 — Inverted logic in usp_BatchSort (High)

Problem: The IF condition checked whether the xml_plan column does NOT exist (IS NULL), but the code block inside immediately queries that column. This meant the query would always fail when the column existed and never execute when it didn't — completely backwards.
Fix: Changed the IS NULL check to IS NOT NULL so the code runs only when the xml_plan column is present.

---

Bug 7 — wait_time_ms_per_sec not clamped to zero when negative (Low)

Problem: In DataSet_WaitStats_WaitStatsTop5Categories, the total_wait_time_ms column was already clamped to 0 when wait stat counters reset mid-collection (e.g., via DBCC SQLPERF CLEAR), but the wait_time_ms_per_sec column used the raw unclamped delta. This produced inconsistent results such as total_wait_time_ms = 0 alongside wait_time_ms_per_sec = -500, which showed negative values in report charts.
Fix: Applied the same CASE/WHEN clamping pattern that was already used for total_wait_time_ms, so wait_time_ms_per_sec also returns 0 when the delta is negative.

---

Bug 8 — Shadowed table alias in NOT IN subquery (Low)

Problem: In two procs (DataSet_WaitStats_WaitStatsTop5Categories and DataSet_WaitStats_WaitStatsTopCategoriesOther), the NOT IN subquery that identifies the top 5 categories reused the same table alias as the outer query. While the query happened to work because the subquery is independent, it is confusing and error-prone. Additionally, the subquery lacked the same WHERE filters (excluding SOS_SCHEDULER_YIELD, filtering for positive values) used by the outer Top 5 logic, so the exclusion list could differ from the actual Top 5 shown.
Fix: Renamed the inner alias to a distinct name and added the matching WHERE filters to ensure the exclusion list is consistent with the actual Top 5 categories returned.

---

Bug 9 — @@FETCH_STATUS check uses wrong comparison (Low)

Problem: In DataSet_BlockingChain_BlockingChainTextSummary, the cursor loop checked for @@FETCH_STATUS not equal to -1. However, @@FETCH_STATUS has three possible values: 0 (success), -1 (past end of cursor), and -2 (row missing/deleted). Using "not equal to -1" means the loop continues processing when a row is missing (status -2), potentially operating on incomplete or invalid data.
Fix: Changed the comparison to check for @@FETCH_STATUS equal to 0, which is the standard and correct pattern.

---

Bug 10 — Unintended SELECT * in proc_AutoStats (Low)

Problem: The proc_AutoStats procedure contained a SELECT * FROM tbl_AnalysisSummary that returned the entire table contents to the caller on every execution. This appeared to be leftover debug code and served no functional purpose.
Fix: Removed the SELECT * statement.

---