Internals – Curated SQL

SQL Server and the Halloween Problem

Published 2026-10-29 by Kevin Feasel

Dualcore DBA takes a look at a historical problem:

The Halloween Problem gets its name from the day it was discovered – 31st October (some reports differ between 1975 and 1976). On this day, Donald Chamberlin, Patricia Selinger, and Morton M. Astrahan were working on the System R database project in an IBM lab and wrote a SQL query which updated a database, giving each staff member who earned less than $25,000 a 10% pay rise. The query completed, but every staff member then had a salary of at least $25,000 – this was not the expected output of the query.

They had inadvertently found a flaw in how the update was being executed – some affected records were updated more than once, until they no longer matched the filter criteria.

Click through to see how most data platforms, including SQL Server, resolve this issue.

The SQL Server Execution Plan Reference

Published 2026-10-28 by Kevin Feasel

Hugo Kornelis completes a magnum opus:

In 2017, I made the decision that SQL Server execution plans needed better documentation. I did not expect Microsoft to do this for me. And so I decided, heavily inspired by Paul Randall’s Wait Type Library, to write it myself. I purchased the domain sqlserverfast.com, migrated my blog there, and started to write the SQL Server Execution Plan Reference.

That part of the site went live on May 31, 2018, when I had finished the generic pages plus the pages on all join and aggregation operators. After that, I regularly added a page, sometimes even multiple at once, and I also frequently updated pages with new or corrected information.

Check out the amazing work Hugo has done through the years.

The Structure of Vector Indexes

Published 2026-10-16 by Kevin Feasel

Hugo Kornelis digs into another index variant:

Support for the vector data type, vector indexes, and vector search, was added as a preview feature in SQL Server 2025 and in Azure SQL Database. In the latter, the feature has then been developed further, and moved out of preview.

There have been significant changes between the original implementation, that is still available in SQL Server 2025 (as of CU9), and the current (mid October 2026) implementation in Azure SQL Database. These differences affect the available features, the syntax to use for some features, and the internals. Especially the latter is important for those who already created vector indexes with the original implementation on Azure SQL Database. Because the new version uses a different structure, these users are recommended to migrate from that earlier version to the new version. Click this link for more information.

In this post, I will look at both the original implementation, as still available in SQL Server 2025, and at the current implementation in Azure SQL Database, pointing out similarities and differences where needed.

Click through for a theoretical explanation of when vector operations are relevant as well as how SQL Server and Azure SQL DB actually store this information. Hugo may apologize for how long the post is, but on his behalf, I highly recommend giving it a read.

Storage Engines in MySQL versus MariaDB

Published 2026-09-24 by Kevin Feasel

Lukas Vileikis compares storage engines:

The big difference between MySQL and MariaDB largely revolves around database storage engines and anything related to them. In this article, I’ll walk you through some of the key differences between storage engines in MySQL and MariaDB, explain which is best for your use case, and detail how to change your database’s storage engine if necessary.

Most of you will do just fine with InnoDB on either of the database management systems – however, those of you who are building something more wild and exotic might want to look specifically into MariaDB.

Read on to learn more.

Comments closed

Tuning DiskANN

Published 2026-09-16 by Kevin Feasel

Mala Mahadevan wraps up a three-parter on how DiskANN works:

In Part 1, we saw how ‘Vamana’ represents vectors as nodes, connects them with edges, and uses greedy graph traversal to avoid comparing a query against every vector in the dataset. In Part 2, we investigated how PQ gave us compact representations for inexpensive distance calculations, caching kept useful graph nodes close, how SSDs provided capacity, and beam search allowed multiple promising nodes to be expanded together.

In this last part, we will be exploring how we use the parameters to tune DiskANN.

Click through to learn more about these parameters and what they do.

Comments closed

Deletion and Ghost Cleanup in SQL Server

Published 2026-08-13 by Kevin Feasel

Martyn Jones hunts down the ghosts:

The concepts discussed in the previous blog post can also be seen in the transaction log. The logical removal of rows (by marking them as ghost records), the associated allocation metadata updates, and the eventual physical removal of those rows by the ghost cleanup process are recorded as individual transaction log operations.

The demo code uses the undocumented function fn_dblog(), this provides a clear view of the internal sequence of events that SQL Server performs during a delete operation so the individual physical changes required to implement ghosting, and later, cleanup can be studied.

Click though to see how SQL Server marks ghost records, tracks where they are, and performs cleanup.

Comments closed

Digging into Postgres Plan Caching

Published 2026-08-13 by Kevin Feasel

Jordan Boich runs an experiment:

In my last post, I talked about how I was amazed that PostgreSQL doesn’t have a shared plan cache like SQL Server does. I wanted to create an experiment / lab where I could see this in action, so I built one. I’m going to be leveraging a free PostgreSQL database hosted on an Azure Flexible Server. I’ll also be using DBeaver to connect to my PG Database.

Click through to see what Jordan learned.

Comments closed

Page Splits in Animated Form

Published 2026-07-23 by Kevin Feasel

Brent Ozar paints a picture:

You’ve heard that page splits are bad, and they’re an indication that your table design is making your storage work too hard. You’ve heard that the right answer to fix it is adjusting fill factor lower, or doing regular index maintenance.

Before you watch the below animation, you’ll wanna get up to speed with how index seeks work. Then, let’s explain page splits with an animation:

Click through for the visual explanation.

Comments closed

JSON Index Internals in SQL Server

Published 2026-07-20 by Kevin Feasel

Hugo Kornelis looks into one of the newest types of index available in SQL Server:

It’s time to continue the series on internals of storage structures. I already covered the more standard storage types in the parts about on-disk rowstore, columnstore indexes, memory-optimized storage, and memory-optimized columnstores, and in the last episode of this series I started to cover specialized storage structures by looking at XML indexes.

Microsoft introduced limited JSON support in SQL Server 2016. However, it took until SQL Server 2025 before the native json data type and JSON indexes were introduced. So let’s look at how these work under the cover.

Click through to learn more about how SQL Server stores the contents of a JSON index.

Comments closed

Impacts of Disabling Ghost Cleanup in SQL Server

Published 2026-07-17 by Kevin Feasel

Martyn Jones performs an experiment:

The first blog post introduced the ghost cleanup process, why it exists and how it works. The second demonstrated the process in action, including monitoring with Extended Events.

This post explores the ghost cleanup process further, including how it can be disabled and a high-level view of the potential impact. It also dives into data pages to show how ghost records are marked.

Disabling the ghost cleanup process can help expose internal behaviour more clearly, making it easier to observe how ghost records are marked and retained on pages.

Read on for the trace flag that can do it, as well as the consequences of doing it.

Comments closed

Category: Internals