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..


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 built the hash tables, and a sort obviosuly requires sort tables. Queries without a grant still need some memory, but that adds up to trivial ammounts (maybe few KBs) and such memory is obtained form 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 exactlty 'part of the buffer pool', that 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 styll needs memory: spills occur (extremly slow) or the query may give up and error.

See Understanding SQL server memory grant

  • Remus, your answer it little complicated for me to understand. Very few people know SQL Server internals as good as you..:-) Is there any other resource that explains this better than the blog post you posted – SQL Learner Jul 24 '12 at 13:40
  • Jay's post is the only resource I know of which is really correct (I know the stuff I explain also from Jay, so there is no better source...). Perhaps you can be more specific what part you don't understand... – Remus Rusanu Jul 24 '12 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.