This post explains the technical differences between automatic seeding and manual seeding for secondary replicas in SQL Server 2022 Always On availability groups. It describes how automatic seeding uses the AG transport layer to stream a virtual backup directly to the secondary, while manual seeding relies on traditional backup and restore operations. The discussion includes configuration details, compression options, and monitoring techniques. We also cover the pros and cons of both approaches.
Category: Internals
When 1,000+ Partitions in SQL Server Becomes an Anti-Pattern
SQL Server supports up to 15,000 table partitions, but going beyond 1,000 is a known anti-pattern. Large partition counts introduce memory pressure, longer DBCC runtimes, and unsupported maintenance scenarios. Queries without partition elimination often slow down, and compilation overhead grows as partitions multiply. Empty partitions also add metadata overhead and create unnecessary complexity. This post explains why 1,000 should be treated as a practical ceiling and how to design smarter partition strategies.
Avoiding the Halloween Problem in SQL Server with Safer UPDATE Patterns
The Halloween Problem can silently degrade SQL Server performance by causing rows to be updated multiple times in a single statement. This post explains how SQL Server’s internal table spool (eager spool) protects against it—and why that can hurt your workload under heavy load. You’ll learn two simple patterns—using CTEs or temp tables—to materialize target rows before the update, eliminating the need for spooky spools. Finally, we cover how batch updates help control locking, reduce escalation risk, and improve concurrency.
Optimizing SQL Server with Indirect Checkpoints and Target Recovery Time
A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to disk, and finally writes those pages to disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with SQL instances that have OLTP databases, use more memory and/or involve sequential scanning of all pages, the performance of the system could be impacted.
Restoring a Single Data Page in SQL Server: A DBAs Guide
Usually corruption in SQL Server is either nonexistent or so widespread that you have no choice but to perform a file or full‑database restore. Yet an awkward middle ground exists: a handful of pages—perhaps only one—become unreadable while the rest of the database remains perfectly healthy. A full restore would repair the damage, but at the cost of rolling back hours of work and locking users out of an otherwise functional system. That’s precisely why Microsoft built RESTORE … PAGE. You can surgically overwrite just the bad 8‑KB chunks, roll them forward with transaction‑log backups, and return the database to service in minutes rather than hours.
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.
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.
SQL Server 2019 and the Memory Grant
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