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.
Category: Query Optimization
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.
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.
Natively Compiled Stored Procedures with in-memory OLTP in SQL Server
Modern applications often demand lightning-fast performance from their databases, whether they’re handling large transactional workloads or complex analytical queries. SQL Server’s in-memory OLTP feature addresses these needs by using memory-optimized tables and natively compiled stored procedures to boost throughput and reduce latency. This post provides an overview of natively compiled stored procedures, how to create them, and best practices for performance monitoring and maintenance.
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
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.
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.
Handling Compatibility Level Changes During SQL Server Upgrades
Compatibility levels determine how certain features of SQL Server behave, allowing databases to maintain functionality even as the server itself evolves. However, changing compatibility levels requires careful consideration to balance the benefits of new features against the potential risks of disrupting existing applications. This blog explores the importance of handling compatibility level changes thoughtfully during SQL Server upgrades, highlighting the risks of making changes too hastily and the advantages of adopting new compatibility levels within a reasonable timeframe.
Leveraging SQL Server 2019’s Last Known Actual Query Plan for Troubleshooting
Troubleshooting production performance issues is often challenging because detailed query execution data can vanish before you realize there’s a problem. Many times, you end up piecing together incomplete clues from runtime statistics or the plan cache. Fortunately, SQL Server 2019 introduced a powerful feature to address this pain point: the Last Known Actual Query Plan.