This is a topic I have discussed before. Here is some more information about it.

I have, sometimes, a thread abort situation like this:

"Thread was being aborted."

This is the stack trace:

at SNIReadSyncOverAsync(SNI_ConnWrapper* , SNI_Packet** , Int32 )
at SNINativeMethodWrapper.SNIReadSyncOverAsync(SafeHandle pConn, IntPtr& packet, Int32 timeout)
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.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Framework.Data.SQLExec(String tcSQL)

This is from the data provider Fill() command.

This occurred on a weekend, where there was almost no traffic and almost no hits againts SQL Server. Two seconds before, the user did the same request. Then, he does it again and ends up in this situation.

In a period of two minutes, the user sends 7 hits to the server. They were navigational hits from a list. Thus, it was the same request being sent. Two succeeded. Five generated this error. The hits timed out between 28 to 30 minutes. They were send between 11:26:44 to 11:28:10. However, they got stacked and the IIS hits were all returned/completed in the same second. This was at 11:54:11. Thus, despite the fact the hits were sent to SQL Server on a period of two minutes, they all timed out in the same second very long after they were sent.

I just cannot find what caused this and understand this situation. As it occurred during the weekend, I can see that the user was not affected by other hits. He was almost alone in the application. I can also see that he was able to do the same hits before the error occurred and one between them.

I also verified the execution plan and it showed that it is using the proper index. In the error, I have the SQL command. When I paste this command in SSMS, I can see this. However, this is not a guarantee that during production, this is what SQL Server took for the index. I can only simulate that after from the command that was used. Since this situation started, I always thought that, sometimes, the same command may not use the proper index. Executing that command minutes later or 99% of the times that it would be executed after that it will always take the proper index. Is that possible?