Understanding query execution plan

Question

I am new and i want to know what should i look for in execution plan. What does 0% or 100% mean in execution plan. How do i trouble shoot a problem in query that is slow or having time out execution.

Answer 1

The below books is a good one for you.

https://learn.microsoft.com/en-us/answers/questions/361407/understanding-query-execution-plan.html

Answer 2

Execution plans is an extremely broad topic, and it is best learnt bit by bit by getting your hands dirty with actual cases.

Here I will drop a few things. To start with, in SSMS you can see plans from two different angles, known as estimated and actual plans. These names are fully accurate, but they are established.

An execution plan is based on estimates from the optimizer, and the estimated plan only has the values the optimizer estimated (from the statistics sampled about the data). The actual plan also has the actual values. That is, the optimizer may have estimated an operator to process 1982 rows, but in reality it processes 2983 rows. If this sounds like a big deviation to you, I can tell you that this is a pretty good estimate.

The percentages you see are always based on the estimates. That is, they do not reflect the actual cost. Therefore, they are not that interesting, because many performance problems are due to gross misestimates - like one row estimated when the actual value was one million.

When I look at an execution plan, I starting with look for thick arrows, because the the thicker the arrow, the more rows are being processed, and the more time it takes. This is a little simplified, but it is a good starting point.

For a longer introduction, you can watch this presentation Execution Plans, Where Do I Start? by my fellow MVP Hugo Kornelis. I have not watched this particular presentation myself, but Hugo generally does a good job, so I am sure that you will find it informative.

Answer 3

Hi @Max buddy ,

Welcome to the TSQL Q&A forum!

The official Microsoft document has a detailed introduction to the execution plan,
you can refer to:
Execution Plans
Compare and Analyze Execution Plans

For a beginner, the following may be useful:

Place the mouse on the three icons in the lower right corner, and the function of the button will be automatically displayed.
When you click on these icons, after you execute the query, the corresponding execution plan and statistics will appear.When you put the mouse on each icon in the execution plan, the corresponding data will appear automatically:

In addition, when you want to use an index, after you create the index and execute the query, you can check the execution plan to know whether the index you created is used by the query optimizer. In other words, if you create an inappropriate index, your index will not be used in the query. And these, you can find in the execution plan. If the execution plan shows a table scan, then your index is not being used. If the execution plan shows an index scan, then your index is a suitable index, and the query optimizer uses your index.

Regarding statistics, before you execute each query, execute below statement:

DBCC dropcleanbuffers --Empty the cache        
set statistics io on --Open IO statistics        

After executing the above statement, execute your query, and then click message, you can see information similar to the following picture:

This involves logical read and physical.The smaller their value, the faster the query speed.

When you encounter the problem of slow query or query timeout, you can post a new post in this forum, and at the same time post your table and data, execution plan and other information. The people here will provide solutions based on your specific situation.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.