>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. 


Posted on March 14, 2011, in Performance Tuning, Sql Server 2005, Sql Server 2008. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: