OK, I’ve explained how you can see the execution plan for your query (either the actual or the estimated).  So now you can see your plan…Now what?  What do you want to look for?  Here’s the plan for the query I was running when I started this post.  I was looking at the IO statistics for a server.

Thick Arrows

When you’re first getting started with execution plans, there’s only a couple of items you really need to worry with.  First up, look at the thickness of the arrows.  The thicker the line, the more rows are moving from one operation to the other.  In my case, you’ll notice all the arrows are quite thin.

If your query has some really thick arrows, roll your mouse over them, and get an idea of the number of rows you’re sending between those operators.  Could you reduce the number any?  If so, your query would run faster.

Often when I have really thick lines I’ll look for JOIN criteria that may be sending too many rows.  Sometimes I can use a different ON clause, and the number of rows will decrease.  Most of the time a change in my WHERE clause will do the trick.  Remember, your goal is to retrieve as few rows as possible to answer your question.

Hash Operations

In my case, I’m doing a Hash Match.

from sys.dm_io_virtual_file_stats(NULL, NULL) vfs
inner join sys.master_files mf
 on vfs.database_id = mf.database_id
 and vfs.file_id = mf.file_id

This usually will be a JOIN or a GROUP BY.  It’s telling you you’re either missing an index, or you don’t have an index that covers the JOIN or GROUP BY in your query.  Check the indexes you have defined, and see which is the case.

Sort

Sorts aren’t always bad.  If you have an index that covers them, then you won’t see a high cost associated with your sort.  This example shows the cost of my sort is only 1% of the overall cost.  If you see the sort is a high cost of your query, you’re probably missing an index.  Remember you can call ASC and DESC in your index.  When you have an appropriate index, sorts aren’t going to be a problem.

Large Execution Plans

If you’re having to scroll a lot to see your whole plan.  There’s probably a simpler way to do what you’re trying to do.  You may want to investigate a simpler query.

Table Scans

If you see a table scan, and the cost of the table scan is significant, you may want to consider a covering index.  Chances are you’re completely missing an index.  But if you do have an index, and you still see a table scan, check to make sure you have an index that covers both the JOIN, WHERE, GROUP BY, and ORDER BY clauses as well as the SELECT clause.  You might even want to consider a filtered index (it can save you some index maintenance costs)!

This should get you started in exploring your execution plans, but it’s by no means complete.  Grant Fritchey is just the first person that comes to mind when looking for more information on execution plans.  There are others who have materials on execution plans.  Seek it out!

Mastering your understanding of execution plans can really help when you’re trying to improve your queries.

If I can help you interpret your execution plans, let me know!  I’m always willing to help!

ES77E8HYEE8W