Hi,
Periodically, my web app logs are showing timeout expired errors when
connecting to my sql server. The timeout expired errors seem to correspond to
one error which states that the SQL server can't be found. This only happens
for about one second, twice per day (sometimes more often), and other
concurrent requests do not seem to time out. This is a new error that just
started appearing about a week ago.
I've spent the past week attempting to diagnose the problem.
What I have done thus far:
- My sql server performed an average of 40 queries per second. I reduced this
to about 18 by optimizing and removing some duplicate queries and other things
that weren't written optimally.
- Did a dbreindex for the entire db, removed old records which were no longer
used. Created some new indexes, which seemed to improve performance somewhat
(average CPU is now about 6% during peak hours). Statistics are updated nightly.
- Upgraded my network card to a gigabit card, connected through a new gigabit
switch. At one point, I assumed this must be a networking issue, but I believe
I've ruled this out. Neither server shows any logs of any network disconnects
at any time.
- Changed the schedule of my backups, as this seemed to be related, however, it
doesn't correspond to the errors at this point.
- Checked for long running queries. Corrected one, which was running an average
of 3 to 5 seconds, reduced it to a max of 500ms, average of 63ms. This is still
the longest running query on the server, but there's really nothing that can be
done to reduce the response time at this point (other than maybe upgrading the
server maybe).
- I/O seems normal. CPU never peaks above 50%, and is usually averaging 6%. DB
server has 1gig of ram, DB is 1.5gigs, but paging statistics don't seem to be
anything significant.
The errors happen during peak times, but seem more frequent during non-peak
hours, generally between 11pm and 1am. There is nothing scheduled during this
time frame.
I have maintain database connection set to yes.
All of the queries that are timing out use with (nolock), so they shouldn't
be deadlocking. Typically these queries average between 0ms and 16ms, rarely
longer. None of the timed out queries are updates, only nolock reads.
I've covered everything that I can think of... is there anything else that I
should be looking at?
I almost suspect that the connection between the servers is dropping, though
there is no reason for this, and nothing that would indicate this is the case
that I can find...
Geoff B
~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195433
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
