September 21, 2011 at 4:16 pm
Got a complain from user that the "drop box" on website takes about 10 seconds, but it only takes 1 second if i run the same query from Mangement Studio, and i can dupicated the same problem back and forth a couple of times -- return right away from Mangement Studio and pretty slow on website !
I manually update statistics for those tables used in the query, and then both returned right away. So, the question is : before udpate statistics, what could be the reason to make the query slow in app side but not in Mangement Studio ? ( we have job update statistics on daily basis, so, it's not like totally out of state ). Any thoughts ? Thanks.
ddfg
September 21, 2011 at 4:41 pm
Do you have a date column in that query where the "default" data = getdate()?
Could also be a case of bad parameter sniffing but I can't tell without seeing both actual plans side by side.
September 21, 2011 at 4:46 pm
Different SET options resulting in different execution plans, one good one bad. Without seeing the plans (which are gone since the stats update) near-impossible to say anything else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2011 at 3:14 am
I suspect you're not running the same exact statements. Maybe you're running the statement providing parameters in a slightly different way than the app does. Could definitely be parameter sniffing.
When you update statistics, you invalidate the plans, that's why everything goes fast from then on: it's not necessarily that statistics are outdated.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply