As some of you may have noticed, our last blog post was on February 18th. On February 19th, 2025, Yvonne Vanslageren, the cofounder of this blog and my very dear friend, passed away after a short battle with illness. She was 55 years old. Her unexpected loss has devastated her family, colleagues and myself. When I first got the news I couldn’t bring myself to touch the blog which is why I’m notifying you all of this so late.
Author: Stephen Planck
Lock Escalation in SQL Server: Understanding and Troubleshooting for Performance
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.
Understanding the “mssqlsystemresource” (Resource) Database in SQL Server
This blog post explores the critical role of the hidden mssqlsystemresource database in SQL Server. It explains how this read-only database stores essential system object definitions and simplifies patch management and upgrades. DBAs will gain valuable insights on best practices, versioning, and troubleshooting to keep their SQL Server environment secure and efficient.
Exploring Programming Constructs in T-SQL – Part 4: Common Table Expressions (CTEs) and Recursive Queries
Welcome to Part 4 of our ongoing series on T-SQL programming constructs. So far, we’ve discussed variables, conditional IF statements, loops, CASE expressions, and covered essential concepts like error handling and transaction management. Now, we turn to Common Table Expressions (CTEs)—a powerful feature in T-SQL that can simplify complex queries and enable recursion.
Understanding and Managing the Model, MSDB, and Master Databases in SQL Server
It’s easy to focus on user databases and overlook the system databases at the core of every SQL Server instance. However, the master, msdb, and model databases form the backbone of your environment. Understanding each database’s specific responsibilities, how to safely customize them, and how to protect them from data loss or corruption is important for maintaining a stable server. In this post we take a look at all three databases to better understand their purposes and proper maintenance.
Comparative Analysis: SQL Server on Linux vs. Windows for Production
For years, SQL Server was synonymous with the Windows operating system, a pairing that brought deep integration and mature ecosystem support. The arrival of SQL Server on Linux introduced a broader choice, allowing organizations to deploy Microsoft’s flagship relational database on an entirely different platform. Although both versions share the same core engine, technical differences persist in the way each handles process scheduling, I/O, security, and administration.
Exploring Spinlocks and Latch Contention in SQL Server
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.
Five Helpful Queries for SQL Server DBAs
As a SQL Server DBA, having the right set of queries can significantly streamline your workflow, enhance monitoring, and aid in troubleshooting. This post shares five essential queries that address common administrative tasks, each accompanied by a detailed explanation to help you understand their purpose, interpret the results, and take appropriate action.
Reading SQL Server’s XML Deadlock Report Captured by the system_health Event
SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.
Optimizing Table Width and Data Types in SQL Server
Organizing data effectively in SQL Server can be a juggling act. You may be tempted to make tables as all-encompassing as possible by giving columns very large data types or piling on extra fields for convenience. While this seems harmless in the short term, it can lead to heavier storage requirements, slower queries, and frequent obstacles when updating the schema. Narrower tables, carefully chosen data types, and a normalized design often work together to keep a database agile and efficient.