SQL Server: Execution Plan Major Points in Query Optimization

In response of earlier post How to Find Costliest Nodes in Graphical Execution Plan few readers asked that beside relative cost of a node, what else is important when examining an execution plan? I think it’s a bit major topic and can not be covered in just one post. But here are some important points which almost every DBA keeps in his mind when examining an execution plan for query optimization.

• When execution plan is for batch of multiple statements, first find and focus on most costly statements.

• Examine costly statements plan closely to find out nodes with highest relative cost.
• Among physical joins, hash join is most expensive. Find out hash joins in execution plan and try to remove the cause behind hash joins.
• Bookmark lookup are performance killers and bookmark lookups for large result set can cause a large number of logical reads. Use covering indexes to avoid bookmarks lookups.
• Keep eye on sort operations in execution plan. This is an indication that required data is NOT being retrieved in correct order.

• Execution plan may indicate some warnings (as shown in picture). Resolve these warning situations on priority basis.

• Thickness of joining arrows between nodes indicates number of rows that are being transferred between these nodes. There are chances that node on the left of narrow, requiring unnecessary large number of rows.