Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
When I select columns from both joined tables a get a huge memory grant (529808). If I query each table columns separately it has a combined memory grant of just 8008.
Why does the combined list of columns have such a large memory grant? Or what are some of the steps I could take to find out?
SELECT * has a memory grant of 529808:
Querying just the columns from the first table has a memory grant of 6152:
Querying just the columns from the other table has a memory grant of 1856
Your first query plan with the merge join has a sort in it to support that operation. Merge joins require ordered data, and you only have index-ordered data coming from the inner table.
The outer table's index does not have the join key in a useful place in the key of the index, hence the sort.
When you SELECT *, you'll be sorting * by whatever ordering element(s) exist(s). That's a size of data operation, which you need a memory grant for scratch space to accommodate.
In the other merge join query, your sort operation will only take place on the join column, which will require significantly less memory space than the * in your first example.
Feel free to hover your mouse over the Sort operator to validate output and order by columns.
The hash join query has no sorting, but the hash join will require some memory (just less because hashes normally aren't size of data the way sorts can be) to build the hash table.
Though it's not mentioned, it may be worth noting that parallelism does not cause memory grants to increase by DOP.
