Query timeout
The reason that queries are receiving timeout errors is likely to because the statistics are being updated and queries need to wait for the update to complete.
If the value is Timeout, you should spend time tuning your query because the property indicates that the current query plan is not optimal.
You may want to use delayed durable transactions, also known as lazy commits, to solve the timeout problem.
By using delayed durable approach, SQL Server can process more concurrent transactions with less contention for log IO, thereby increasing throughput. Once the transaction is written to the transaction log, SQL Server reports a successful transaction and any changes that it made are visible to other transactions. However, all transaction logs remain in the log buffer until the buffer is full or a buffer flush event occurs, at which point the transaction is written to disk and becomes durable. A buffer flush occurs when a fully durable transaction in the same database commits or a manual request to execute sp_flush_log is successful.
Therefore, Delayed durability is useful when you are willing to trade potential data loss for reduced latency in transaction log writes and reduced contention between transactions. Such a trade-off is acceptable in a data warehouse workload that runs batches frequently enough to pick up rows lost in a previous batch. The eventual resolution of data loss is acceptable alternative to durability only because the data warehouse is not the system of record. Delayed durability is rarely acceptable in an online transaction processing (OLTP)system.
Delayed durability won't fix the timeout issue during statistic updates.
Example:
Answer:
