问题描述
环境:
应用程序(用 C# 为 .Net 4 编写)最多有 10 个线程，每个线程在其自己的 AppDomain 中运行.每个线程都使用 ADO.Net DataReader 从 SQL-Server 2008 上的存储过程中获取结果.线程也可以使用 ADO.Net 执行写入操作(批量插入).一切都在本地机器上运行.
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.
问题#1:
有时(大约每 30 次运行)线程的执行速度会急剧下降.这发生在 DataReader 获取存储过程结果 - SqlCommand.ExecuteReader() 时.通常读取操作在 10 秒内执行.当它变慢时，它会在 10-20 分钟内执行.SQLProfiler 显示正在查询数据，尽管速度很慢.
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(请注意没有异常):
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 …
问题 #2:
线程可以挂起，而不是减慢速度.
Instead of a slowing down a thread can hang.
调用栈:
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 是一种在网络级别工作的机制，用于通过网络传输数据包.我们已经在本地机器上重现了这个问题，从等式中消除了网络问题.
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();
}
}
推荐答案
几个月来我们一直在尝试调试类似的问题，今天终于找到了它......
We've been trying to debug a similar issue for months, and finally tracked it down today...
我们有一个查询被隐藏到缓存中(没有调用 ToList/ToArray/等).该查询有效地绑定到一个已被清理的连接，并且我们从 ReadSni 获得了似乎 100% 的 CPU 阻塞(下面包含完整堆栈).
We had a query that was being stashed into the cache (without calliong ToList/ToArray/etc. on it). The query was effectively tied to a connection that had since been cleaned up, and we got what appeared to be 100% CPU blocking from ReadSni (full stack included below).
我怀疑缓存代码是在查询转换为使用 Linq 之前编写的(并且过去常常返回一个 List<T>，但仍然转换为 IEumberable) 所以它是在有人让数据访问懒惰"时引入的.
I suspect the caching code was written before the query was changed over to use Linq (and used to instead return a List<T>, but still cast as IEumberable) so it was introduced when someone made the data access "lazy".
我无法解释为什么它在生产中每隔几天就会发生一次；要么缓存没有被大量使用，要么连接必须处于某种状态才能以这种方式失败.
I can't explain why it only happened every few days in production; either the caching isn't being used a lot, or the connection had to be in a certain state for it to fail in this way.
OS Thread Id: 0x20b8 (27)
Child SP IP Call Site
16edd0fc 6184267e System.Data.SqlClient.TdsParserStateObject.ReadSni(System.Data.Common.DbAsyncResult, System.Data.SqlClient.TdsParserStateObject)
16edd134 61842624 System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
16edd144 618446af System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
16edd150 61c583d0 System.Data.SqlClient.TdsParserStateObject.CleanWire()
16edd15c 61d1beb9 System.Data.SqlClient.TdsParser.Deactivate(Boolean)
16edd174 6184995f System.Data.SqlClient.SqlInternalConnectionTds.InternalDeactivate()
16edd180 61849640 System.Data.SqlClient.SqlInternalConnection.Deactivate()
16edd1b0 61849587 System.Data.ProviderBase.DbConnectionInternal.DeactivateConnection()
16edd1e4 61849405 System.Data.ProviderBase.DbConnectionPool.DeactivateObject(System.Data.ProviderBase.DbConnectionInternal)
16edd224 61849384 System.Data.ProviderBase.DbConnectionPool.PutObject(System.Data.ProviderBase.DbConnectionInternal, System.Object)
16edd26c 6184920c System.Data.ProviderBase.DbConnectionInternal.CloseConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory)
16edd2ac 618490f7 System.Data.SqlClient.SqlInternalConnection.CloseConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory)
16edd2c4 618393bf System.Data.SqlClient.SqlConnection.Close()
16edd304 11238f0a NHibernate.Connection.ConnectionProvider.CloseConnection(System.Data.IDbConnection)
16edd340 11238eae NHibernate.Connection.DriverConnectionProvider.CloseConnection(System.Data.IDbConnection)
16edd34c 11aceb42 NHibernate.AdoNet.ConnectionManager.CloseConnection()
16edd358 11aceb02 NHibernate.AdoNet.ConnectionManager.AggressiveRelease()
16edd364 11acf783 NHibernate.AdoNet.ConnectionManager.AfterTransaction()
16edd370 11acf6d1 NHibernate.Impl.SessionImpl.AfterTransactionCompletion(Boolean, NHibernate.ITransaction)
16edd3ec 11acf5de NHibernate.AdoNet.ConnectionManager.AfterNonTransactionalQuery(Boolean)
16edd3fc 11acf539 NHibernate.Impl.AbstractSessionImpl.AfterOperation(Boolean)
16edd474 130311e4 NHibernate.Impl.SessionImpl.List(NHibernate.IQueryExpression, NHibernate.Engine.QueryParameters, System.Collections.IList)
16ede51c 13031071 NHibernate.Impl.AbstractSessionImpl.List(NHibernate.IQueryExpression, NHibernate.Engine.QueryParameters)
16ede538 13030b68 NHibernate.Impl.ExpressionQueryImpl.List()
16ede568 13030a47 NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NHibernate.Linq.NhLinqExpression, NHibernate.IQuery, NHibernate.Linq.NhLinqExpression)
16ede59c 11d4c163 NHibernate.Linq.DefaultQueryProvider.Execute(System.Linq.Expressions.Expression)
16ede5b0 11d4c108 NHibernate.Linq.DefaultQueryProvider.Execute[[System.__Canon, mscorlib]](System.Linq.Expressions.Expression)
16ede5c4 11d4c0a6 Remotion.Linq.QueryableBase`1[[System.__Canon, mscorlib]].GetEnumerator()
16ede5d4 61022108 System.Linq.Enumerable+WhereEnumerableIterator`1[[System.__Canon, mscorlib]].MoveNext()*** WARNING: Unable to verify checksum for System.Core.ni.dll
*** ERROR: Module load completed but symbols could not be loaded for System.Core.ni.dll
16ede5e4 610166ea System.Linq.Buffer`1[[System.__Canon, mscorlib]]..ctor(System.Collections.Generic.IEnumerable`1<System.__Canon>)
16ede620 6122e171 System.Linq.OrderedEnumerable`1+<GetEnumerator>d__0[[System.__Canon, mscorlib]].MoveNext()
16ede63c 79b39758 System.Collections.Generic.List`1[[System.__Canon, mscorlib]]..ctor(System.Collections.Generic.IEnumerable`1<System.__Canon>)*** WARNING: Unable to verify checksum for mscorlib.ni.dll
*** ERROR: Module load completed but symbols could not be loaded for mscorlib.ni.dll
16ede66c 61021acf System.Linq.Enumerable.ToList[[System.__Canon, mscorlib]](System.Collections.Generic.IEnumerable`1<System.__Canon>)
这篇关于ADO.Net SQLCommand.ExecuteReader() 变慢或挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！