The 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:
- 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.
- 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.
- 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.
- 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
- 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.
- 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.
My no 1 stop is actual vs estimated row count. If they are out then all bets are off for a good plan.
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.
[…] The 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, […] Database, Performance Read the original post on Home Of The Scary DBA… […]
[…] Execution Plans, What Do I Look At? – Given the man has written a number of publications on the subject this is information gold. Grant Fritchey (Blog|Twitter) sharing how he gets the job done. […]
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 ?
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.
[…] at, in what order should I review, or what are the first things that indicate an issue. In this article by Grant Fritchey, we learn a little about reviewing Query Execution […]
I am not sure this is the right place to ask. If it is not, please let me know. I have a execution plan complete xml. Is there a way to get an actual query from it?
Yes, the query is in the header of the XML of the execution plan. You should be able to retrieve it from the XML, or by opening the plan inside SQL Server Management Studio and then looking at the properties of the first operator (SELECT/INSERT/UPDATE/DELETE).
Thanks. I tried that and the query is truncated in the xml as well. It is a fairly large query.
Hmmm…. OK. One other possibility. Check out the DMV sys.dm_exec_text_query_plan. That’s for plans that are excessive in size. It might, might, have the complete query. It’s just possible that you are dealing with a query that’s too big to go into the plan. It’s not something I was aware that they would truncate. Here are the details on that: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-text-query-plan-transact-sql?view=sql-server-2017
If you can, and I certainly understand if you can’t, could you send the plan to grant – at – scarydba.com? I’d like to take a look at it.
[…] plans in SQL Server is just hard. There’s a lot to learn and understand. I previously outlined the basics I use to get started when I’m looking at an execution plan for the first time. However, just […]