April 11, 2009 at 10:19 am
Application team is running a batch job every 2 minutes. But everyday
they are facing a timeout error between 4:00 am and 4:10 am
That batch job is run through a stored procedure.
I am thinking of running a profiler at that time to detect any possible reasons for this. I checked the database jobs that are running on this server, but didn't find any jobs that are overlapping at the same time.
Could you please suggest any good means by which I can rectify this timeout errors.
M&M
April 12, 2009 at 8:26 am
Usually time out errors occur due to blocking and deadlocking issues.I would advise you to run a profiler trace with locking events and also turn on trace flag 1222 to check whether there are any deadlocks occurring on your server.
April 12, 2009 at 8:44 am
Deadlocks won't cause timeouts. Deadlocks cause error 1205.
Timeouts at a certain time indicate that something else is running at that time that's interfering with the query that's timing out. Any jobs or scheduled tasks running between 4am and 4:10am?
Profiler's an idea, though rather use the server-side trace than the profiler gui. See the beginning of this article - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
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
April 12, 2009 at 9:42 am
As Krishna mentioned, it is probably a blocking issue. I suppose it could (technically) also be a deadlock - deadlock detection is not instantaneous after all. I didn't see mention of the timeout period by the way? The possible causes are many and varied - out of date statistics, maintenance operations on indexes holding Sch-M locks, the amount of load on the server (if a parallel plan is required for example).
Profiler is probably your best bet, though blocking, deadlocks, and rollbacks are a fact of database life and a good procedure/job/application should handle this gracefully.
Depending on your environment and what you find in the Profiler traces, consider read committed snapshot and online index rebuilds (enterprise only).
If you get some more information on the specific causes, post back and I'll try to be more specific.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply