April 22, 2011 at 4:18 am
Hi,
I am kind of new to SQLServer and my question involves query timeout in stored procedures.I have a stored procedure that uses "WAITFOR DELAY " and sleeps for say,10 secs , when the query timeout is set to 9secs..i get a query timeout exception as expected however when i change the stored procedure to stay busy for 10secs insted of a sleep.
as in
CREATE PROCEDURE Sleepy @sleep INT AS
BEGIN
DECLARE @now DATETIME;
DECLARE @end DATETIME;
SET @now = SYSDATETIME();
SET @end = DATEADD(S, @sleep, @now);
WHILE (@now < @end)
SET @now = SYSDATETIME();
END;
where the loop goes on till the current_time+sleep parameter in secs then i do not get a query timeout exception insted the stored procedure loops for the time and exits.
Could someone help me understand why the query timeout is not received when the stored procedure with the loop is executed? Also how can i modify the above stored procedure to receive the query timeout as expected instead of using "WAITFOR DELAY ?" as it cannot be used with other databases
April 22, 2011 at 4:24 am
If you are using SSMS query analyzer then this will not come you can manage this also in
SSMS->Tools->Option->Query Execution
Second thing is you can change query time out at the sql server level
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 22, 2011 at 5:11 am
Hi Syed,
Thanks for the reply.Actually i am running the procedure using java-CallableStatement on SQLServer and the timeout is set in the java code.
April 22, 2011 at 9:07 am
anu.suresh.hema (4/22/2011)
Hi,I am kind of new to SQLServer and my question involves query timeout in stored procedures.I have a stored procedure that uses "WAITFOR DELAY " and sleeps for say,10 secs , when the query timeout is set to 9secs..i get a query timeout exception as expected however when i change the stored procedure to stay busy for 10secs insted of a sleep.
as in
CREATE PROCEDURE Sleepy @sleep INT AS
BEGIN
DECLARE @now DATETIME;
DECLARE @end DATETIME;
SET @now = SYSDATETIME();
SET @end = DATEADD(S, @sleep, @now);
WHILE (@now < @end)
SET @now = SYSDATETIME();
END;
where the loop goes on till the current_time+sleep parameter in secs then i do not get a query timeout exception insted the stored procedure loops for the time and exits.
Could someone help me understand why the query timeout is not received when the stored procedure with the loop is executed? Also how can i modify the above stored procedure to receive the query timeout as expected instead of using "WAITFOR DELAY ?" as it cannot be used with other databases
I understand the concern, however I would recommend against using something like this...
WHILE (@now < @end)
SET @now = SYSDATETIME();
END;
...as a replacement for WAITFOR DELAY because this type of loop can tend to rev up CPU usage. What other DBMS are you worried about that you do not want to use WAITFOR DELAY? It is rare these days, if you;re using server-side objects like stored procedures, to be able to write completely portable code. Opinions vary, but I do not think it is worth the effort. If you are ever going to migrate to a new platform you'll almost certainly have things to "port" with respect to indexes, query behavior and data types anyway so you might as well use the best language features available and not plan for a migration while building a supportable system for the present time.
I do not know of a SQL Server timeout option on the server side as was recommended in a previous post. There is a query governor which can limit queries above a certain cost, but that's different from a timeout. SQL Server, unless the server is short on resources, should never time out a query. That is left to the client.
All of that said, I do not know why the Java code does not time out on the call to SQL Server when using the loop as opposed to the WAITFOR command...I suspect that is more of a Java issue than a SQL Server issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2011 at 8:02 am
Syed Jahanzaib Bin hassan (4/22/2011)
If you are using SSMS query analyzer then this will not come you can manage this also inSSMS->Tools->Option->Query Execution
That affects only queries run from Management Studio, not from client applications.
Second thing is you can change query time out at the sql server level
There is no query timeout setting at the SQL Server level. Query timeout is purely and solely a client application concept.
There's a remote query timeout in SQL, but that just affects remote queries (eg openrowset, openquery) not queries that SQL is running locally.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply