Below are the questions to ask and work on when we have Query time out issues
what kind of crime is this ?
Query time out is client side time out.
Is it my SQL server setting
EXEC SP_CONFIGURE ‘query timeout’
Note : Default is 60 secs in .net code
Questions to Ask
People who managing the system engineers, any middle tier.
users- super users.
Initial Questions for users
(1) Are the timeouts consistent, or random ?
(2) Are the timeouts for all users, or some users ?
(3) if some which users ?
(4) Is there a pattern of activity around when the timeout happens ?
(5) can someone reproduce this right now ? yes or no
(6) How critical is this issue ? -Red flag. how many users are getting effected.
Time out Questions
(1) Where is the timeout set ?
(2) What is the current value of the timeout ? ————seconds
(3) Has the timeout changed recently ?
(4) can the application run in a mode where detailed error information goes to the log ? YES OR NO
(5) Under which host name run this query ?
(6) Under which login is used ?
(7) which application name is used on the connection ?
Database related Questions
(1) Under which database(s) does this query use ?—
(2) Is this query part of a distributed transaction ? YES OR NO
(3) If this a stored procedure or if the materialized view , What are sample runtime parameters ?
(4) Does the query is text/stored procequery is procedure call ?
Profier to fix the timeouts
(1) SQL: Batchstarting
(2) SQL: Statstarting
(3) SQL: StmptCompleted
(4) SQL: Batchcompleted
Columns : Duration , SPID, Database ID, DatabaseName , LoginName
who else was the crime scene ?
May be due to contentions issues.
May be jobs are running ? check the acitve SpID’s and check blocking.