Topic Guide
SQL Server Correctness and Performance
There are two kinds of database story here, and they're more related than they look. The first is performance: an index seek that read far more than its name promised, a query plan that looked exactly like a bot swarm until it wasn't, an admin screen that took 25 seconds because everyone trusted the wrong query. The second is data recovery: a customer's entire site deleted and rebuilt from a shared multi-tenant database, a one-shot repair script built to run against production and be trusted, a datetime value that corrupted a migration because 1900-01-01 looks like a real date.
What connects them is correctness under pressure. A slow query and a wrong row are both cases where the database told you one thing (an operator name, a symptom, a root cause) and the truth was somewhere else. The posts below are grouped that way: performance and correctness incidents first, then the recovery and repair discipline that makes touching production data survivable, dry runs, diffable artifacts, and scripts that assert their own assumptions instead of hoping they hold.
If you're debugging a query plan or writing a repair script tonight, the pattern worth stealing is the same in both halves: verify the actual shape of the data or the actual size of the seek range before you trust the label SQL Server gave it.
21 posts in this guide, by DX
Start here
An Index Seek Can Still Read Far More Than You Expect
An execution-plan operator did not reveal the size of its seek range. In this verified, non-overlapping range dataset, a differently oriented covering index substantially reduced worst-case reads after staged testing.
Execution plans and index design
-
From 3.5 million round-trips to hundreds: batching a remap with temp tables
How I collapsed a 3.5-million-round-trip ID remap into a handful of set-based join-UPDATEs using temp tables, and why the index matters as much as the query.
-
A 25-Second Admin Screen Meant We Trusted the Wrong Query
An internal admin screen that looked like a layout ticket required a trace of its real database work. The incident showed why query plans, scope, and user value matter more than the surface description of a slow page.
-
The Page Was Performing a Ceremony, Not a Job
An internal overview page accumulated more than 80 blocking queries per load. The important fix was to reconsider its page contract and defer nonessential sections, not merely tune one query.
-
314 broken stats pages and one bad cursor
Stats pages were broken across 314 sites. The cause was a shared recordset helper opening keyset ADO cursors over aggregate queries. The data caught up.
-
The bot swarm that turned out to be one bad query plan
The uptime monitor fired on a 9 PM CPU spike. Everything pointed to hostile traffic. It was one cached plan in a request-log table doing clustered scans on every request, and one OPTION (RECOMPILE) settled it.
-
Codex flagged my ONLINE=ON, my own probe said it was fine, and the server proved us both wrong
I verified the SQL Server edition before shipping an ONLINE index build. Still shipped the wrong answer. The only thing that caught me was the server refusing the statement.
Correctness: datetimes, types, and shapes
-
1900-01-01 Is Not a Date: The Zero-Datetime That Corrupts Every Migration
SQL Server's datetime epoch (1900-01-01) looks like a real date, serializes cleanly, and inserts without error. That's exactly why it corrupts migrations. Here's how to catch it and why you need to omit the column, not blank it.
-
Avoid Unnecessary Date String Round Trips
A date value was formatted for display and then handed back to a database parser. The durable lesson is to preserve typed values, use parameters, and perform only authorized, reviewed, validated, and reversible data changes.
-
A presence check is not type validation
A value can be present, non-empty, and still be the wrong kind of value for the operation that follows. The durable fix is to validate the real contract, separate identifiers from their representations, and fail safely at the boundary.
-
Same Root Cause Is Not Same Fix: Verify the Data Shape, Not Just the Symptom
Two accounts, same symptom, same root-cause label, opposite fixes: one row re-pointed, one row deleted. Query the actual data shape before reusing a repair script.
When the incident wore a performance costume
Data recovery and the repair-script discipline
-
I Wrote a 1,477-Line Site-Recovery Engine in Classic ASP: Copy First, Remap IDs Second
When a customer's entire sports site gets deleted in a shared-database multi-tenant system, RESTORE DATABASE is not an option. Here's the copy-first, remap-IDs-second pattern that makes application-level recovery work without cross-tenant corruption.
-
Don't leave the recovery gun loaded: scrubbing prod constants at end of day
After a successful data recovery, the script was still armed: real customer username hardcoded, phase set to run. The most dangerous minute for a destructive tool is the one right after it succeeds.
-
Recovering a 327-Team League: Plan-Run Before You Touch Prod
A 327-team recovery was too risky to validate by instinct. A plan mode, diffable before-and-after artifacts, and a disposable test database made the production run inspectable before it wrote live data.
-
The One-Shot Data-Repair Script as a First-Class Artifact
The console UPDATE is the most dangerous tool a solo dev points at production. A four-part repair-script discipline: dry-run, assert, idempotent, JSON record.
-
A code-first schema audit: keeping a 1,477-line ASP file in sync with a 14,536-column DB
When a hand-maintained recovery script has to stay in sync with a 14,536-column database, discipline alone won't cut it. Here's how a 418-line Python differ and a source-of-truth decision solved the problem.
-
When a Workflow Gap Needs an Operator Tool: Preview, Policy, and an Audit Trail
When a normal workflow cannot make a needed, authorized change, resist the one-off production update. A narrow operator tool should make intent, authorization, validation, review, execution, and recovery explicit.
-
The Auto-Approval Feature That Quietly Rewrote Curated Data
A payment-confirmation automation overwrote a status that some administrators managed by hand. The remediation separated payment evidence from approval, added explicit tenant controls, and used a narrowly scoped, audited recovery process.
Build the diagnostic before you touch anything
-
Before you repair data, build the diagnostic
Many apparent software failures are mismatches between the story a user reports, the state a system stores, and the assumptions a repair would make. A focused diagnostic can separate genuine drift from valid but unfamiliar data before anything is changed.
-
The Fastest Legacy Hotfix Is Often a Diagnostic
A stacked set of customer tickets became clearer once small, access-controlled diagnostics separated data-state problems from code regressions before remediation began.
Hitting one of these walls in your own codebase or your own machine? Talk it through with us, or read the rest of the Build Log.