We have .NET Application connecting to SQL Server database. I have noticed the timeout from the Application while calling the stored Procedure.
We didn’t configure any specific timeout limit , so we were using default 30 Sec Timeout.
Its pretty big red flag, that the stored procedure can’t complete within 30 Seconds.
Our general development practice is to have the execution time below 200 milliseconds.
I also noticed the odd Application behavior. While calling the stored procedure, it would complete the first half and would timeout before completing the second half even though both statements are part of the explicit transaction.
As we all been taught over the years, when you issue Explicit BEGIN TRAN / COMMIT TRAN , Its all or nothing proposition. But I was noticing the only half of my statements being committed and other half never gets executed and never being rolled back.
Further looking into this issue, by default XACT_ABORT is OFF. As a side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout.
How many application developers know about XACT_ABORT Configuration and the effects on time out.
I explicitly added ” SET XACT_ABORT ON ” in my stored procedure and then I was able to do all or nothing.
Stored procedure taking more than 30 seconds is just not acceptable and we also fixed the stored procedure to execute within 200 milliseconds.
Microsoft Definition:
Syntax
SET XACT_ABORT { ON | OFF }
Remarks
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
Further Reading related to this topic:
http://www.sommarskog.se/error-handling-II.html
http://msdn.microsoft.com/en-us/library/aa259192(v=SQL.80).aspx