June 18, 2012 at 6:08 am
I'm attaching two execution plans. I is bad plan and second is bad plan. Both are coming from same queries. The only difference is one is getting run from a procedure and second directly from query.
I go the point that bad execution plan was created for this query for the procedure and it is getting used again and again. But my quesiton are:
how to ensure that correct execution plan is created and used every time it executes ?
how to check the difference between these good and bad plans ?
What made the sql create bad execution plan for proc ? the table stats and index fragmentation are same for both procs and query.
June 18, 2012 at 6:33 am
Please read these blog posts by Gail Shaw: -
Parameter Sniffing Part 1[/url]
Parameter Sniffing Part 2[/url]
Parameter Sniffing Part 3[/url]
In your sproc, try adding the hint "OPTION RECOMPILE" to your query. Read the blog posts for an explanation.
June 18, 2012 at 9:07 am
Just to help you troubleshoot a bit in the future, take a look at the plan. Right click the SELECT operator and choose properties. For the plan run from a procedure you can see that it has different runtime and compile time parameter values. If those compile time values lead to different execution plans, you're probably looking at bad parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 19, 2012 at 12:12 am
Thanks Cadavre & Grant for pointing me to the issue. I will check more about parameter sniffing as per the links provided by you. 🙂
June 19, 2012 at 12:55 am
how to ensure that correct execution plan is created and used every time it executes ?
Add OPTION (HASH JOIN) at the end of the query.
how to check the difference between these good and bad plans ?
One difference between these good and bad plans is hash join and nested loop join.
June 19, 2012 at 4:51 am
Suresh B. (6/19/2012)
how to ensure that correct execution plan is created and used every time it executes ?
Add OPTION (HASH JOIN) at the end of the query.
how to check the difference between these good and bad plans ?
One difference between these good and bad plans is hash join and nested loop join.
I would not recommend using a JOIN hint on this query. There are a number of better ways to resolve bad parameter sniffing rather than trying to force the optimizer into a particular plan. Plus, you have no guarantee that the rest of the plan will be the same, or that the query will perform as well, after you force a hash join on the optimizer. Query hints, especially join hints & index hints, are an absolute last resort after all other solutions have been tried.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply