Mystery of memory fraction in Showplan XML

If you examine Showplan XML, you may find an entry like <MemoryFractions Input="1" Output="1" /> on some operators . They are internal parameters used by query memory grant to save overall memory usage, and “fraction” means how much this operator is allowed to use from “total additional memory” (please see previous post for definition on additional memory).

For example, let’s consider the following hypothetical plan:

SORT – HJ (hash join) – complex build side child
              |--- probe side child

Since server knows HJ does not need to access its build-side once hash table is built, it can delay creation of sort table until HJ is ready to output joined rows. Then SORT can reuse memory previously used by HJ build side child. This is done by observing input (building hash table) to output (probing) phase transition by HJ.

So what’s the practical use of these values? Not much beyond confirming memory grant usage, if the fraction is comparable to 1. If it is very small (say 0.1), however, the query may be susceptible for bad cardinality estimate. If you experience bad performance due to disk spilling and see such small number in Showplan XML, then it may be a good starting point for investigation.

--- Jay Choe, SQL Server Engine

Comments

• Anonymous
  December 26, 2011
  Hi ,I got lots of very useful skills from this blog, but it seems has not updated for a long time :-(