A Little About Memory Grants In SQL Server Query Plans
In this video, I delve into memory grants and SQL Server execution plans, providing insights on how they work and the factors that influence their size. I share a detailed walkthrough of a query that selects top 1000 IDs from a users table, demonstrating how SQL Server calculates memory grants for sorts and how these can vary based on the join algorithm used. The video also explores the impact of string data types, particularly large VARCHAR columns, on memory grant estimates, highlighting why they can lead to significant overestimations. I discuss how memory grants are handled in parallel queries and emphasize the importance of accurate row and data size estimates for optimal performance.
CHAPTERS
00:00:00 - Introduction
00:00:56 - Query Plans Turned On
00:01:22 - Running the First Query
00:01:57 - No Index for Reputation Column
00:02:27 - Memory Grant for Sort Operator
00:02:57 - Estimated Row Size Details
00:03:41 - Memory Grants for Multiple Operations
00:04:03 - Hash Join and Shared Memory
00:04:22 - Query Plan Probing
00:04:41 - Overall Memory Grant Analysis
00:05:02 - Memory Consumption of Sort Operators
00:05:29 - Loop Join Increasing Memory Grant
00:05:41 - Nested Loops Join Comparison
00:06:12 - Query Plan Memory Grants
00:06:30 - SQL Server Memory Grant Calculation
00:07:01 - String Data and Memory Grants
00:08:09 - Large Memory Grant Example
00:08:28 - Excel File Visualization
00:09:03 - Spreadsheet Sorting Analogy
00:09:52 - String Columns and Memory Estimation
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━