Torsten Strauß - Deep Dive into Memory Grants: Diagnosing and Optimizing Query

### Deep Dive into Memory Grants: Diagnosing and Optimizing Query Performance in SQL

This session takes a deep technical dive into memory grants, examining how SQL Server and Azure SQL request, allocate, and consume memory during query execution—particularly for operations such as sorting and joining that require temporary data storage.
We will uncover the internal factors that influence memory grant behavior, including cardinality estimation, query plan shape, and query complexity. The session will analyze what happens when memory is insufficient—leading to spills to tempdb that can severely degrade performance—and when queries request excessive memory, whether needed or not, potentially causing other queries to wait for available memory and reducing overall system throughput. Understanding both under-allocation and over-allocation is critical to managing memory grants effectively and maintaining workload stability.
Beyond theory, the session delivers practical techniques to reduce excessive memory usage, covering index tuning, query rewrites, execution plan analysis, and a deeper look at Memory Grant Feedback, which dynamically adjusts memory allocations based on prior executions. These strategies apply across Azure SQL Database, SQL Server on-premises, and SQL Server on Azure VMs, making the content broadly relevant.
By the end of the session, you will have a comprehensive understanding of SQL Server’s memory management behavior and a set of actionable methods to improve query performance, reduce resource contention, and enhance overall system efficiency.