This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.
This is somewhat confusing.
For starters, CODE does not get saved to the DB, DATA does.
Secondly, SQL Server does not work that way.
If a set of code is updating, as an example, 100 rows and it takes longer than the 20 seconds to do the update, none of the rows would be updated. They would all be rolled back.
That being said, is this code doing a loop and updating one row at a time? Is it in a cursor? If that's the case, then there is likely a more efficient way to write this code.
Can you post the actual code?
What steps have you taken to tune the server?
What metrics have you captured about this server/database?
Where did the 20 second rule come from? This seems like an arbitrary number that was pulled out of a hat. 20 seconds is not a very long time to have to wait for a large complex result set. Have you ran one of the offending procs in SSMS to get the actual time it does take to run? The 20 seconds would be very silly if the proc completed successfully in 30 seconds.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/