If You Hang Around Fetish Bars Long Enough…
You learn things about memory grants in execution plans.
Stuff like the things that usually need memory
- Hash Join
- Hash Match
- Sort
- Parallelism Exchanges
But there’s something that rarely gets discussed, even in the deepest, darkest, dankest dungeons.
Let’s loosen the clamps and look at it.
Keys and Loops
In this innocent, unassuming, serial, hash and sort free plan, what could be asking for memory?
But here we are with a 5.3 MB memory grant in the small version of Stack Overflow — the one Brent trimmed down to only data up to 2010.
It’s A Lot Like Life
There’s a few different types of Nested Loops Joins. Some may perform prefetching, where they go out and grab rows in anticipation of needing them. Those can be ordered or unordered, depending on the query.
Sometimes, they’re also optimized.
You can either dig through a bunch of XML, or just get the properties of the Nested Loops Join.
A couple things will stand out about it.
One is that the operator received and output memory fractions. The other, and more obvious thing, is that the Optimized attribute is true.
These aren’t normal attributes of a Nested Loops Join operator.
It’s hard to find a lot of good information about them, so here are blog posts I recommend:
- OPTIMIZED Nested Loops Joins – Craig Freedman
- Nested Loops Prefetching – Paul White
- Batch Sort and Nested Loops – Dmity Piliugin
Anyway, if you ever see a very simple looking plan asking for an odd memory grant, this could be why.
Thanks for reading!
Brent says: I would just like to go down on record as saying I am going to forget this, and I’m going to be looking at a plan in the near future, wondering where the memory grant comes from, and Erik’s gonna say, “It’s right here, you moron, and why don’t you remember my blog posts? You even wrote a Brent Says on there, for crying out loud.”
