Have you ever looked at the properties inside an execution plan and wondered what the units of measure were? I know I have. I also get the question pretty frequently about all sorts of properties. What does EstimatedAvailableMemoryGrant display it’s units in anyway? For that matter, what the heck is an EstimatedAvailableMemoryGrant?
Showplan Schema and Units of Measure
The answer to those questions and a whole lot more is pretty easy to find. You just have to look to the ShowPlan Schema. Microsoft has all the schema’s published, going back to 2005. The next time you’re faced with a question such as, what are the units of measure of the MemoryGrantInfo:
Or, what the heck is SerialDesiredMemory and why is it exactly matching RequestedMemory, you just have to go and look at the Schema.
Why?
Because the showplan schema contains notes throughout stating what the units of measure are, what each of the values means. For instance, I can explain why SerialDesiredMemory, DesiredMemory, RequestedMemory are identical:
…Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are estimated during query compile time for serial execution. The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism. SerialRequiredMemory: Required memory in KB if the query runs in serial mode. The query will not start without this memory. SerialDesiredMemory: Memory estimated to fit intermediate results in KB if the query runs in serial mode. RequiredMemory: Required memory in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialRequiredMemory. …
That’s taken directly from the 2017 schema. The units of measure are KB.
On a side note, what you see above is how you should be copying & attributing content. First of all, use a very small sub-section of the content. Also, lay the text out so it’s clear that it’s a quote. Finally, add full attribution and a link to the original. Sorry. Unfortunately I’ve been dealing with some plagiarism lately and it’s gotten irritating.
Conclusion
Prior to today, I didn’t know this information existed. This information is available and has been for years and years. I just found it. I thought I’d share. Now you have an authoritative resource for exactly what some values mean within an execution plan.
I sure could have used this the first two times I wrote that book on execution plans. Luckily, I’m working my way through the third edition right now. I am adding this to the information within the book.
If you want to get together and talk query tuning, I’m doing an all day seminar prior to SQLSaturday Virginia on March 23rd, 2018. Seating is extremely limited, so please sign up early.
Also, if you want to sit down and talk, one-on-one, over a glass of rum, TechOutbound is hosting their cruise event on the week of 10 February, 2018. It’s an opportunity that’s hard to describe. I’ve seen it change people from those who have jobs to those who have careers. Go here, get signed up because seating is very limited. I can offer you a deal though, $100 off registration. Use the code BFFGF.
The post Execution Plan Metrics and Units of Measure appeared first on Grant Fritchey.
