Hi, clients using our application from time to time report the error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

I understand this error could occur from:

Long running tasks, uncommitted transactions, table locking, server resources or network latency.

The thing is our application needs to be a corporate solution for multiple users at the same time so extending the timeout expiration to infinity could be a solution to accommodate long running tasks, table locking, server resources and network latency. But the user experience would be pretty poor when that occurs.

If the timeout is from table locking by long running transactions I think that improving all SQL statements locking by using ROWLOCKING would allow the application to be used by multiple people at the same time. 

The increased timeout value would however still be needed for servers with low resources or high network latency.

Are these valid conclusions? I am looking for some validation before sending my team of to change the application.

>>>Long running tasks, uncommitted transactions, table locking, server resources or network latency.

Yes all those things are valid.... BTW what timeout value have you specified in the connection string?

Hi Daniel,

The reasons a command runs longer than expected is commonly due to blocking, the need for query/index tuning or both.  A quick way to check for blocking to to run sp_who2 while the UPDATE is running.  The BlkBy column will show the SPID of the blocking connection if the UPDATE is blocked. 

Check the execution plan to verify of the UPDATE statement to verify only the needed rows are touched.  For example, if your intent is to update a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.  Similarly, check the execution plans of blocking SELECT queries for optimization opportunities.

Can you set i to 0 and check again? Moreover,

read Erland's article http://www.sommarskog.se/query-plan-mysteries.html

Thanks Uri, I can't set it to zero because there are many transactions within the database and the developers have a different project to handle the SQL that they have not given me the source for. I told them ages ago to allow me to override the time-out but they didn't implement it for all of the SQL statements. Furthermore the timeout value is given to the classes I don't have the source for so can't investigate them... I am still trying to pry it out of their hands.

I will give that article a read thanks!

Thanks Milan, I gave the sp_who2 a little go yesterday, the transaction duplicates what we call a project which contains many linked tables, so the transaction reads into our domain objects then is inserting many records into multiple tables used by all other projects in the database. I am starting to believe that the issue is blocking. I do think that either the SQL Server locking escalation is blocking other users from using one or more of the tales during this big duplication process or the SQL code is doing it...

However from our developers coding the data layer none of the SQL commands use NOLOCK, ROWLOCK or READUNCOMMITTED. From my research so far it looks like to remove/reduce the blocking each SQL command they use will have to be updated with the correct locking command.

Yesterday I did a trace on our resource limited SQL Server 2008 R2 SP3 the duplication process. There are thousands of SQL Batches so there isn't just one UPDATE statement to test against.

I will however over time look at the execution queries of the SQL statements that take the longest duration.

This duplication process take over 30 seconds to complete, around 1-2 minutes. 14,200 row linked to 1000 rows in another table linked to 100 rows in another table linked to the specific project's id. Plus many many more tables involved for lookup values and what not.

Currently my belief is all the SQL need ROWLOCK to ensure table locking doesn't happen.. Is that valid?

Looks like HQL is used to create the SQL based on the object. There are thousands of SQL commands sent to the SQL server during this duplication process. 

What about transaction Isolation? I have started looking into that. A duplicate process should just dirty read the information and not lock it because in a multi-user application many users will be using the same tables at the same time as other users.

But within the one transaction if it is reading a lot of information and duplicating that information a ReadUncommited isolation level would still be ok? 

if no isolation level is used what happens?

Soooooo after some testing with my in house developer we found that the Locks available for the SQL server is initialset to 5000. Due to the way the application is programmed the process required around 180,000 locks during the duplication process that was all wrapped up in one transaction. 

How it needs 180,000 locks when there was only 4000 records within the main table is beyond me atm. The isolation level was unspecified. 

So to resolve the issue we increase the Locks value for the SQL Server to 500,000.

Is this a valid solution? Yes it made the application work multi-user for both me and the other user. But if 100 users where trying to do this at the same time the time-out errors would reappear for sure.

Is there a best practice for this? My research on my part required I guess.