Internals – Curated SQL

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

Storage of XML Indexes in SQL Server

Published 2026-06-19 by Kevin Feasel

Hugo Kornelis continues a series on storage structures:

After covering on-disk rowstore, columnstore indexes, memory-optimized storage, and memory-optimized columnstores, it is now time to turn our attention to storage structures that are used for specific datatypes only. The first “victim” will be the XML index.

When you need to store XML data in a SQL Server database, you can choose between two data types, each with their pros and cons. You can choose to store the XML data as nvarchar(max). This preserves the exact content of the XML as you received it, which might be required for legal reasons. However, any query that cares about the specific content of the XML data will have to resort to very complicated string expressions.

The xml data type shreds the XML and stores the content in an internal format, that allows SQL Server to work with for instance XQuery or XPath expressions. This format also saves space as compared to the nvarchar(max) alternative. However, when you query the data, the content will be the same, but formatting and whitespace might be different.

The latter also allow you to build indexes on top of them, and those indexes are what Hugo covers.

Comments closed

Log Records in SQL Server

Published 2026-06-18 by Kevin Feasel

Paul Randal explains what a log record is:

The simplest definition of a log record: it describes a single change to a database. A single operation in the database may cause multiple changes, but each change will usually have its own log record to describe it. An example of this is updating a column in a single row—it will do the following:

Read on to see what it will do, what it looks like, and what kinds of log records exist.

Comments closed

Category: Internals