Indexing for Procs (Warehouse Boards)

Status: Reviewed (deep-dive v1)
Stack note: Blazor boards call usp_* (Stored procedures) with filters — indexes must match sargable predicates and covering needs, or GetOpenBoard / heat maps time out under estate growth.
Pairs with: Index & statistics maintenance (index/stats maintenance), Performance health / Perf triage board (perf triage), Stored procedures–15 (proc shape, #temp, dynamic SQL, errors).
Goal: Practical index patterns for board/read procs — not a full indexing encyclopedia.

Quick chooser

Board pattern	Index lean toward
Filter by status + order by date	`(Status, FirstSeenAt)` or filtered IX on open rows
Filter by instance then child rows	Leading `SqlInstanceId` + supporting cols
Heat map by RAG / last backup	Supporting IX on status + `LastBackupAt`
Point lookup by PK/id	Clustered / PK enough
Optional filters (`@Id IS NULL OR …`)	May need multiple IX; measure — don’t index every column
Big `#temp` stage then join	Index `#temp` after load (Temp vs table vars)

Sargable filters (proc habit)

Good (index-friendly):

WHERE a.Status = @Status
  AND a.FirstSeenAt >= @Since

Often bad:

WHERE ISNULL(a.Status, N'') = @Status
WHERE CONVERT(date, a.FirstSeenAt) = @Day
WHERE a.Title LIKE N'%' + @q + N'%'   -- leading wildcard
WHERE (@Status IS NULL OR a.Status = @Status)  -- can be OK; watch plans

Optional-param pattern is still preferred over dynamic SQL (Dynamic SQL) — if the plan goes dumb, try:

Separate procs for hot paths, or
OPTION (RECOMPILE) on the board proc when measured, or
Dynamic SQL with parameterized values + allowlisted bits

Covering ideas for boards

UI needs a thin column list — help the IX include them:

-- example: open/acked alerts by severity age
CREATE INDEX IX_Alert_Status_FirstSeen
  ON dbo.Alert (Status, FirstSeenAt)
  INCLUDE (AlertType, EntityKey, Title, SeverityCode, SqlInstanceId, AckedBy, LastSeenAt);

Filtered index when boards mostly show active rows:

CREATE INDEX IX_Alert_Active_FirstSeen
  ON dbo.Alert (FirstSeenAt, SeverityCode)
  INCLUDE (AlertType, Title, SqlInstanceId, Status)
  WHERE Status IN (N'Open', N'Acked');

Rule: Match WHERE + ORDER BY first; INCLUDE what SELECT lists without exploding width.

Estate board → index map (starting point)

Proc / board	Likely keys
`usp_Alert_GetOpenBoard`	`Alert(Status, FirstSeenAt)` + `AlertSeverity(SeverityCode)` / SortOrder
`usp_Inventory_GetInstances`	`SqlInstance(LastSeenAt)`, hostname/instance search carefully
`usp_Backup_GetHeatmap`	DB + RAG/last backup timestamps
`usp_Agent_GetFailBoard`	Job fail flags + `LastRunAt`
`usp_Alert_Evaluate`	Supporting IX for source flags; `#Findings` indexed if large

Don’t create ten overlapping indexes “just in case” — add from Query Store / actual plans.

`#temp` in procs

INSERT #Findings (…);
CREATE CLUSTERED INDEX CX ON #Findings (AlertType, EntityKey);
-- then merge/join

Stats on #temp beat naked @table for big stages (Temp vs table vars).

Ops checklist

[ ] After new board proc: check actual plan + Query Store top consumers
[ ] Stats: maintenance plan / UPDATE STATISTICS on hot warehouse tables (Index & statistics maintenance)
[ ] Watch key lookups → widen INCLUDE if lookup cost dominates
[ ] Writes (Ack, merge): every extra IX costs inserts — keep board IX lean
[ ] Parameter sniffing: skewed instance sizes → measure before RECOMPILE
[ ] No duplicate indexes (same keys different names)

Anti-patterns

Smell	Fix
Index per Blazor column	Design for real filters only
`SELECT *` in proc	Narrow list → easier covering
Function on column in WHERE	Compute persisted / fix predicate
Non-allowlisted dynamic ORDER BY	Dynamic SQL allowlist — indexes must match real sorts
Fixing perf with NOLOCK everywhere	Index + query shape first

Tiny lab

Run GetOpenBoard with actual plan — note scans/lookups
Add one covering/filtered IX — re-compare duration + reads
Ack 100 rows — confirm write regression acceptable

Tie-ins

Index & statistics maintenance: Ongoing index/stats jobs
Performance health / Perf triage board: Triage board feeds what to index next
Stored procedures: Stable aliases + narrow SELECTs enable covering

Done when

[ ] Each hot usp_* board has a documented supporting IX (or explicit “PK enough”)
[ ] Optional-param plans reviewed once under realistic data
[ ] This note linked from Knowledge Base list

SQL Dude — Indexing for Procs v1