Query time out


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’

Time out

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.

Reference : http://www.brentozar.com/archive/2011/07/mystery-of-query-timeouts-video/

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s