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.
Author: Stephen Planck
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.
Why staying on the legacy Cardinality Estimator in SQL Server 2022 is a bad idea
Cardinality estimation (CE) is how the optimizer predicts the number of rows that will flow through each operator in a plan. Those estimates drive cost, join choices, memory grants, and ultimately latency and resource usage. SQL Server has shipped multiple CE models over time. The pre-2014 model—commonly called the legacy CE—dates back to SQL Server 7.0. Starting in SQL Server 2014, Microsoft introduced a new CE and has continued refining it in later releases, including SQL Server 2022. Keeping the legacy CE turned on in SQL Server 2022 is usually the wrong long-term choice.
Controlling TempDB Resources with Resource Governor in SQL Server 2025
Every SQL Server instance relies on tempdb as its universal scratch pad. When one poorly-written report or maintenance job floods that database with temporary objects, the entire server can stall or even go offline. SQL Server 2025 finally gives database administrators a precise brake pedal: TempDB Space Resource Governance, an extension to Resource Governor that lets you cap how much tempdb space each workload can consume.
Resumable Maintenance in SQL Server: Index Rebuilds, Index Creation, and ALTER TABLE ADD CONSTRAINT
Large tables make routine maintenance risky. Traditional online index builds and ALTER TABLE ADD CONSTRAINT operations run as a single long‑lived transaction. If an outage or fail‑over occurs near the end, SQL Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL Server 2017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017–2022) and resumable online ADD CONSTRAINT(2022).
Why SSMS 21 Feels Like a New Era for SQL Server Database Professionals
For years, SQL Server Management Studio has anchored day-to-day database work. Most improvements since the 2010s were minor interface tweaks or new wizards that left the underlying 32-bit shell untouched. SSMS 21 marks a clean break: it moves to a modern 64-bit platform, folds indispensable tools into the core product, and refreshes the entire interface without changing the familiar Object Explorer + Query Editor workflow. The result is the same trusted environment—only faster, more accessible, and far better equipped for today’s workloads.
Verifying SQL Server Backups with PowerShell and SMO
Regularly restoring test copies of your databases is the gold-standard proof that your backups work. Between those tests, however, RESTORE VERIFYONLY offers a fast way to confirm that a backup file is readable, that its page checksums are valid, and that the media set is complete. In this post you will see how to run that command from PowerShell by invoking SQL Server Management Objects (SMO), turning a one-off verification into a repeatable step you can schedule across all your servers.
Five SQL Server 2025 Enhancements DBAs Will Notice
There is a lot to be excited about in SQL Server 2025! When thinking about features that may not get as much attention as others, yet will make a real difference in the lives of DBAs, I have selected my top 5 enhancements for SQL Server 2025. These improvements may not make the headlines, but they address pain points we’ve all experienced as DBAs. Please let me know if I’ve left any of your favorites off the list.
Filtered Indexes in SQL Server: Targeted Performance in High-Volume Tables
When a table holds millions of rows yet most queries touch only a small, well-defined subset, a traditional non-clustered index feels like using a searchlight to find something sitting under a desk lamp: the index still stores an entry for every row even though the workload rarely needs most of them. SQL Server’s answer is the filtered index—introduced in SQL Server 2008 and still under-used today—allowing you to index just the rows that match a predicate you supply in a WHERE clause.
Database Snapshots in SQL Server High-Availability Setups
A database snapshot in SQL Server provides an instant, read-only view of your data at a specific point in time without blocking the live workload. This post explores how snapshots interact with high-availability technologies—Always On Availability Groups, log shipping, and transactional replication—highlighting that creating a snapshot is always safe but restoring it severs existing sync mechanisms. You’ll learn the steps required to roll back to a snapshot, including AG reseeding, log-shipping reinitialization, or replication re-initialization, and why planning for these operations is essential. The post also covers practical tips on monitoring sparse-file growth and choosing between snapshots and full backups for longer-term retention.