Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Reading Older SQL Servers: Engine Evolution from 2016 to 2025 and a Compatibility Level Upgrade Strategy
The engine version and the compatibility level are different things: in a database running at level 130 on SQL Server 2025, a table variable is still estimated at 1 row, an MSTVF at 100 rows, a scalar UDF is called 8,916 times and REGEXP_LIKE isn't recognized. A 2016 to 2025 feature table, Microsoft Lifecycle dates, and a real run of the flow: upgrade the engine at the old level, collect with Query Store, raise the level, force the plan on a regression.
A Diagnostic Method: Solving "the Database Is Slow" with Wait Statistics, DMVs and Extended Events
A "database is slow" complaint is solved by measuring, not guessing: session or server waits say what is waited on, DMVs and Extended Events say which query does how many logical reads, the plan says why, and the fix is verified with the same metric. In the example the slowness was blocking, not an index; an index took the status list from 7,707 to 3 logical reads. Duplicate index detection, 2025 time-bound XE sessions and OpenTelemetry from endpoint to query.
The SQL Server Security Model: Logins, Users, Schemas, Roles, Least Privilege and Row-Level Security
Making the application user db_owner hands out table-dropping rights in an injection hole; the right way is to separate runtime and migration identities, grant through schemas and roles, and protect critical rows with Row-Level Security. SQL Server 2025's PBKDF2 password hashes, DENY precedence, RLS with SESSION_CONTEXT and how the predicate shape affects performance, why DDM isn't a security boundary, and connection tests with TrustServerCertificate and TDS 8.0 strict.
tempdb, Temp Tables and Table Variables: Which One When?
Below compatibility level 150 a table variable is estimated at 1 row, and a 17,359-row join climbs to 182,749 logical reads; deferred compilation fixes the row count, but without column statistics filter estimates are still wrong. The statistics, rollback and re-evaluation differences between temp tables, table variables and CTEs, tempdb contention and data files, bulk data with TVPs, and SQL Server 2025 tempdb space resource governance.
Functions and Triggers: Scalar UDF Inlining, Inline TVFs, Multi-Statement TVFs and Trigger Pitfalls
A scalar UDF that isn't inlined runs as a separate module per row: in a query over 17,359 orders the function was called 34,718 times, 365,498 logical reads never showed up in STATISTICS IO, and the plan couldn't go parallel. SQL Server 2019 inlining, is_inlineable, inline TVFs versus the fixed 100-row MSTVF estimate and interleaved execution, the single-row trigger assumption, EF Core's OUTPUT restriction and temporal tables for auditing, measured.
Stored Procedures: Parameters, Error Handling, Ownership Chaining and Safe Dynamic SQL
The right stored procedure skeleton is SET NOCOUNT ON, SET XACT_ABORT ON, ROLLBACK inside TRY/CATCH and a parameterless THROW; otherwise a CHECK error can commit half an order. Reading output parameters and return codes from Dapper, ownership chaining that gives the application user only EXECUTE, safe dynamic SQL with an allowlist, QUOTENAME and sp_executesql, and repairing the chain dynamic SQL breaks with module signing, with real output.
SQL Server 2025 Optimized Locking: TID Locking, Lock After Qualification and Behavior Changes
With optimized locking on, a transaction that updates 1,000 rows holds a single XACT lock instead of 1,017 locks and never escalates; with RCSI also on, LAQ evaluates the UPDATE predicate on the last committed version without locks, removing blocking but possibly changing the result of some concurrent work. The ADR prerequisite, new LCK_M_S_XACT waits, hints that disable LAQ, xactlock in the deadlock graph and the 2019/2022 difference, with real measurements.
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.
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.
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.
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.
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.