April 5, 2011 at 10:56 am
I have a proc which basically has five set of sql statements. One set is taking most time. Can someone please clarify below questions?
i) I see two exec plans in cache, one is good and other not so good.The estimated number of counts and est i/o cost of bad one is way too high? Why is it so? How do i fix this? Sometimes the proc is using this plan
ii) I wanted to do some testing by forcing a exec plan. I following the steps from "http://msdn.microsoft.com/en-us/library/cc917694.aspx" but got the following error "Msg 8695, Level 16, State 6, Line 26
Cannot execute query because of incorrectly formed XML plan in USE PLAN hint.
Verify that XML plan is a legal plan suitable for plan forcing. See Books Online for additional details.
". In the XML format i replaced all ' with '' . How can i make this work?
Please advice
April 5, 2011 at 11:47 am
It sounds like you're experiencing what is known as bad parameter sniffing. There are several ways to fix this before you get to plan forcing. That should be the last thing you try. Have you looked at using a hint such as OPTIMIZE FOR and providing a value? Or maby setting the procedure or statement to recompile? Any of these are preferable to plan forcing.
As far as your error goes, it sounds like you don't have the XML for the plan properly formed. That's all I can tell you since I'm not looking at your code, but the error message is very straight forward.
"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
April 5, 2011 at 1:14 pm
Grant Fritchey (4/5/2011)
It sounds like you're experiencing what is known as bad parameter sniffing. There are several ways to fix this before you get to plan forcing. That should be the last thing you try. Have you looked at using a hint such as OPTIMIZE FOR and providing a value? Or maby setting the procedure or statement to recompile? Any of these are preferable to plan forcing.As far as your error goes, it sounds like you don't have the XML for the plan properly formed. That's all I can tell you since I'm not looking at your code, but the error message is very straight forward.
I was just testing to see if using another plan was taking more time or not. I don't want to use recompile option because i do know that recompile in my case takes long time. How about i declare local variables and set values to it? If i do , i don't know how can i test from management studio?
April 5, 2011 at 2:48 pm
Yeah, local variables is a mechnism for defeating some forms of parameter sniffing. What it means is, instead of using a specific value to arrive at an execution plan, it'll use an average value against the statistics and come up with a different plan.
Testing it should be fairly straight forward, we're talking about a stored procedure, right? change the proc, recompile it, you're good to go.
"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
April 6, 2011 at 12:59 pm
Grant Fritchey (4/5/2011)
Yeah, local variables is a mechnism for defeating some forms of parameter sniffing. What it means is, instead of using a specific value to arrive at an execution plan, it'll use an average value against the statistics and come up with a different plan.Testing it should be fairly straight forward, we're talking about a stored procedure, right? change the proc, recompile it, you're good to go.
Ok my goal is have my proc use goodplan "always". Is changing to local variable preferred over the query hint which you specified?
April 6, 2011 at 1:25 pm
sqldba_icon (4/6/2011)
Grant Fritchey (4/5/2011)
Yeah, local variables is a mechnism for defeating some forms of parameter sniffing. What it means is, instead of using a specific value to arrive at an execution plan, it'll use an average value against the statistics and come up with a different plan.Testing it should be fairly straight forward, we're talking about a stored procedure, right? change the proc, recompile it, you're good to go.
Ok my goal is have my proc use goodplan "always". Is changing to local variable preferred over the query hint which you specified?
Using local variables is exactly the same as saying OPTIMIZE FOR UNKNOWN. The optimizer will use averages against the statistics. That doesn't guarantee a good plan "always" because statistics can, and do change over time. But if I were making the recommendation, I'd go with the hint, not local variables. However, I'd also suggest you spend a bunch more time ensuring that you have good, up to date, statistics, properly configured indexes, etc.
"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
April 6, 2011 at 3:15 pm
OK, i just realized my procs are also using functions. Now, i know the the procedure is using different plan when i run from management studio Vs being called from the app. However i do not know which one is better over the other one because "sys.dm_exec_query_plan" doesn't give info about functions. I am so lost here, how do other's trouble shoot this type? Very frustrating because none of the dmv's really support table valued functions. How can i say which plan is better over the other?
April 6, 2011 at 6:25 pm
Yes, you can capture the plans for user defined functions. You have to use extended events and with them you can capture the plan that is used for the UDF. But, if you're using multi-statement UDFs you really should consider rearchitecting the solution. Those things are notorious performance problems.
"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
April 7, 2011 at 6:55 am
sqldba_icon (4/6/2011)
Grant Fritchey (4/5/2011)
Yeah, local variables is a mechnism for defeating some forms of parameter sniffing. What it means is, instead of using a specific value to arrive at an execution plan, it'll use an average value against the statistics and come up with a different plan.Testing it should be fairly straight forward, we're talking about a stored procedure, right? change the proc, recompile it, you're good to go.
Ok my goal is have my proc use goodplan "always". Is changing to local variable preferred over the query hint which you specified?
If you KNOW the best plan and want it to always use it and it doesn't change, you can use a plan guide.
If you always want the best plan with a wide range of inputs and data distributions then local variables really are not the solution and neither is OPTIMIZE FOR UNKNOWN. The 2 viable solutions are OPTION RECOMPILE and dynamic SQL. If you use the latter, be sure to guard against SQL Injection!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 7, 2011 at 7:28 am
TheSQLGuru (4/7/2011)
sqldba_icon (4/6/2011)
Grant Fritchey (4/5/2011)
Yeah, local variables is a mechnism for defeating some forms of parameter sniffing. What it means is, instead of using a specific value to arrive at an execution plan, it'll use an average value against the statistics and come up with a different plan.Testing it should be fairly straight forward, we're talking about a stored procedure, right? change the proc, recompile it, you're good to go.
Ok my goal is have my proc use goodplan "always". Is changing to local variable preferred over the query hint which you specified?
If you KNOW the best plan and want it to always use it and it doesn't change, you can use a plan guide.
If you always want the best plan with a wide range of inputs and data distributions then local variables really are not the solution and neither is OPTIMIZE FOR UNKNOWN. The 2 viable solutions are OPTION RECOMPILE and dynamic SQL. If you use the latter, be sure to guard against SQL Injection!!
I'd just be careful about using the word "always" with plan guides. They can be overriden and ignored, so you're not guaranteed 100%.
"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
April 7, 2011 at 9:17 am
Thanks everyone for the inputs. Still some of my questions are unanswered.
i) Why do i see two different plans one from mgmt studio and one from the application? The sql code i am running from management studio is EXACTLY the same being called from app?
ii) Using "Option Recompile" for proc is NOT AN OPTION because it takes more than 20 secs to compile first time
iii)I have tested my procs with different values from management studio and it works perfectly fine. Now the app is using SOME other plan which i am not able to say whether it is good or bad. I will try with plan guides.
Thanks again.
April 7, 2011 at 9:30 am
sqldba_icon (4/7/2011)
Thanks everyone for the inputs. Still some of my questions are unanswered.ii) Using "Option Recompile" for proc is NOT AN OPTION because it takes more than 20 secs to compile first time
Thanks again.
That would be a huge concern for me. Unless you send a million statements to compile it just shouldn't take that long to compile. That means that the server is likely going to the extreme allowed limits to find the execution plans. This is very likely to cause sub-optimal plans to be generated.
I'd run the query with actual execution plan to see if you have timeouts as the reason for early termination (in the gui plan, right click the final select, properties, check out the stats from there).
Those queries will most likely be not optimized / planned correctly.
My absolute worst performing compiling code was well over 1000+ lines of code, dynamic sql, using 30 to 80 tables out of a pool of ever swapping 200 tables and having 10+ queries to compile in the batch.
That code never took more than 200 ms to compile on a low quality server. Now your query takes 100 times longer than that to compile? The only reason I see that happenning is that the server is going way too far in it's plan getting sequence...
April 7, 2011 at 10:37 am
If you have two completely different plans in cache, it's probably the connection settings, specifically the ANSI settings, that are different between Management Studio and the application. Focus there.
"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
April 7, 2011 at 12:49 pm
Grant Fritchey (4/7/2011)
If you have two completely different plans in cache, it's probably the connection settings, specifically the ANSI settings, that are different between Management Studio and the application. Focus there.
Well, as far as i know SET OPTION settings inside the proc is the one which actually taken in effect no matter what the SET options are in the .NET client? What do you say?
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply