A Strange Place For A Memory Grant

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?

Oh you loopy things

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.

Oh so quiet.

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.

Sore thumbs.

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:

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

Previous Post
How to Reduce the CPU Overhead of Dynamic SQL
Next Post
SQL Server Setup Feature List Changes 2008-2017

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.