There are many types of memory allocations in SQL Server. One such memory grant is for query execution. The relational engine needs memory to execute your query. More memory is needed if you have expensive hash and sort operations. Let’s take the example of the SORT operator. This is an expensive in-memory operation. And if enough memory is not available, the operation spills down to disk. The memory requirement increases and is directly proportionate to the size of the data it’s sorting. Sorting a million rows will need more memory than sorting a thousand rows. Unfortunately, it has to sort all the data before it can output the first row. And that is why it is a blocking operator too. In the execution plan, when you take the cursor over the SELECT operator, you can see the memory grant for the query execution. You can also see the overall query cost. Keep an eye on the memory grant. Sometimes, it will run into GBs – one of the reasons for the sudden memory burst (requirement). The optimizer estimates the amount of memory needed based on the estimation of the data size it has to sort. This memory is reserved at the beginning of the query execution by a memory clerk called MEMORYCLERK_SQLQERESERVATIONS. You can track this clerk and many others using the DMV sys.dm_os_memory_clerks. Sounds interesting? 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.