SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Homework – January 2017 – Inspect an Execution Plan

homework-clipart-homework-alert-free-images-at-vector-clip-art-onlineIt’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)


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...