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

ADO.Net SQLCommand.ExecuteReader() slows down or hangs(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>)

这篇关于ADO.Net SQLCommand.ExecuteReader() 变慢或挂起的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

DispatcherQueue null when trying to update Ui property in ViewModel(尝试更新ViewModel中的Ui属性时DispatcherQueue为空)
Drawing over all windows on multiple monitors(在多个监视器上绘制所有窗口)
Programmatically show the desktop(以编程方式显示桌面)
c# Generic Setlt;Tgt; implementation to access objects by type(按类型访问对象的C#泛型集实现)
InvalidOperationException When using Context Injection in ASP.Net Core(在ASP.NET核心中使用上下文注入时发生InvalidOperationException)
LINQ many-to-many relationship, how to write a correct WHERE clause?(LINQ多对多关系,如何写一个正确的WHERE子句?)