System.Data.SqlClient.SqlException (0x80131904): Timeout expired. Three different cases

Question

I've started to encounter timeout errors, but not sure how to approach them because they are different each time.

First type of timeout

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
   at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
   at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.BeginDbTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)

Second type of timeout

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

Third type of timeout

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 419

How can I diagnose these problems? Where to look at? Why the same Timeout error happens on three different operations? On the client side, CommandTimeout-setting is this my last resort?

Also, one important detail: I happened to encounter this Timeout error even NOT under load in the TEST environment. .NET 6, MARS=false, Windows, Timeout=30. Only CommandTimeout has default value. Unfortunately, I did not manage to catch the exact stack trace.

PS. Looking at the search results, there does not seem to be THE solution to my problems. In one case, Timeout might help [Tried - helped to eliminate Connection timeouts]. In another - CommandTimeout [Alright, I might try it, but to type it EVERYWHERE I use the database? Also can it help with BeginTransaction-timeouts?]. Somewhere even lies the MARS-setting, but the problem reproduces even with MARS disabled. Linux or Windows - does not matter either, because on Windows problem are encountered too. We've thought migration to .NET 6 might help, but it also didn't help.

Answer 1

A link to MSDN someone having the same problem. I believe the relevant info is this:

The message as such is the standard message when the instance cannot be reached. However, error 258 is unusual in this context. And it is not an SQL Server error, it's an OS error; NET HELPMSG 258 says The wait operation timed out.

This could possibly indicate that you are arriving somewhere, but no one is actually listening. That is, the port is open but the process is busy with something else. (And that is necessarily not an SQL Server process.)

So, how's your SQL Server doing? Can't keep up with workload? Crashing intermittently? Antivirus and/or network protection software?

Troubleshooting tips

google sp_Blitz and siblings. They're free scripts that will help you find issues on your SQL Server. Also sp_whoIsActive - it's pretty good at showing blocking problems.

To find Threadpool starvation your best bet is probably wait stats.

Check the sys.dm_os_wait_stats DMV in sql server. Select it (and save), wait a bit, and select again - wait stats are accumulated, so select * from sys.dm_os_wait_stats will not give you anything usefull. sp_Blitz will help you do this btw.

If you have Query Store enabled, you can easily find top resource consuming queries, but also a somewhat useful wait stats breakdown - not sure where Threadpool waits is though.