SQL Homework – January 2018 – Inspect an Execution Plan

6

January 3, 2018 by Kenneth Fisher

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)

6 thoughts on “SQL Homework – January 2018 – Inspect an Execution Plan

  1. notarian says:

    The dream of the 90s is alive in that Clip Art.

  2. ScaryDBA says:

    What? No plan comparisons?

  3. Ah, to re-live the joy of opening the plan xml and seeing there’s more than 1 index recommendation!

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,423 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: