Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
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.
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.
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.
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.
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.
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.
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.