Query Tuning Mastery - Zen and the Art of Workspace Memory

Dive into the intricacies of workspace memory management in SQL Server with this comprehensive conference talk from PASS Data Community Summit. Learn advanced query tuning techniques to optimize large, complex queries for top performance and improved concurrency. Explore expert-level monitoring methods, specialized query hints, and memory internals essential for achieving scalability. Discover how to guide the query processor in granting sufficient memory while maintaining balance for concurrent operations. Gain insights into iterators, shared memory, resource semaphores, memory fractions, and query memory objects. Understand the impact of TempDB, hash matches, and recursion on memory allocation. Master the art of workspace memory management to transform query execution times from minutes to seconds, enhancing overall database performance.

Intro
Quick Warning
Agenda
Workspace Memory
Iterators
Memory
Shared Memory
Query Running
Resource Semaphore
Exception
Question
Monitoring
Verifying
Viewing the Memory Grant
Memory Fractions
Memory Grants
Query Memory Objects
Cue Timeouts
Recap
TempDB
HashMatch
Memory Allocation
Recursion
Sort
Exchange