December 4, 2011 at 7:38 am
Hi,
I've been experiencing periodic SQL performance issue that leads to eventual timeout for our web visitor.
I set up a Profiler/Trace to monitor and find that it appears to very time patterned. The symptoms usually show up between 11am and 12pm (regardless of visitor volume)
I compare the trace data for a specific stored procedure to get the summarized results below:
Three "phases":
Typical - what we see most of the time - when things are "normal"
Failure - when delays and reads lead to timeouts. (I'm calling it failure but no actual SQL error)
TailOff - when delay eventually reduces but not normal and reads are still very high (usually after period of failure event)
Typical Disk Read Value is between 21,000 and 31,000 (typically low 20's)
Typical Duration - 200 and 700 milliseconds (typically in 200s)
Failure Disk Read Values - 1.6 million to 2.2 million
Failure Duration - 30,000 (times out web app)
Tail Off Disk Read Values - 1.6million
Tail Off Duration - 4,000 to 6,000
Friday I reset SQL Instance and after 1.5 hours high tail off and it went back to normal
Saturday -tail off lasted 1 hour and went back to normal itself.
I find no local jobs running on this server to account for interference and trace is only showing me when the performance problem occurs.
Normally I would assume the SP needs tuning (and maybe it does) but if that were the case, why wouldn't it perform badly all of the time? Same for tables, row counts, indexes - they're the same all of the time.
It seems like an "external" event is interfering and I'm wondering if anyone has any suggestions for tracking th source down.
Thanks
December 8, 2011 at 2:04 pm
you said you ran the profiler
since it runs everyday it is probably a sql job or external job hitting the database, run the t_sql tuning option or t sql in the profiler to see what else is running. It sounds like probably waiting on something else
December 8, 2011 at 2:11 pm
DoubleEx (12/4/2011)
Hi,I find no local jobs running on this server to account for interference and trace is only showing me when the performance problem occurs.
Normally I would assume the SP needs tuning (and maybe it does) but if that were the case, why wouldn't it perform badly all of the time? Same for tables, row counts, indexes - they're the same all of the time.
It seems like an "external" event is interfering and I'm wondering if anyone has any suggestions for tracking th source down.
Thanks
I'd agree it's an external event, but it might not be completely external. It may be this query is a symptom of another query or even causing a problem, particularly in concurrency blocking.
Typical Disk Read Value is between 21,000 and 31,000 (typically low 20's)
Typical Duration - 200 and 700 milliseconds (typically in 200s)
Failure Disk Read Values - 1.6 million to 2.2 million
Failure Duration - 30,000 (times out web app)
Tail Off Disk Read Values - 1.6million
Tail Off Duration - 4,000 to 6,000
Friday I reset SQL Instance and after 1.5 hours high tail off and it went back to normal
Saturday -tail off lasted 1 hour and went back to normal itself.
Hm, this is interesting. You suddenly switch from an optimized plan in reads to a horeendous plan in reads. It sounds like you're swapping from a few seeks into a couple of scans. As you can imagine that's going to fail horribly.
Any chance you're capturing the xml showplans in the trace?
My guess is you end up getting a bad plan in cache during a recompile. Most likely due to bad parameter sniffing, at least that'd be the first culprit I'd start hunting.
Check out this blog post series by Gail Shaw:
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply