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.
Author: Stephen Planck
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.
Working with Database Snapshots in SQL Server
Database snapshots are one of those features that’s been around forever, but still solves real-world problems with very little setup. In a single statement you can capture a point-in-time, read-only copy of any user database, use it for reporting or off-load testing, and—if disaster strikes—revert the source back to that snapshot in minutes. This guide explains how snapshots work under the hood, walks through day-to-day tasks (including creating the original database), and highlights the pitfalls you should plan for before using them in production.
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.