February 4, 2015 at 8:43 pm
dwain.c (2/4/2015)
patrickmcginnis59 10839 (2/4/2015)
dwain.c (2/4/2015)
Sounds like a problem I had recently and the same solution too.SQL Query invoked by Crystal Reports v9.2 Performance Issue
I'm a great believer in going with what works, without trying to overanalyze it. Unless of course the cure is worse than the disease, which in my case it wasn't.
I wonder if you can drop the procedure, recreate it, and then run it from the web/Crystal reports the first time with a parameter that doesn't produce a bad plan so as to escape the parameter sniffing stuff? Maybe also delete that particular bad acting plan? Just curious.
You would think that would work, wouldn't you?
All I know is that I tried the SP from SSMS with a specific date as the parameter. It ran quickly. I assumed the first run should create a query plan based on that date parameter, bad or not. Then I tried it from the web with exactly the same parameter and it took forever to run.
And that was for sure the first run from the web? If so, that is certainly a pain. I can see why it wouldn't use the plan from the ssms, just because of different 'set' options, but for the first run from the web with the same parameter, I would hope sql server would subsequently determine the same plan especially if the base tables and stats didn't change in the meantime.
February 4, 2015 at 8:58 pm
patrickmcginnis59 10839 (2/4/2015)
dwain.c (2/4/2015)
patrickmcginnis59 10839 (2/4/2015)
dwain.c (2/4/2015)
Sounds like a problem I had recently and the same solution too.SQL Query invoked by Crystal Reports v9.2 Performance Issue
I'm a great believer in going with what works, without trying to overanalyze it. Unless of course the cure is worse than the disease, which in my case it wasn't.
I wonder if you can drop the procedure, recreate it, and then run it from the web/Crystal reports the first time with a parameter that doesn't produce a bad plan so as to escape the parameter sniffing stuff? Maybe also delete that particular bad acting plan? Just curious.
You would think that would work, wouldn't you?
All I know is that I tried the SP from SSMS with a specific date as the parameter. It ran quickly. I assumed the first run should create a query plan based on that date parameter, bad or not. Then I tried it from the web with exactly the same parameter and it took forever to run.
And that was for sure the first run from the web? If so, that is certainly a pain. I can see why it wouldn't use the plan from the ssms, just because of different 'set' options, but for the first run from the web with the same parameter, I would hope sql server would subsequently determine the same plan especially if the base tables and stats didn't change in the meantime.
The whole thing was pretty weird. That's the reason I didn't consider parameter sniffing to be the problem in the first place and for a long time. In the end, it was kind of "hit-or-miss" troubleshooting that nailed it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2015 at 10:27 pm
This is how the SET option is looking
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through SSMS
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through Web
February 5, 2015 at 3:50 am
SQL_Surfer (2/4/2015)
As soon as I take the recompile option out, web hangs...Process goes to runnable step and never finishes for me to get to the execution plan.
Get the estimated plan. You'll still have just about everything you need. You can pull it right out of the cache while the query is running using the DMVs.
But, if you run it with EXACTLY the same parameters against EXACTLY the same database from SSMS it runs fine without the RECOMPILE hint?
"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
February 5, 2015 at 3:51 am
SQL_Surfer (2/4/2015)
Actually, finally came up. But i see only one plan in the cache. Should I be seeing two for parameter sniffing?
No, not for a bad parameter sniffing problem.
"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
February 5, 2015 at 3:52 am
patrickmcginnis59 10839 (2/4/2015)
dwain.c (2/4/2015)
patrickmcginnis59 10839 (2/4/2015)
dwain.c (2/4/2015)
Sounds like a problem I had recently and the same solution too.SQL Query invoked by Crystal Reports v9.2 Performance Issue
I'm a great believer in going with what works, without trying to overanalyze it. Unless of course the cure is worse than the disease, which in my case it wasn't.
I wonder if you can drop the procedure, recreate it, and then run it from the web/Crystal reports the first time with a parameter that doesn't produce a bad plan so as to escape the parameter sniffing stuff? Maybe also delete that particular bad acting plan? Just curious.
You would think that would work, wouldn't you?
All I know is that I tried the SP from SSMS with a specific date as the parameter. It ran quickly. I assumed the first run should create a query plan based on that date parameter, bad or not. Then I tried it from the web with exactly the same parameter and it took forever to run.
And that was for sure the first run from the web? If so, that is certainly a pain. I can see why it wouldn't use the plan from the ssms, just because of different 'set' options, but for the first run from the web with the same parameter, I would hope sql server would subsequently determine the same plan especially if the base tables and stats didn't change in the meantime.
Different ANSI settings can result in a completely different plan. One where parameter sniffing is more of an issue than the one with the other set of ANSI settings. It's possible.
"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
February 5, 2015 at 3:23 pm
I've attached the plan from both SSMS and WEB. Since this is a new app, I've not built any indexes yet you'll see table scan.
February 6, 2015 at 10:43 am
Ah, those are very different plans, compiled for very different inputs.
The one through SSMS was compiled for a 5 year date range and a null CustomerNumber, while the one through the web was compiled for a 1 month range and particular customer number.
The web plan expects a small amount of data, so it's using nested loops for all the joins, while the SSMS plan is using hash joins. If you're running both for a larger date range, I imagine those nested loops are killing you because of the larger dataset.
That's my hunch based on a preliminary glance at the plans; I wouldn't wager anyone's life on it though 🙂
February 6, 2015 at 10:48 am
Thanks. But in the table itself there is data for 1 year only. Wanted to figure out why would it show such a strange behavior?
February 6, 2015 at 11:13 am
Sure, the main point is that it expected a lot more data when it cached the plan for the SSMS query.
Just based on this it seems like a normal parameter sniffing problem, where the plan used by web is inefficient for the parameters it's normally passed.
Having said that, it might not even run all that well for the parameters for which it was compiled.
What parameters are you actually passing to the procedure when you run it from the Web server?
February 7, 2015 at 3:18 am
I agree. This looks like standard parameter sniffing gone wrong. It's always about the data and the data distribution. The parameter values passed from one create a plan for retrieving smaller amounts of data and the parameters for the other are for retrieving larger amounts. That's all it is. The recompile is working because you need a different plan for different data sets.
"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
February 10, 2015 at 12:55 am
Coincidentally I just now ran across a quite interesting article on this subject by Erland Sommerskog in his blog and since I didn't see anyone post that link here, I will:
Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
Unfortunately, it is too late for me to do the analysis now as I've un-cached my problematic plan. But next time, you can bet I'll be ready!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 10, 2015 at 8:17 pm
SQL_Surfer (2/4/2015)
This is how the SET option is lookingANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through SSMS
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through Web
You know, I had a problem a few weeks ago with the ARITHABORT setting making a web report run crazy slow that executed fine in SSMS. Can you change that?
February 10, 2015 at 8:29 pm
sqldriver (2/10/2015)
SQL_Surfer (2/4/2015)
This is how the SET option is lookingANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through SSMS
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through Web
You know, I had a problem a few weeks ago with the ARITHABORT setting making a web report run crazy slow that executed fine in SSMS. Can you change that?
I was checking into this yesterday and it depends on how you're making your connection from the client to SQL. Try Google on "sql connection string c# arithabort" and you'll find several answers that might be workable for your case.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 10, 2015 at 8:38 pm
dwain.c (2/10/2015)
sqldriver (2/10/2015)
SQL_Surfer (2/4/2015)
This is how the SET option is lookingANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through SSMS
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through Web
You know, I had a problem a few weeks ago with the ARITHABORT setting making a web report run crazy slow that executed fine in SSMS. Can you change that?
I was checking into this yesterday and it depends on how you're making your connection from the client to SQL. Try Google on "sql connection string c# arithabort" and you'll find several answers that might be workable for your case.
Will do, but I think since it was an in-house application we just changed the connection string. They were particularly mum on the subject since they had me looking at this ridiculous thing for 3 hours. Heh.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply