This is one among those thousands of architectural beauty inside SQL Server engine that make me fall in love with it. When you send your query to SQL Server, sometimes, it may need an extra memory grant for its execution. For example, let’s say the query has an expensive sort to deal with. While the query optimizer is working on an efficient execution plan for your query, it knows that a lot of memory will be needed and just before execution it will want to reserve that memory upfront. So just before the execution begins, the SQLQERESERVATIONS Memory Clerk gets into action and reserves the required memory upfront. Now fathom this: your query might need GBs of memory grant. Does this mean that the clerk will be able to secure all the memory your query needs? In other words, will the engine grant all of it? No. There are other workloads too that need a slice of memory, CPU & IO. In such a case, your query will wait on the RESOURCE SEMAPHORE wait type for the memory grant. To Know more watch the video below.

Get new content directly in your inbox – Click here

You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).

Hope the video was worth your time.

Happy Learning.

Learn more about SQLMaestros Products & Services: Video Courses| Hands-On-Labs | Learning Kits | Health Check Service