February 4, 2015 at 9:21 am
A web app calls a proc from a sql server which resides on VM box. A proc takes a date range. When I change the date range from the web app
to pull larger data sets, the web hangs up...But if I run the same date parameter through SSMS, it works without issue. To make it work
through web not to cause hang up, I had to use option Recompile option on the proc. I just don't understand this. Why strange behavior through web? Can anybody please help on what to look for?
February 4, 2015 at 9:44 am
If using option Recompile fixes it then you may be dealing with parameter sniffing. One way to know for sure is to capture the query plan when running the query in SSMS and via the web app. Post the query plans here and let us have a look.
-- Itzik Ben-Gan 2001
February 4, 2015 at 9:52 am
Thanks. Can you post the query to capture the query plan? Appreciate your help.
February 4, 2015 at 10:10 am
This will give you the info that you need:
How to Post Performance Problems[/url]
-- Itzik Ben-Gan 2001
February 4, 2015 at 10:18 am
It's also possible that you're seeing differences in the plan because of ANSI connection settings being different between SSMS and the web app. Because, assuming you ran the query with identical parameter values in SSMS and the web app, you should see the exact same execution plan regardless of if you're getting bad parameter sniffing or not.
"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 4, 2015 at 10:23 am
Thanks Grant. Why would option recompile would fix it through the web app?
February 4, 2015 at 10:34 am
Or in other words, how do I get the ANSI connection settings from the web app?
February 4, 2015 at 10:37 am
Without more evidence, I don't know.
It could just be bad parameter sniffing. It could be something else. Gather to data to be sure.
"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 4, 2015 at 10:43 am
SQL_Surfer (2/4/2015)
Or in other words, how do I get the ANSI connection settings from the web app?
Oh, that's easy. Look at the first operator from the execution plan. It's stored in the properties there. One point though, it's possible if you do have differences in the ANSI settings that you actually have two different execution plans for the same procedure. Check that in the cache.
"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 4, 2015 at 3:34 pm
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.
February 4, 2015 at 3:37 pm
Running sp_whoisactive, it shows reads spiking u, CPU to 368, 163 and used_memory to 131.
February 4, 2015 at 3:44 pm
Actually, finally came up. But i see only one plan in the cache. Should I be seeing two for parameter sniffing?
February 4, 2015 at 5:27 pm
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.
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 5:51 pm
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.
February 4, 2015 at 6:44 pm
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.
Major nuisance for me too because it was one of only two outstanding (known) bugs in a system we were trying to complete, and it was out there for a long time.
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
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply