Jan 30 2012

Execution Plans, What Do I Look At?

lookThe question came up, what are the top 3-5 things that you look at in a query. I realized then that I hadn’t really written them down. There are some areas that let me know pretty much immediately where problems lie within a plan. You don’t necessarily know the answer from looking at these things, but you know you have a problem. Being me, I couldn’t stick to 5, so here are 6:

  1. Immediately check the properties on the first operator (SELECT/DELETE/INSERT). There’s tons of information in here and people frequently just ignore it. You can see if the plan is based on full optimization or not. That immediately tells me if I’m working on the optimizer’s best estimate at a plan or I’m looking at a timeout. If it’s a timeout, I know I can’t count on this plan being good. Also I get the parameter compile time & run time values to help determine parameter sniffing issues in the properties.
  2. Warnings. If you see no join predicate warnings, that should jump up and poke you in the eye like some jumping eye-poking little monster. Same goes with missing statistics. The new warnings in plans in 2012 are equally important to know about. These are quick pieces of information that should immediately point you in a direction of inquiry within the plan.
  3. The most costly operations. Yes, I know you can’t trust these values because they are just estimates. Yes, the estimated operator cost is the same in both estimated and actual plans. No measurements of actual cost are taken by an execution plan. But these are the numbers available, so I use them. They’re accurate more often than not and quickly lead you to the possible source of the problem.
  4. Fat pipes. Now really, these are usually just an indication of volume and knowing that you’re moving lots of rows helps you read a plan (umpty-million rows joining umpty-million rows through a Loop might be an issue). But the real alarm bells go off when you see big fat pipes going to little skinny ones or skinny ones to big fat ones or even skinny-fat-skinny. That’s a huge indicator of something
  5. Extra operators. This is like that old statement about pornography “I can’t give you a precise definition, but I know it when I see it.” It’s looking for stuff that doesn’t belong. For example, you don’t have a single ORDER BY statement, but there sits a Sort operation. Why? That’s my “extra operator” indicator telling me to dig deeper.
  6. Scans. Scans are not necessarily bad and Seeks are not necessarily good. In general terms, with smaller data sets, you usually would expect to see a Seek over a Scan. Scans can be the right, good, and best choice, especially for very large data sets and in other situations, but they are an indicator of potential issues.

After that, you have a whole slew of things you can get worked up about. Table Spools in SELECT statements are usually not good. Look for indications of multi-statement UDF’s (Scan’s with zero cost). Loop joins when a Merge makes more sense, Merges where you ought to see a Hash, missing index information, mismatch between estimated & actual, blah, blah, blah… You get the point. There’s just tons & tons of information within execution plans. But that list of six are usually the first things I look for.

6 Comments

  • By dave ballantyne, January 30, 2012 @ 5:23 pm

    My no 1 stop is actual vs estimated row count. If they are out then all bets are off for a good plan.

  • By Grant Fritchey, January 30, 2012 @ 5:45 pm

    Yep, that’s true. But, you do have to take into account the number of executions & stuff like that which could affect them. I really like knowing if I had a timeout or not. If I did, then the plan is nothing but a guess. That’s my first stop now.

  • By Raymond Dale, February 5, 2012 @ 5:54 pm

    I have 2 questions, how can I easily tell if a plan is suffering from parameter sniffing ? Can I tell this from a cached plan ? Most of the time when I have to diagnose problems, it has happened in the past and I have to look at the cached plans.

    Actual rows/Estimated rows, I’m assuming that this is only for when you run a query and view the plan then, not cached plans ? Also if the figures are not the same, does it mean its a problem with stats etc ?

  • By Grant Fritchey, February 6, 2012 @ 6:34 am

    There’s not an easy way to know that a plan is suffering from bad parameter sniffing. But, there are places in the plan that give you information to help you determine if you have bad parameter sniffing. You can look at the compile time and run time parameter values. Having those values, you can compare betweeen a good plan, with good parameter sniffing, and a bad plan with bad parameter sniffing. You can also check your data.

    If actual/estimated is off, and you don’t have another explanation such as the number of executions, yeah, that can be an indication that your stats are out of date.

Other Links to this Post

  1. Execution Plans, What Do I Look At? | Database, Performance | Syngu — January 31, 2012 @ 1:56 am

  2. Something for the Weekend – SQL Server Links 03/02/12 — February 3, 2012 @ 6:03 am

RSS feed for comments on this post. TrackBack URI

Leave a comment