November 26, 2010 at 3:55 am
I am little confused over the term timeout which has more than one context.
From an application context, one of our application team has setup a timeout of 8 minutes which, as per what I know, is the maximum time a query can get to be executed. There were few long running 'bad' queries that took around an hour to execute.
There could be case where the query has executed in seconds and is taking too long to accept resultset from the sql server. Will the query timeout in such case if it crosses the timeout limit that has been defined?
November 26, 2010 at 5:38 am
Hi,
I am not completely sure, but I think that the timeout in the client should mean the wait time for the database server to respond to the query. If the server has responded within limit, but the result set takes long time to load should not affect timeout limit. However, this is a client setting, so different clients might behave differently. What tool do you use for the connection? Is there any documentation?
Cheers
November 26, 2010 at 5:49 am
Thanks for your reply.
There is an application whose command timeout is set at 8 minutes. App team thinks any query taking more than 8 minutes should be timed out. But yesterday a query virtually pulled the system down and it took more than an hour to execute. Which led me to think that this 8 minute is the time for sql server to respond. if the server is returning huge recordset, the query wont get timed out in 8 minutes.
But i am trying to find some authentic information related to this :ermm:
November 26, 2010 at 7:27 am
There might be setting in the driver that aborts the way your app team request, but that shouldn't be the connection timeout. It seems logical to me that such abortion should be handled in the code.
What driver are you using (JDBC, ADO, ...)?
November 29, 2010 at 9:51 am
The timeout setting at the application will not cancel the query at the database. It will just stop caring about the result set, the sql side of the connection will keep processing the request.
It will depend on the app as to whether it considers the beginning of the data transfer or the end of the data transfer to need to fall within the timeout window, before considering it a failed query.
I believe that answers your question, sorry if I misread it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2010 at 10:28 am
Thanks Craig. 🙂
As of now i have written a script that emails a set of users whenever a query crosses the threshold (which is 8 minutes for them). It is at instance level so app owners will be intimated whenever such case arise and they can decide what to do with that query.
November 29, 2010 at 12:42 pm
A very simple answer to this is if you are connected online, you could have lost your connection briefly and when it sent a signal, it didn't receive anything back and thus timed out
November 29, 2010 at 12:46 pm
kcinman11358 (11/29/2010)
A very simple answer to this is if you are connected online, you could have lost your connection briefly and when it sent a signal, it didn't receive anything back and thus timed out
This helps this question:
There could be case where the query has executed in seconds and is taking too long to accept resultset from the sql server. Will the query timeout in such case if it crosses the timeout limit that has been defined?
How?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 30, 2010 at 12:56 am
Ok. here is the query. May be you guys can help me better this. Or tell me what all things should i exclude/include.
-- Script written to find and report long running queries
-- @TimeThreshold is the threshold defined for queries in milliseconds
-- You can exclude/include databases for this script. those clauses are commented below
-- Following statements are excluded - backup, update stats, index rebuilds/reorg, dbcc
-- Author - Pradeep Singh ()
-- Date :- 29 Nov 2010
Declare @TimeThreshold as bigint
Declare @NumberOfQueries as int
Declare @FoundOffendingQueries as smallint
Declare @RunningTime as bigint
Declare @SQLtext as varchar(max)
Declare @Session_ID as int
Declare @DB_name as varchar(100)
Declare @User_Name as varchar(100)
Declare @Host_Name as varchar(100)
Declare @Program_Name as varchar(100)
Declare @Login_Name as varchar(100)
Declare @Net_Address as varchar(100)
Declare @ErrorMsg as varchar(max)
set @TimeThreshold=1*5*1000 -- Change this to 8 minutes > 8*60*1000
set @ErrorMsg=char(10)+char(10)+'ALERT - Long Running Query'+char(10)
set @FoundOffendingQueries=0
/*select @NumberOfQueries=count(*) from sys.dm_exec_requests er
inner join sys.dm_exec_sessions es on es.session_id=er.session_id where er.total_elapsed_time>=@TimeThreshold and er.session_id<>@@SPID
and es.is_user_process=1
If @NumberOfQueries>=1*/
--Prepare the error message for all offending SQLs
BEGIN
DECLARE Queries_Cursor CURSOR FOR
select er.total_elapsed_time,est.text, er.Session_id,
Db_name(database_id),user_name(user_id), es.host_name, es.program_name,
es.original_login_name, ec.client_net_address
from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(plan_handle) est
inner join sys.dm_exec_sessions es on es.session_id=er.session_id
inner join sys.dm_exec_connections ec on er.connection_id=ec.connection_id
where er.total_elapsed_time>=@TimeThreshold and er.session_id<>@@SPID
and es.Is_user_process=1
--and db_name(database_id) in () -- list of databases to include
--and db_name(database_id) not in () -- list of databases to exclude
and lower(est.text) not like '%backup%database%'
and lower(est.text) not like '%backup%log%'
and lower(est.text) not like '%alter%index%'
and lower(est.text) not like '%sp_updatestats%'
and lower(est.text) not like '%update%statistics%'
and lower(est.text) not like '%dbcc %'
and lower(est.text) not like '%sp_readrequest%'
OPEN Queries_Cursor;
--set @ErrorMsg=@ErrorMsg+ cast(@@rowcount as varchar)
FETCH NEXT FROM Queries_Cursor INTO @RunningTime, @SQLtext, @Session_id, @Db_name, @User_Name, @Host_Name, @Program_Name, @Login_Name, @Net_Address
WHILE @@FETCH_STATUS = 0
BEGIN
set @FoundOffendingQueries=1
set @ErrorMsg=@ErrorMsg+'||-------------------------------------------||'+char(10)
set @ErrorMsg=@ErrorMsg+'||-------------------------------------------||'+char(10)
set @ErrorMsg=@ErrorMsg+': Running since last ' + cast(@RunningTime/1000 as varchar) + ' Seconds.'+char(10)
set @ErrorMsg=@ErrorMsg+': Running since last ' + cast(@RunningTime/1000/60 as varchar) + ' Minutes.'+char(10)
set @ErrorMsg=@ErrorMsg+': Session Id - ' + cast(@Session_ID as varchar)+char(10)
set @ErrorMsg=@ErrorMsg+': User Name - ' + @User_Name+char(10)
set @ErrorMsg=@ErrorMsg+': Database - ' + @Db_name+char(10)
set @ErrorMsg=@ErrorMsg+': Host Name - ' + @Host_name+char(10)
set @ErrorMsg=@ErrorMsg+': Program Name - ' + @Program_name+char(10)
set @ErrorMsg=@ErrorMsg+': Login Name - ' + @Login_name+char(10)
set @ErrorMsg=@ErrorMsg+': Client Net Address - ' + @Net_Address +char(10)
set @ErrorMsg=@ErrorMsg+': Query :- '+char(10) + @SQLtext+char(10)
set @ErrorMsg=@ErrorMsg+'||-------------------------------------------||'+char(10)
set @ErrorMsg=@ErrorMsg+'||-------------------------------------------||'+char(10)
FETCH NEXT FROM Queries_Cursor INTO @RunningTime, @SQLtext, @Session_id, @Db_name, @User_Name, @Host_Name, @Program_Name, @Login_Name, @Net_Address
END;
CLOSE Queries_Cursor;
DEALLOCATE Queries_Cursor;
if @FoundOffendingQueries=1
Begin
--print len(@errormsg)
--print @errormsg
RAISERROR ( @ErrorMsg,
15,
1
) with log
-- Code to send email here
declare @body1 varchar(100)
declare @subject1 varchar(8000)
set @body1 = @ErrorMsg
set @subject1='Server : '+@@servername+ ' - Long Running Queries '
EXEC msdb.dbo.sp_send_dbmail @recipients='x@b.com',
@subject = @subject1,
@body = @ErrorMsg,
@body_format = 'HTML' ;
End
End
edit - removed my official mail id.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply