Does Workspace memory (part of buffer pool) is limited for only Hash Join, Sort etc. only?
If I have a query that does use a nested loop join (wihtout a sort or hash), would it not use workspace memory?
Where would worktable for a simple query that return 100 rows will be created? in Tempdb..
1 Answer 1
If a query does not have a memory grant then it cannot request memory from this 'workspace'. You can see which queries request memory in sys.dm_exec_query_memory_grants. As you already noticed only certain operators request memory grants, namely the operators that require some significant intermediate storage. A nested loop does not require any storage, as it operates one row at a time, therefore it will not request a memory grant. A hash requires memory to build the hash tables, and a sort obviously requires sort tables. Queries without a grant still need some memory, but that adds up to trivial amounts (maybe a few KBs) and such memory is obtained from ordinary execution allocators. A grant is measured usually in MBs and can go into the 100s of MBs.
The memory reserved for grants is not exactly 'part of the buffer pool', which makes it sound like is a separate portion of the buffer pool. Is more of a gray area. The memory grant is backed by the buffer pool. A query that is granted X amount of memory places a reservation on the BP. It does not 'steal' the pages from BP right away. But since the (unstolen part of) BP contains data pages, the BP can make good on the reservation promise because it can evict data pages to disk anytime it likes. As the query makes progress it may request pages from the BP from the grant it acquired (from the reservation) and as the BP fulfills the request, the pages handed out to the query become really 'stolen', ie. the BP can no longer use them for data until the query release them back. In order to ensure all grants can be honored the sum of reservations cannot exceed the BP size (ie. there is no VM style over-allocation). These grant reservations tend to err on the overallocation side by a generous margin as things can turn out quite ugly if a query consumes all the grant and still needs memory: spills occur (extremely slow) or the query may give up and error.