ADO.Net SQLCommand.ExecuteReader() 变慢或挂起

本文介绍了ADO.Net SQLCommand.ExecuteReader() 变慢或挂起的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

环境:

应用程序(用 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>)