Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
A Query's Journey Inside SQL Server: Relational Engine, Storage Engine, Buffer Pool and Transaction Log
A query reaches SQL Server over TDS, is parsed, bound and optimized in the Relational Engine and lands in the plan cache; the Storage Engine reads pages through the buffer pool, and changes go to the transaction log first (WAL) and to the data file later via checkpoint. Also: the deterministic 5.6-million-row Bookstore lab data used across the series, the 2025 editions and how compatibility level differs from engine version.
Pages, Heaps and Clustered Indexes: How SQL Server Stores Data on Disk
SQL Server keeps data in 8 KB pages; a clustered index is a shallow B-tree that is the table itself, while a heap means unordered pages and forwarded records on update. Real pages, ROW_OVERFLOW/LOB, choosing a clustering key, an experiment showing Guid.CreateVersion7 values are as random as NEWID in SQL Server ordering, and fill factor and page density measurements.
Nonclustered Index Design: Key Order, INCLUDE, Covering Indexes and Key Lookups
A good nonclustered index puts equality columns before the range column in the key and carries the query's remaining columns with INCLUDE; otherwise key lookup cost makes the optimizer scan the table. Bookstore's order search query drops from 7,707 to 25 logical reads; with the tipping point, how filtered indexes interact with EF Core literals, computed columns, HasIndex equivalents and write cost measured.
Statistics and Cardinality Estimation: Histograms, Density and the Cost of a Wrong Estimate
The optimizer estimates row counts from statistics: if the value is a histogram step the estimate is exact, inside a step it is an average, and when unknown it falls back to the density vector. On 1 million Bookstore orders: reading the histogram, verifying the auto-update threshold (31,623 changes) by experiment, a plan 24 times more expensive due to the ascending key, CE models on correlated columns, and CE feedback.
Reading Execution Plans 1: Estimated vs Actual Plans, Operators and Join Algorithms
An execution plan is read right to left, inside out; the first thing to check is not cost percentage but estimated versus actual rows and how many times each operator ran. Where cost percentage misleads in the actual plan of an EF Core Include query, seek/scan/lookup, the measured cost of nested loops, merge and hash joins on the same query, stream and hash aggregates, and going from 2,549 to 689 logical reads with two indexes.
Reading Execution Plans 2: Memory Grants, Spills, Parallelism and Implicit Conversion Warnings
Plan warnings show the price of an estimate: a small estimate leaves the memory grant short and spills to tempdb, a low cost threshold spreads small queries over 10 threads, and an nvarchar parameter on a varchar column turns a 3-page lookup into 3,117 pages. Memory grant feedback, exchange operators, CX waits, DOP feedback on by default in 2025, implicit conversion with EF Core and Dapper, batch mode and adaptive joins.
Plan Cache, Parameterization and Parameter Sniffing: Symptoms, Diagnosis and Fixes
Parameter sniffing is a cached plan compiled for the first parameter value that does not fit other values on skewed data: the same procedure did 7,709 logical reads for customer 1 in one order and 612,850 in the other. Ad hoc/prepared plans, simple and forced parameterization, the price of RECOMPILE and OPTIMIZE FOR, 2022's PSP optimization, 2025's OPPO, and plan cache bloat from ORM parameter lengths.
Query Store and Intelligent Query Processing: Finding Regressions, Forcing Plans and Hinting Without Code Changes
Query Store keeps query texts, plans and runtime statistics durably in the database, so it answers the "what was it like yesterday" question the plan cache forgets, and lets you force a plan and add hints without changing code. A regression query, the price of plan forcing, FORCE_LAST_GOOD_PLAN, RECOMPILE on an EF Core query via a Query Store hint, 2025's ABORT_QUERY_EXECUTION and the IQP family.
Transactions and the Transaction Log: The Engine Side of ACID, XACT_ABORT, Savepoints and Accelerated Database Recovery
Atomicity does not come for free in SQL Server: with XACT_ABORT OFF a COMMIT after a failing statement makes half the work durable, an inner COMMIT commits nothing, and a single open transaction stops log truncation even in SIMPLE recovery. The XACT_ABORT and TRY/CATCH/THROW pattern, savepoints, LSN/VLF/recovery models, a 200,000-row rollback going from 200,031 log records to 1 with ADR, and TransactionScope's distributed transaction error on Linux/macOS.
Isolation Levels and Row Versioning: READ COMMITTED, RCSI, SNAPSHOT, REPEATABLE READ and SERIALIZABLE
Isolation levels decide, through locking or row versioning, which anomalies concurrent transactions can see: the default READ COMMITTED makes readers wait for writers, RCSI returns the last committed version, and SNAPSHOT keeps the view from the start of the transaction and fails with 3960 on conflict. Two-session experiments on dirty reads, non-repeatable reads, phantoms, lost updates and write skew, the 14-byte cost of versioning and choosing an IsolationLevel from .NET.
Locks and Blocking: Lock Modes, Granularity, Lock Escalation and Finding the Blocking Chain
Even a one-row UPDATE holds four locks, and SQL Server queues lock requests, so you look for the root of the chain with sys.dm_exec_requests and sys.dm_exec_sessions. Lock modes and the compatibility matrix, escalation measured at 6,249 locks in the lab, a safe upsert with UPDLOCK/SERIALIZABLE, a queue table with READPAST, LOCK_TIMEOUT, and a migration whose Sch-M stops every reader, with real output.
Deadlocks: Reading the Deadlock Graph, Common Patterns and Retrying in .NET
In a deadlock SQL Server picks the transaction that is cheapest to roll back as the victim and returns 1205; the fix is to read the graph, fix the access order, break reader-writer cycles with a covering index or RCSI, and retry the whole transaction while rechecking the business rule. Opposite-order, HOLDLOCK upsert and key lookup deadlocks, DEADLOCK_PRIORITY, EF Core's execution strategy, and a real case where a Polly retry overrode a cancellation.