It’s a new year and yet education never ends. So this month let’s take a look at an important part of performance tuning. The Execution Plan. When a query is run, the optimizer goes through and selects what it feels will be the good enough plan to plan that query. Note: that’s good enough not best. The optimizer is designed to get that plan quickly rather than spending a lot of time trying to find a better plan.

If you want to learn about execution plans in detail I highly recommend getting Grant Fritchey’s (b/t) excellent book SQL Server Execution Plans. In the mean time here is your homework.

• View a graphical execution plan (10 pts)
  • Is it the Estimated or Actual plan? What’s the difference? (10 pts)
  • View the properties of a given operator (any one will do). If you are looking at an actual plan, how far off are your estimates? Any idea where those estimates come from? (10 pts)
  • View the properties of one of the arrows between two operators (any one will do). What does this represent? How can this information be useful? (5 pts)
  • Read the plan from left to right, then from right to left. Don’t worry if you don’t understand what each operator does yet. Which ones do you understand? Based on your query can you figure out what the plan is doing in general? (10 pts)
• View the XML execution plan (20 pts)
  • Is it the Estimated or Actual plan? What’s the difference? (10 pts)
  • Can you find the properties of the same operator you looked at in the graphical plan? (15 pts)
• Save the plan to a file for future consideration. (5 pts)
• Close all copies of the plan and open the file that you have saved. (5 pts)