Tip to Analyze Execution Plan

Hey, everyone. Today’s idea is sharing what I do first when users come to me with a performance issue, it really helps me understanding where the issue could be and all it takes is running the query.

As a reminder, when you have performance issues, you probably can’t replicate it in the development environment because your SQL server is not under the same pressure, it probably doesn’t have the same resource level as well.

So, the first think I like to do once I get the performance complaint, is running the query to get the Actual Execution Plan. It’s important to notice that if the query is too long or runs through lots of data, we might not be able to get the Actual Execution Plan, so we will have to resort to the estimated one.

Once we get the execution plan, and I am attaching one here just as an example.

Of course, this plan is simple enough and easy to read, but it is still useful to share what I wanted within this post which is you should start reading from right to left, top to bottom, and I find the first place in which estimates x actual are completely wrong.

So, in order to know what to look for, we need to establish a few things first. First, the percentage that you can find in the cost section is a number made up by Microsoft. And basically, the only thing that you can trust is the estimates x actual number which you can on the last two lines. For example, on the last operator, SQL estimated it would find 9040 rows and it actually found 3488.

Like I said, this was a simple query, so having bad estimates is not an issue. However, if you join tables and they start with the wrong estimative, then it will only get worse throughout the rest of the objects.

This issue can be solved in many ways, we can create a better index which will create new statistics, we can rewrite the query, or we can use temp tables/CTEs to help SQL server have a better estimate.