Analyze Actual Execution Plan

One of the many new sets of functionality introduced in SQL Server Management Studio 17 is the new option “Analyze Actual Execution Plan.” If Microsoft continues down this path, there will be a lot of useful functionality at some point. If you haven’t yet looked at Analyze Actual Execution Plan, well, read on.

Analyze Actual Execution Plan

To get to the new functionality, you have to have an Actual Execution Plan open within SSMS 17. After that, it’s just a matter of right clicking to bring up the context menu:

If you select the menu choice, then a new window opens at the bottom of the execution plan, showing each of the batch statements in one tab, and some interesting stuff in the next tab:

You can click that to make it larger.

While the tab says ‘Scenarios’, as I write this, there is only a Scenario, Inaccurate Cardinality Estimation. The scenario looks at your plan and lets you know which of the operators have disparate cardinality estimation. This is why you only, currently, can run this analysis against Actual Plans. Clicking on one of the nodes brings up details and suggestions as well as highlighting the node/operator, within the plan.

Conclusion

Based just on how they’ve set this up, I expect Microsoft to continue to add to the scenarios within the Analyze Actual Execution Plan window. I’m curious to see what’s next. However, I’d suggest you check out this functionality so that Microsoft can see its use in telemetry. That’ll spur them to develop more.

---

If you want to learn more about how Analyze Actual Execution Plans works, as well as a ton of other tools to make query tuning easier, I have an all day seminar on the topic that I’m giving all over the place:

For SQLSaturday Indianapolis (and my first time speaking in Indiana) on August 10, 2018. Please go here to sign up.

I’ll be at SQLSaturday Sioux Falls (and my first time speaking in South Dakota) on August 18, 2018. Please sign up here.

For SQLSaturday Oslo on August 31, 2018. Click here right now to register.

I’ll be at my local event too, SQLSaturday Boston, on September 21st. You can go here to register.

I’m going to be presenting at SQLSaturday Munich on October 26, 2018. Go here now to join the class.