Page Free Space

Setting a Fixed Size for Transaction Log VLFs The documentation has this to say about virtual log file (VLF) sizes: ...

Why Batch Mode Sort Spills Are So Slow Batch mode sorting was added to SQL Server in the 2016 release under compatib...

Fast Key Optimization for Row Mode Sorts SQL Server row-mode sorts generally use a custom implementation of the well-kn...

Reducing Contention on the NESTING_TRANSACTION_FULL latch Each additional worker thread in a parallel execution plan ex...

More Consistent Execution Plan Timings in SQL Server 2022 The updated showplan schema shipped with SSMS 19 preview 2 c...

Be Careful with LOBs and OPTION (RECOMPILE) It sometimes makes sense to add OPTION (RECOMPILE) to a query. Typically ...

Incorrect Results with Parallel Eager Spools and Batch Mode You might have noticed a warning at the top of the release ...

sql_handle and the SQL Server batch text hash This article describes the structure of a sql_handle and shows how the...

SQL Server 2019 Aggregate Splitting The SQL Server 2019 query optimizer has a new trick available to improve the perfor...

A bug with Halloween Protection and the OUTPUT Clause Background The OUTPUT clause can be used to return results fro...

How MAXDOP Really Works A few days ago I ran a Twitter poll: The most popular answer gets highlighted by Twitter at ...

Pulling Group By Above a Join One of the transformations available to the SQL Server query optimizer is pulling a logic...

SQL Server Temporary Object Caching Creating a table is a relatively resource-intensive and time-consuming operation. T...

Nested Loops Prefetching Nested loops join query plans can be a lot more interesting (and complicated) than is commonly...

Two Partitioning Peculiarities Table partitioning in SQL Server is essentially a way of making multiple physical tables...

Working Around Missed Optimizations In my last post , we saw how a query featuring a scalar aggregate could be transfor...

Execution Plan Analysis: The Mystery Work Table I love SQL Server execution plans. It is often easy to spot the cause o...

Incorrect Results with Indexed Views If you use MERGE , indexed views and foreign keys, your queries might return incor...

Optimizing T-SQL queries that change data Most tuning efforts for data-changing operations concentrate on the SELECT s...

Deletes that Split Pages and Forwarded Ghosts Can DELETE operations cause pages to split? Yes. It sounds counter-int...