August 7, 2014 at 12:08 pm
The attached query plans are for the same stored procedure.
Nightly, we rebuild indexes which forces the stored procedure to recompile. The "good" plan is the correct one for the stored procedue. Occassionally, we get the "bad" plan but cannot figure out why. (We correct the situation by recompiling the stored procedure which gives us the "good" plan.)
The "bad" plan takes over a minute to execute while the "good" plan will do it in 8 seconds. We are baffled as to why the "bad" plan is created in the first place.
Any ideas?
August 7, 2014 at 12:39 pm
This is most likely a parameter sniffing issue. The "bad plan" has a compiled value of 40 for marketID and the "good plan" has a compiled value of 17. I assume these market IDs will have a big difference in rows returned or atleast have a big skew in the rcd_community table.
The probable reason why it has a good plan sometimes them goes to bad plan is that the good plan gets bumped out of cache and the next time the proc is run it is done so with a parameter that produces a bad plan.
if you wanted to test this out, recompile the proc, then immediately run it with a value of 40 for marketID , the bad plan should be produced.
August 7, 2014 at 12:48 pm
I always thought that parameter sniffing problems had more to do with ranges than with direct seeks.
If it was a parameter sniffing problem then shouldn't I be able to see a query plan similiar to the "bad" plan when I execute the query directly with a value of 40? I tried that (and with a value of 17) and got the same plan as the "good" plan.
August 7, 2014 at 12:52 pm
once the plan is created, that plan will always be used until it is bumped out of cache or invalidated (recompiling proc invalidates plan). recompile your proc then run it with marketID = 40, the bad plan should be produced.
here is a good post- http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
August 7, 2014 at 2:10 pm
Roger Sabin (8/7/2014)
I always thought that parameter sniffing problems had more to do with ranges than with direct seeks.If it was a parameter sniffing problem then shouldn't I be able to see a query plan similiar to the "bad" plan when I execute the query directly with a value of 40? I tried that (and with a value of 17) and got the same plan as the "good" plan.
Take a scenario where 90% of a 100M row table has a single value for fieldA, which has a non-clustered index on it. All the rest of the 10M rows have no more than 5 rows with any given value. Now suppose you hit that table where fieldA = 90%Value. You clearly want to scan that table to gather those 40M rows. But if you go in with fieldA = Mom&PopValue then you definitely want to do a non-clustered index seek and bookmark lookup to get to those 4 rows. If you cache that plan and call it with the OTHER type of value than the one cached you get a HORRIBLY inefficient plan! And the worst thing is that it doesn't matter which direction you go - you get screwed either way. That is the classic data-value-skew-induced parameter sniffing/plan caching issue.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2014 at 2:32 pm
Is there any way to "prove" that the issue is a data skew problem? This is a production system so I can't just play with trying to force a bad plan.
Would running the query with a varable set to the value found in the "bad" plan create the same "bad"plan if it was because of data skew?
August 7, 2014 at 2:46 pm
Roger Sabin (8/7/2014)
Is there any way to "prove" that the issue is a data skew problem? This is a production system so I can't just play with trying to force a bad plan.Would running the query with a varable set to the value found in the "bad" plan create the same "bad"plan if it was because of data skew?
Try OPTIMIZE FOR, which you can use to force the optimizer to use a particular value. That will show you the plan differences you will get. Also just do an aggregate value check on table:
select fieldA, count(*)
from mytable with (nolock) --don't lock out production system
group by fieldA
order by count(*) desc
OPTION (maxdop ??) --throttle on production system?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2014 at 3:01 pm
I tried what you suggest and it does force the "bad" plan when I use 40 and a "good" plan when I use 17. Thanks.
August 7, 2014 at 3:33 pm
Roger Sabin (8/7/2014)
I tried what you suggest and it does force the "bad" plan when I use 40 and a "good" plan when I use 17. Thanks.
So now you verified what is happening the "fix" is up to you.
As Kevin suggested, you can use optimize for (which I have used on a few occasions) which will always create a plan for a specific parameter or if you use unknown then it will build a plan based on all stats for all values. in either case you will not always have an optimal plan.
Another option is to add with recompile to the proc which will generate the best plan for each parameter at the expense of having to recompile each time it runs. Depending on your workload and resources this may be acceptable or not.
yet another option would be to refactor the proc so it always generates the same plan.
August 7, 2014 at 4:07 pm
Robert klimes (8/7/2014)
Roger Sabin (8/7/2014)
I tried what you suggest and it does force the "bad" plan when I use 40 and a "good" plan when I use 17. Thanks.So now you verified what is happening the "fix" is up to you.
As Kevin suggested, you can use optimize for (which I have used on a few occasions) which will always create a plan for a specific parameter or if you use unknown then it will build a plan based on all stats for all values. in either case you will not always have an optimal plan.
Another option is to add with recompile to the proc which will generate the best plan for each parameter at the expense of having to recompile each time it runs. Depending on your workload and resources this may be acceptable or not.
yet another option would be to refactor the proc so it always generates the same plan.
Or use a hint(s) to force SQL to use a similar plan.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 7, 2014 at 6:12 pm
Robert klimes (8/7/2014)
Roger Sabin (8/7/2014)
I tried what you suggest and it does force the "bad" plan when I use 40 and a "good" plan when I use 17. Thanks.So now you verified what is happening the "fix" is up to you.
As Kevin suggested, you can use optimize for (which I have used on a few occasions) which will always create a plan for a specific parameter or if you use unknown then it will build a plan based on all stats for all values. in either case you will not always have an optimal plan.
Another option is to add with recompile to the proc which will generate the best plan for each parameter at the expense of having to recompile each time it runs. Depending on your workload and resources this may be acceptable or not.
yet another option would be to refactor the proc so it always generates the same plan.
I was definitely NOT espousing the use of OPTIMIZE FOR as a SOLUTION for this issue - just to expose it. I DESPISE that "feature", because it GUARANTEES you will get a BAD PLAN for at least some of your executions, potentially many of them!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 8, 2014 at 7:52 am
TheSQLGuru (8/7/2014)
I was definitely NOT espousing the use of OPTIMIZE FOR as a SOLUTION for this issue - just to expose it. I DESPISE that "feature", because it GUARANTEES you will get a BAD PLAN for at least some of your executions, potentially many of them!
I apologize. I misunderstood that you were suggesting to try OPTIMIZE FOR to identify the plans for different parameters instead of correcting the issue. While I agree this isn't the best option to solve bad plans caused by parameter sniffing, it may be good enough or it may be the best option. Only testing the different options would identify that.
Re-factoring the procedure to get the best plan is ideal but sometimes not possible.
August 8, 2014 at 8:34 am
My first choice would be to use RECOMPILE and just force SQL to rebuild a plan every time.
But, if a HASH join is the "good" plan, forcing a HASH join is much safer overall than forcing a LOOP join. You might try that for a range of values and verify that it works OK across all of them. This, too, may not be the "best" solution, but it should be a workable solution.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 8, 2014 at 11:03 am
ScottPletcher (8/8/2014)
My first choice would be to use RECOMPILE and just force SQL to rebuild a plan every time.But, if a HASH join is the "good" plan, forcing a HASH join is much safer overall than forcing a LOOP join. You might try that for a range of values and verify that it works OK across all of them. This, too, may not be the "best" solution, but it should be a workable solution.
I am curious why you say HASH force would be safer. I would say just the opposite...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 8, 2014 at 11:19 am
TheSQLGuru (8/8/2014)
ScottPletcher (8/8/2014)
My first choice would be to use RECOMPILE and just force SQL to rebuild a plan every time.But, if a HASH join is the "good" plan, forcing a HASH join is much safer overall than forcing a LOOP join. You might try that for a range of values and verify that it works OK across all of them. This, too, may not be the "best" solution, but it should be a workable solution.
I am curious why you say HASH force would be safer. I would say just the opposite...
My thinking is:
LOOP is extremely -- even prohibitively -- expensive on a very large number of rows.
HASH might not be ideal for a smaller number of rows, but it shouldn't be awful either.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply