SQL Server performance case study

Problem. A daily report filters with CONVERT(CHAR(10), BookedUtc, 120) = '2025-03-14'. Wrapping the column in a function means SQL Server cannot seek the index on BookedUtc, so it scans all 600,000 jobs and converts each date to text.

Fix. Filter the bare column with a half-open range: BookedUtc >= '2025-03-14' AND BookedUtc < '2025-03-15'. No schema change, same 822 rows.

Measured. Logical reads went from 6,399 to 2,532. Elapsed time went from about 108 ms to about 2 ms.

Worth noticing. Reads fell by only 2.5 times but elapsed time fell by about 50 times. Most of the original cost was CPU spent converting 600,000 dates to strings, which a reads-only review would underrate. The 2,532 reads that remain are key lookups, and scenario 02 shows how to remove that kind of cost when a query runs often enough to justify an index.

Problem. A despatch board asks for one pickup zone on one day. The only useful index is on BookedUtc, so SQL Server reads the day for all 60 zones and does a key lookup per row (822 lookups) to test the zone and fetch the other columns. Only 21 rows come back.

Fix. A covering index with the equality column first: (PickupZoneId, BookedUtc) INCLUDE (CustomerId, DriverId, JobStatus, ServiceLevel).

Measured. Logical reads went from 2,528 to 3. Elapsed time went from about 1.4 ms to about 0.3 ms.

Worth noticing. On one execution with a warm cache that time saving is tiny, and it would be dishonest to sell it as more. The value is in reads: a board refreshed by many users all day does about 800 times less page work, which leaves more buffer pool for everything else. Every index also slows writes and takes disk space, so a real review weighs this against the write load and looks for existing indexes it can replace.

cleanup.sql drops the index afterwards, because it also happens to cover the query in scenario 01 and would change that scenario's "before" numbers. The benchmark hit exactly that on its second run, which is why the cleanup exists.

Problem. dbo.GetCustomerJobs returns a customer's job history. Customer 1 has 119,987 jobs and customer 5000 has 18. SQL Server builds the cached plan for whichever value it sees first, so the same procedure is fast or slow depending on who ran first after the last recompile. That is why this kind of issue often looks random and "fixes itself" after a restart.

Fix. OPTION (RECOMPILE) on the statement, so each call gets a plan for its own value.

Measured.

Worth noticing. Both directions are bad. The large customer suffers from 120,000 key lookups, and the small customer pays for a full scan to get 18 rows. OPTION (RECOMPILE) is not free, since every call compiles, and that is acceptable here but not for a procedure called thousands of times a minute. In that case I would look at a covering index, OPTIMIZE FOR, or separate code paths for the few very large customers. Which one is right depends on call frequency, so the choice is made per procedure.

Problem. ConsignmentNumber is VARCHAR(20) with a unique index. The calling code passes a .NET string through AddWithValue, which sends NVARCHAR. NVARCHAR has higher precedence, so SQL Server converts the column rather than the parameter, and a one-row lookup becomes a scan of the whole index.

Fix. Send the right type. Nothing changes in the database:

// after command.Parameters.Add("@Number", SqlDbType.VarChar, 20).Value = number; ```

Measured. Logical reads went from 2,918 to 6. Elapsed time went from about 66 ms to about 0.3 ms.

Worth noticing. The result depends on collation. This database uses SQL_Latin1_General_CP1_CI_AS, where the conversion forces a scan. With a Windows collation SQL Server can often still seek, so the same code would cost much less, which is why the setup script pins the collation. Plans with this problem carry a PlanAffectingConvert warning, so they can be searched for across the plan cache instead of found one complaint at a time.

Problem. Two batches update the same jobs and consignments at the same time. dbo.RateJobs goes Jobs then Consignments, and dbo.ImportPods goes Consignments then Jobs. Each ends up holding what the other needs, SQL Server kills one as the deadlock victim (error 1205), and that batch's transaction rolls back.

Fix. Three things, in order of importance:

1. Consistent lock order: both procedures now touch Jobs first, then Consignments (after.sql). 2. Short transactions: 200 jobs per commit instead of 2,000, so locks are held for less time and a failure loses less work. 3. A retry on error 1205 with a short backoff, in DeadlockRepro/Program.cs, as a safety net.

Run it.

It runs two concurrent tasks through 20 rounds of 2,000 jobs in each of three modes, then checks the rows themselves: a job counts as processed only if both batches stamped it and all of its consignments.

Measured.

Worth noticing. Every round deadlocks before the fix because the repro holds each transaction open long enough to overlap, using a simulated work delay. Real systems usually deadlock less often, and that makes the problem harder to diagnose, not milder. Retry alone recovers the data but the deadlocks keep happening, so it hides the cause and still pays for the rollbacks. The elapsed times come from this deliberately conflicting workload and should not be read as a general throughput gain.

read-deadlock-graphs.sql pulls the graphs from the built-in system_health session. The XML includes host and login names, so review it before sharing it. The repro changes stamps on jobs 300,001 to 340,000; rerun setup/setup.sh for a pristine database.