July 21, 2010 at 1:50 pm
Hey Folks,
I'm trying to troubleshoot a long running query that has caused an app to time-out. The problem is that the query is running smoothly now. When I profile the query I see it consistently takes ~5000 reads regardless of whether or not the query is executed via SSMS or via the app. The app uses an ADO.NET connection to SQL Server 2005.
A couple days ago the same query was reading ~1,470,000 pages when executed from the app, and ~5000 from SSMS. I was not present for the original troubleshooting when the app was timing out but I'm told that this behavior started after setting the Blocked Process Threshold (Server Properties) at 5. Setting the threshold back to 0 apparently brought the reads back down to ~5,000.
I took a stab this morning with the developer and no matter what we did ( app vs SSMS and Threshold of 0 vs 5 ), the reads came out at ~5,000. As I said I was not present for the original troubleshooting, but I do have a trace file collected by my colleague.
To the best of my knowledge the code behind the app has not changed, nor has the database (i.e. no new indexes).
I've never encountered such a situation, so I'm seeking guidance on what to do next? There isn't much in the trace file that stands out to me. Does anyone have any ideas on how I can proceed? I'm at a loss on this and what better place to turn than here. Thank you in advance!
July 21, 2010 at 2:23 pm
In many cases, the actual cached plan will be different for the same query depending on the source of the execution because of settings such as ARITHABORT. So, if the app isn't consistent with SSMS in this regard, you'll actually have two separate plans.
From my own experience (and there are many people on this site who are much better at performance tuning issues than I am), I would want to look at the execution plans to see the difference. Also, this poorly performing plan may be fine when certain parameters are passed and have problems with others. When it is first cached, it's on the basis of the parameters at that time, so there is the potential for poorly performing plans under certain conditions.
Posting the actual code may lead to some insight.
July 22, 2010 at 5:48 am
I'm in agreement here. You're possibly looking at a parameter sniffing issue, where the differering parameters are getting you differing execution plans. Based on what you describe, that's pretty likely. It might be as simple a fix as updating the statistics, possibly with a full scan.
"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
July 26, 2010 at 12:48 pm
Thank you both for the replies. I will try to flush out the various parameters being used on these queries. My apologies for the delay on thanking you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply