Press "Enter" to skip to content

Category: Internals

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.

Leave a Comment

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

SQL Server 2019 and Row Mode Memory Grant Feedback

Published 2025-01-23 by Kevin Feasel

Yvonne Vanslageren explains a solid feature in SQL Server 2019:

When SQL Server runs a query, it needs memory for operations like sorting and joining data. It also relies on memory during query compilation to hold intermediate plans while the Query Optimizer finds the best execution strategy. In parallel processing scenarios, the memory requirement grows even further.

SQL Server manages this by pre-allocating memory for each query through the SQL Server Operating System (SQLOS). This process ensures that no single query can monopolize the server’s memory.

Read on to learn more about memory grants, problems you can run into with memory grants, and one way SQL Server 2019 has improved to reduce the risk of bad memory grant estimates.

Comments closed

An Overview on Spinlocks in SQL Server

Published 2025-01-20 by Kevin Feasel

Stephen Planck talks spinlocks:

High concurrency can expose subtle performance bottlenecks in SQL Server, particularly those stemming from spinlocks and latch contention. Both mechanisms exist to synchronize access to shared data structures, yet they operate differently and require distinct troubleshooting approaches. By recognizing how they work and knowing what causes them to overload a system, DBAs can reduce CPU spikes, timeouts, and overall application slowdowns.

Read on to more about spinlocks and latch contention. My experiential bias is that spinlocks are the actual problem approximately 5% of the number of times that DBAs believe spinlocks are the actual problem.

Comments closed

Understanding the SQL Server Version Store

Published 2025-01-20 by Kevin Feasel

Haripriya Naidu digs into tempdb:

This article discusses when version store starts generating rows, when cleanup takes place, and how TempDB space gets occupied due to this activity.

I’ve hardly used PerfMon much in the past, nor was I a fan of it, just like many of you. But, when I used it a few times to determine how many data files needed to be added to TempDB while resolving contention, it helped me make a clear decision on the number of files needed. Since then, I’ve become a fan of PerfMon.
Although I don’t use it daily, I don’t hesitate to turn to it when I think it might help me understand things better.

Coming back to our topic of version store in this article, I resorted to PerfMon counters because visually watching the lines go up and down when an update is performed on a table helped me better understand how the version generation and cleanup works.

Click through for a demonstration of the process, including relevant PerfMon counters and the type of information you can get directly from SQL Server.

Comments closed

Reading Data from Deleted Columns

Published 2025-01-07 by Kevin Feasel

Michael J. Swart checks the typewriter ribbon:

It’s hard to destroy data. Even when a column is dropped, the data is still physically there on the data page. We can use the undocumented/unsupported command DBCC PAGE to look at it.

This is tied in with how we can drop a column in SQL Server and have it not take a very long time: because when we drop the column, we’re just modifying the metadata associated with the table and telling SQL Server to ignore this bit here. Do read the whole thing, and also check out a fun comment from Paul White.

Comments closed

Handling a Consumer Fetch Request in Kafka

Published 2024-11-27 by Kevin Feasel

Multiple Confluent employees (who apparently don’t get to have names this time around) wrap up a series:

It’s been a long time coming, but we’ve finally arrived at the fourth and final installment of our blog series. In this series, we’ve been peeling back the layers of Apache Kafka® to get a deeper understanding of how best to interact with the cluster using producer and consumer clients.

Read on for the final part, as well as links to previous parts if you missed them.

Comments closed

The Difficulty of Deletion in PostgreSQL

Published 2024-11-25 by Kevin Feasel

Radim Marek takes us through the process of deleting rows:

Your database is ticking along nicely – until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance, and make maintenance a nightmare. Worse, retaining some types of data without valid justification might even lead to compliance issues.

Read on to learn about the process, some challenges, and a common pattern for resolving these challenges. The solution is pretty similar in SQL Server as well: batching delete operations, ideally with a supporting index.

Comments closed

Query Hash Value Changes

Published 2024-11-25 by Kevin Feasel

Kendra Little digs into a problem:

This is the worst bug I’ve found in SQL Server to date. Previously, my top find was SQL Server Online Index Rebuild sometimes happens offline without warning. This one has taken top slot because it makes my life more difficult on a daily basis.

Background: SQL Server generates a query_hash for each query. This is stored in sys.query_store_query and it’s one of the primary ways you can identify what a query is across different Query Stores, or even the same Query Store over time, as surrogate query_id values get reset if Query Store is cleared or data ages on. The query_hash is a “Zobrist hash over the shape of the individual query, based on the bound (input) logical query tree. Query hints aren’t included as part of the hash.” (Source)

Except that’s wrong.

Read the whole thing.

Comments closed