We are seeing a strange issue in our Production environment . Often we see at specific time on a particular day of week , we receive sql timeout error while executing a query from .Net client.
SQLtrans = SQLCon.BeginTransaction(IsolationLevel.ReadCommitted);
SqlCommand SQLcmd = new SqlCommand("InsertInto_table_***", SQLCon, SQLtrans);
SQLcmd.CommandType = CommandType.StoredProcedure;
int RecId = (int)SQLcmd.ExecuteScalar();
sqlCmd.CommandText = "UPDATE table_*** WITH (ROWLOCK) SET MsgBody .Write(@datacontent, null, 0) OUTPUT DataLength(INSERTED.MsgBody) WHERE RecId = @id".
size of "datacontent" varies significantly from few hundred bytes to few hundred KBs (and are added in table in chunk of 8040 * 128) .
We ran the sql profiler and found out (for queries that failed due to timeout) Duration = 30092, Reads = 512003, which does suggest there is lot of IO activity going on and duration indicates that query time is greater than default timeout (hence timeout
Table has clustered index set on "RecId" column and Table size is around 20 GB.
Also, these errors do not happen everyday and when retried after some time we are able to insert and update the data.
View Complete Post