Perf Triage Board

Status: Reviewed (spike v1)
Path: Optional spike #5
Stack: PowerShell (schedule) → SQL DMV snapshots/procs → Blazor Server
Builds on: SQL-07 Performance health; requires dbo.SqlInstance from SQL-SPIKE-01
Goal: Ops triage board — hot waits (deltas), blocking now, top queries by CPU/duration/reads — not deep plan tuning.

In scope (MVP)

Per active instance, light DMV sample on a schedule
Store wait deltas, current blocking chains, top N query_stats rows (truncated text + query_hash)
Blazor: three panels (Waits / Blocking / Top SQL) with instance picker
Reuse CollectRun (CollectorName = N'PerfTriage')
Retention: keep detail ~7–14 days (purge job note only)

Out of scope (later)

Full plan XML collection / Query Store UI
Kill sessions from Blazor
Spike #7 alert wiring (Blocking, HotWait) — note hook only
Continuous XE sessions

A. Database objects

CREATE TABLE dbo.PerfWaitSample (
  PerfWaitSampleId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  SampleAt DATETIME2(0) NOT NULL,
  WaitType NVARCHAR(128) NOT NULL,
  WaitTimeMsDelta BIGINT NOT NULL,
  WaitingTasksDelta BIGINT NULL
);

CREATE TABLE dbo.PerfWaitBaseline (
  SqlInstanceId INT NOT NULL,
  WaitType NVARCHAR(128) NOT NULL,
  CumulativeWaitMs BIGINT NOT NULL,
  CumulativeSignalMs BIGINT NULL,
  CumulativeTasks BIGINT NULL,
  CapturedAt DATETIME2(0) NOT NULL,
  CONSTRAINT PK_PerfWaitBaseline PRIMARY KEY (SqlInstanceId, WaitType)
);

CREATE TABLE dbo.PerfBlockingSample (
  PerfBlockingSampleId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  SampleAt DATETIME2(0) NOT NULL,
  BlockedSessionId INT NOT NULL,
  BlockingSessionId INT NOT NULL,
  WaitType NVARCHAR(128) NULL,
  WaitTimeMs INT NULL,
  BlockedText NVARCHAR(400) NULL,
  BlockingText NVARCHAR(400) NULL
);

CREATE TABLE dbo.PerfTopQuerySample (
  PerfTopQuerySampleId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  SampleAt DATETIME2(0) NOT NULL,
  Metric VARCHAR(16) NOT NULL,          -- CPU / Duration / Reads
  RankNum INT NOT NULL,
  QueryHash VARBINARY(8) NULL,
  ExecutionCount BIGINT NULL,
  TotalWorkerTime BIGINT NULL,
  TotalElapsedTime BIGINT NULL,
  TotalLogicalReads BIGINT NULL,
  StatementText NVARCHAR(400) NULL
);

Procs:

CREATE PROC dbo.usp_Perf_ApplyWaitSnapshot
  @SqlInstanceId INT,
  -- TVP or XML of (WaitType, WaitTimeMs, SignalWaitMs, WaitingTasks) cumulative
AS ...
-- Compare to PerfWaitBaseline; insert deltas into PerfWaitSample; upsert baseline

CREATE PROC dbo.usp_Perf_ApplyBlockingSample
  @SqlInstanceId INT, @SampleAt DATETIME2(0) = NULL AS ...
-- Expect rows passed via TVP: blocked/blocking/wait/text

CREATE PROC dbo.usp_Perf_ApplyTopQueries
  @SqlInstanceId INT, @Metric VARCHAR(16), -- TVP of top N
AS ...

CREATE PROC dbo.usp_Perf_GetHotWaits
  @SqlInstanceId INT = NULL, @Minutes INT = 60, @TopN INT = 15 AS
BEGIN
  SELECT TOP (@TopN) i.HostName, i.InstanceName, w.WaitType,
         SUM(w.WaitTimeMsDelta) AS WaitTimeMs, MAX(w.SampleAt) AS LastSampleAt
  FROM dbo.PerfWaitSample w
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = w.SqlInstanceId
  WHERE w.SampleAt >= DATEADD(MINUTE, -@Minutes, SYSUTCDATETIME())
    AND (@SqlInstanceId IS NULL OR w.SqlInstanceId = @SqlInstanceId)
    AND w.WaitType NOT IN (N'BROKER_TASK_STOP', N'SLEEP_TASK', N'XE_TIMER_EVENT') -- extend ignore list
  GROUP BY i.HostName, i.InstanceName, w.WaitType
  ORDER BY WaitTimeMs DESC;
END;

CREATE PROC dbo.usp_Perf_GetBlockingNow
  @SqlInstanceId INT = NULL, @Minutes INT = 15 AS
BEGIN
  SELECT i.HostName, i.InstanceName, b.*
  FROM dbo.PerfBlockingSample b
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = b.SqlInstanceId
  WHERE b.SampleAt >= DATEADD(MINUTE, -@Minutes, SYSUTCDATETIME())
    AND (@SqlInstanceId IS NULL OR b.SqlInstanceId = @SqlInstanceId)
  ORDER BY b.SampleAt DESC, b.WaitTimeMs DESC;
END;

CREATE PROC dbo.usp_Perf_GetTopQueries
  @SqlInstanceId INT = NULL, @Metric VARCHAR(16) = 'CPU', @Minutes INT = 60 AS
BEGIN
  SELECT i.HostName, i.InstanceName, q.*
  FROM dbo.PerfTopQuerySample q
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = q.SqlInstanceId
  WHERE q.Metric = @Metric
    AND q.SampleAt >= DATEADD(MINUTE, -@Minutes, SYSUTCDATETIME())
    AND (@SqlInstanceId IS NULL OR q.SqlInstanceId = @SqlInstanceId)
  ORDER BY q.SampleAt DESC, q.RankNum;
END;

B. PowerShell / collect SQL (shape)

Cadence: every 1–5 minutes per instance (start at 5 min).

Waits: sys.dm_os_wait_stats → pass cumulative to usp_Perf_ApplyWaitSnapshot.

Blocking:

SELECT r.session_id AS BlockedSessionId, r.blocking_session_id AS BlockingSessionId,
       r.wait_type, r.wait_time,
       SUBSTRING(bt.text,1,400) AS BlockedText,
       SUBSTRING(xt.text,1,400) AS BlockingText
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) bt
LEFT JOIN sys.dm_exec_requests x ON x.session_id = r.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(x.sql_handle) xt
WHERE r.blocking_session_id <> 0;

Top queries (×3 metrics): sys.dm_exec_query_stats ORDER BY total_worker_time / total_elapsed_time / total_logical_reads, TOP 15; SUBSTRING statement; include query_hash.

Script: Collect-SqlPerfTriage.ps1
Rights on targets: VIEW SERVER STATE. Warehouse: execute apply procs.

C. Blazor Server (MVP UI)

Page: /perf or /sql/perf

Controls: Instance dropdown; window 15/60 min; Top metric tabs CPU | Duration | Reads

Panels:

Hot waits (bar or table)
Blocking (table; highlight head blockers)
Top SQL (hash + truncated text + counts)

Actions v1: copy query_hash / text; links to SQL-07, SQL-03, SQL-06, SQL-04 runbooks — no KILL

App pool: read procs only

D. Acceptance checks

[ ] Two wait samples → non-zero deltas on busy wait types
[ ] Induced blocker in lab → row on Blocking panel
[ ] Top CPU list returns ≤15 rows with truncated text
[ ] Ignore list filters sleep/broker noise
[ ] CollectRun errors on one instance don’t stop others
[ ] UI switches instance and metric without ad-hoc SQL

E. Repo layout

/sql/perf/001_tables.sql
/sql/perf/002_procs.sql
/ps/Perf/Collect-SqlPerfTriage.ps1
/src/.../Pages/Perf/Index.razor
/src/.../Services/PerfTriageService.cs

Hook for spike #7 (later)

Findings: blocking samples with WaitTimeMs > threshold → Blocking; top wait family PAGEIOLATCH/WRITELOG sustained → HotWait (optional).

Next (wait for pick)

Optional: #6 Security board, #8 Build out-of-date.

SQL Dude — SQL-SPIKE-05 Perf Triage Board v1