December 3, 2011 at 2:18 pm
I have a very simple query, which returns a single row given its primary key. Normally this is very fast. Occasionally, this query times out. The error message returned via ASP.NET is:
The timeout period elapsed prior to completion of the operation or the server is not responding
I do not want to change the current connection timeout or command timeout as that would be simply hiding the problem under the rug.
I am more interested at this stage with simply understanding why this is happening. As such I am looking for recommendations as to diagnostic measures.
What tools (perfmon, SQL Trace, etc.) should / can be used to determine things like:
1. Is this a connection problem or a processing problem.
2. If a processing problem is it due to locks held by some other process? If so, which?
etc.
Recommendations as to diagnostic measures would be most appreciated.
Thanks,
Jamie
December 3, 2011 at 3:07 pm
The two possibilities I see are blocking and network. You could run a server side trace to see if you can catch anything regarding blocking. You may also want to monitor the DMVs to see of you fing anything there. You may also want to run a network trace as well, see if the timeouts occur during time of heavy network utilization.
You may also want to investigate using snapshot isolation on the database so that writers don't block readers.
December 3, 2011 at 3:30 pm
Thanks for the reply. I'm putting my bets on blocking...
I am reading about using SQL Trace. I see "Lock: Timeout (timeout > 0)" as a candidate to trace. Does that make sense? Any others?
And thinking ahead, if the trace does show that locks were timing out, what is available to help determine who was holding the lock?
Thanks,
Jamie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply