Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Execution Plan Metrics and Units of Measure

DZone's Guide to

Execution Plan Metrics and Units of Measure

I get lots of questions about database execution plan properties and units of measure. It's pretty easy to get this info — just look at Microsoft's ShowPlan Schema.

· Database Zone ·
Free Resource

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

Have you ever looked at the properties of 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 its 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 at the ShowPlan Schema. Microsoft has all the schemas 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 and what each of the values means. For instance, I can explain why SerialDesiredMemory, DesiredMemory, and 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.

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.

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

Topics:
database ,metrics ,execution plan ,schema

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}