Internals – Curated SQL

New Objects in SQL Server 2025

Published 2025-05-21 by Kevin Feasel

The first public preview of SQL Server 2025 dropped yesterday, and here’s what’s new under the hood as compared to today’s SQL Server 2022 CU19. Forgive me – this is quite a lengthy post, and it’s mostly a data dump. There are new stored procedures, extended stored procs, views, feature switches, new columns in existing tables, messages, and more.

I suspect that the contents of the new undocumented sys.dm_feature_switches table alone will get y’all really curious because there are 5,910 lines in there (included in this post) that suggest all kinds of wild things Microsoft is currently working on, but hasn’t announced publicly yet.

Read on for the very long list.

Data Recovery in SQL Server without a Backup

Published 2025-05-13 by Kevin Feasel

Rodrigo Riberio Gomes digs in:

In more than 10 years of experience, I have dealt with cases where someone has performed incorrect operations on a table, such as updating or deleting wrong rows, in a SQL Server database that does not have full backups available. There are multiple reasons for no full backup: corrupted backups, taking too much time to restore, etc.

In this post, I want to show an alternative for these cases, an ace up one’s sleeve, that you can use to recover data. This method also provides a deep understanding of the internal workings of how your SQL Server stores data. So, in addition to learning how to recover data, you will gain more insights into the internals of SQL.

Read on to see how. Rodrigo also points out some limitations or things that would need to change if you have index compression. I consider this a very neat thing you might need to know but never want to use.

A Dive into Oracle Memory

Published 2025-04-18 by Kevin Feasel

Kellyn Gorman remembers:

When describing Oracle features to folks that are new to the platform or coming from other database platforms, I found it’s best to keep it simple, but take on the important aspects of the technology. I’m going to take on a few more posts on Oracle internals from the perspective of the individual new to Oracle.

To start, I’ve been having some long discussions, as well as documenting how Oracle memory works and how we monitor it when you don’t have the diagnostic and tuning pack at your disposal. With the exclusion of these management packs, you have to be intentional in your queries to provide the data, without violating the licensing by using the %_HIST_% views.

Click through for an overview of how Oracle makes use of memory and how you can monitor this usage.

Comments closed

Understanding Multi-Version Concurrency Control in PostgreSQL

Published 2025-04-03 by Kevin Feasel

Grant Fritchey explains a mechanism:

Let me start by giving you the short version of what MVCC is, and then the rest of the article explains more details. Basically, PostgreSQL is focused on ensuring, as much as possible, that reads don’t block writes and writes don’t block reads. This is done by always, only, inserting rows (tuples). No updates to an existing row. No actual deletes or updates. Instead, it uses a logical delete mechanism, which we’ll get into. This means that data in motion doesn’t interfere with data at rest, meaning a write doesn’t interfere with a read, therefore, less contention & blocking. There’s a lot to how all that works, so let’s get into it.

Click through for the dive. The pattern for MVCC is interesting, though quite different from pretty much any other implementation of concurrency management in a relational database system.

Comments closed

Identifying an Object Name from a Wait Resource

Published 2025-03-18 by Kevin Feasel

Haripriya Naidu wants to know what object this is:

You run a query to check for locking or blocking in SSMS and find a wait resource in the format (8:1:3610). To identify the object name, you would typically run multiple queries, first find database name, turn on trace flag 3604, then find object id from DBCC PAGE and then find object name from sys.objects.
However, with a new function “sys.dm_db_page_info” introduced in SQL Server 2019, you no longer need to go through these steps. Instead, you can run a single query to get the object name directly.

Read on to see how it all works. This is definitely a lot easier than in the olden days.

Comments closed

Reviewing Azure SQL DB Internals for Hints at SQL Server 2025

Published 2025-03-14 by Kevin Feasel

Brent Ozar does some digging:

Microsoft staff used to say that Azure SQL DB is “the next version” of the boxed product, meaning that Microsoft tested, tweaked, and proved new features in the cloud while they could still rapidly iterate over code, shipping updates to make the product more reliable and scalable. In practice, that’s not entirely true: some things never leave the cloud, and some things actually ship to SQL Server long before they’re available up in Azure SQL DB.

Today, we’re going to go through documented & undocumented system objects that are available up in the cloud today (March 2025), but keep in mind that some of these may be cloud-only. I’m just dumping out the list of Azure SQL DB objects that aren’t present in SQL Server 2022 CU17.

Read on for those sets, with the caveat that not all of this may necessarily be in SQL Server 2025.

Comments closed

Lock Escalation in SQL Server

Published 2025-02-20 by Kevin Feasel

Stephen Planck talks lock escalation:

Lock escalation is a built-in mechanism in SQL Server designed to optimize how the database engine manages locks. By reducing thousands of fine-grained locks (such as row or page locks) to a single table-level lock, SQL Server aims to lower overhead in the lock manager. In practice, lock escalation saves system resources but can also introduce challenges in high-concurrency environments. This post explores why lock escalation happens, how it works under the hood, the problems it can cause, and strategies to prevent or mitigate unwanted escalations.

Read on for the full story.

Comments closed

Checking out the mssqlsystemresource Database

Published 2025-02-17 by Kevin Feasel

Stephen Planck goes into single-user mode:

Have you ever wondered where SQL Server actually stores the definitions for system objects like built-in stored procedures, system views, and functions? Enter the mssqlsystemresource database—often just called the “Resource” database. While this database remains hidden from everyday use, it plays a pivotal role in the internal workings of SQL Server. In this post, we will explore what the Resource database is, why it’s necessary, and what every DBA should know about it.

Read on for a primer on what’s in this database and why we typically shouldn’t mess with it.

Comments closed

Drawbacks of Version Store in SQL Server

Published 2025-01-28 by Kevin Feasel

Haripriya Naidu points out that nothing comes for free:

With every feature comes both advantages and disadvantages and it is important to be aware of both. In regards to version store, we had discussed how to understand version store better and how version store is enabled.

Today, we will talk about the trade-offs and implications that come along when version store is enabled.

Click through for the downsides. With storage prices and speeds where they are, the tradeoffs of using the version store for RCSI or Snapshot isolation are almost always worth it. But it’s still good to know what those trade-offs are and how you can end up in a degenerate state.

Comments closed

The Contents of a Database Page in SQL Server

Published 2025-01-23 by Kevin Feasel

Simon Frazer takes a peek:

In SQL Server, data is stored in tables. Behind the scenes, however, these tables are divided into 8-kilobyte (8 KB) units called pages. Each page is 8,192 bytes in size, and this is a fixed value that cannot be changed.

Out of the 8,192 bytes, the first 96 bytes are reserved for the page header, which stores metadata about the page. This leaves 8,096 bytes for storing rows of data. However, a single row can only use up to 8,060 bytes, as 36 bytes are reserved for additional metadata, such as the slot array, versioning tags, and forwarded record pointers.

Read on for more information about the fundamental unit of storage in SQL Server.

Comments closed

Category: Internals