Environment:
Application (written in C# for .Net 4) has up to 10 threads, each thread runs in its own AppDomain. Each thread uses a ADO.Net DataReader that gets the results from the stored procedure on SQL-Server 2008. Also a thread can use ADO.Net to perform a write operation (Bulk Insert). Everything runs on the local machine.
Problem #1:
Occasionally (approximately each 30th run) execution of a thread slows down drastically. That happens when DataReader gets the stored procedure results - SqlCommand.ExecuteReader(). Usually read operation executes in 10 seconds. When it slows down, it executes in 10-20 minutes. SQLProfiler shows that data is being queried, though very slowly.
Callstack of the slowdown (please note that there are no exceptions):
at SNIReadSync(SNI_Conn* , SNI_Packet** , Int32 )
at SNINativeMethodWrapper.SNIReadSync(SafeHandle pConn, IntPtr& packet, Int32 timeout)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
at System.Data.SqlClient.TdsParserStateObject.ReadString(Int32 length)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at ****.Core.TableDataImporter.ImportDataFromExcel(Int32 tableId, ExcelEntityLocation location, Boolean& updateResult) in …
Problem #2:
Instead of a slowing down a thread can hang.
Callstack:
at SNIReadSync(SNI_Conn* , SNI_Packet** , Int32 )
at SNINativeMethodWrapper.SNIReadSync(SafeHandle pConn, IntPtr& packet, Int32 timeout)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
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.ExecuteReader()
Callstacks were acquired using debug tools in the background thread. No exceptions do happen, either a slowdown or hanging up.
SNIReadSync is a mechanism that works on the network level and works with transmitting packets across the network. We have reproduced this problem on local machine, removing network problems from the equation.
We are looking for any input and solutions or workarounds for this slowdowns/hang ups. For now we a planning to detect the slowdown an rerun the operation. Thanks in advance.
I'm appending simplified code for the method as requested:
public void ImportDataFromExcel()
{
try
{
var _сonnectionBuilk = ... ; // singleton connection (at the app level)
var spName = ... ; // stored procedure name
var сonnectionToRead = new SqlConnection(connectionStirng);
сonnectionToRead.Open();
var sqlCommand = new SqlCommand(spName);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(param1Name, SqlDbType.Int).Value = ...;
sqlCommand.Parameters.Add(param2Name, SqlDbType.Int).Value = ...;
sqlCommand.Parameters.Add(param2Name, SqlDbType.Int).Value = ...;
sqlCommand.Connection = сonnectionToRead;
sqlCommand.CommandTimeout = timeout; // 120 sec
using (var dataReader = sqlCommand.ExecuteReader())
{
dataReader.Read();
.....
int pos1 = dataReader.GetOrdinal(columnName1);
int pos2 = dataReader.GetOrdinal(columnName2);
int pos3 = dataReader.GetOrdinal(columnName3);
int pos4 = dataReader.GetOrdinal(columnName4);
.....
// reading data from sqldatareader
int val1 = dataReader.GetInt32(pos1);
int val2 = dataReader.GetInt32(pos2);
int val3 = dataReader.GetInt32(pos3);
var val4 = dataReader.GetDateTime(pos4);
.....
// append read data into bulkTable
bulkTable.AddCellValue(val1, val2, val3, val4); // bulkTable wraps DataTable, and appends DataRow inside.
if(bulkTable.DataTable.Rows > MaxRowsCount)
{
using (var bulkCopy = new SqlBulkCopy(_сonnectionBuilk))
{
bulkCopy.DestinationTableName = _fullTableName;
bulkCopy.WriteToServer(bulkTable.DataTable);
}
var sqlCommandTransfer = new SqlCommand(spName);
sqlCommandTransfer.CommandType = CommandType.StoredProcedure;
sqlCommandTransfer.Parameters.Add(param1Name, SqlDbType.Int).Value = ...;
sqlCommandTransfer.Connection = _сonnectionBuilk;
....
sqlCommandTransfer.ExecuteNonQuery(); // transfering data from temp bulk table into original table
}
}
}
finally
{
bulkTable.Dispose();
сonnectionToRead.Close();
}
}
