Microsoft distributes Microsoft SQL Server 2012 fixes in one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security updates that were included with the previous SQL Server 2012 update release.
Symptoms
Consider the following scenario:
-
You create a stored procedure that uses a table-valued parameter (TVP) in Microsoft SQL Server 2012.
-
You use SQL Profiler or SQL Server Extended Events to capture the RPC:Starting and RPC:Completed events.
-
You run a query that uses the TVP.
-
The query times out.
In this scenario, a non-yielding scheduler error occurs, and queries that are running on the same scheduler run slowly. Additionally, a mini-dump file is generated in the SQL Server log folder.
When this issue occurs, you receive an error message that resembles the following:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
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.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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at TVPApp.Program.Main(String[] args)
The non-yielding scheduler error together with an error message that resembles the following is logged in the SQL Server error log:
<Date><Time> Server Using 'dbghelp.dll' version '4.0.5'
<Date><Time> Server ***Unable to get thread context for spid #
<Date><Time> Server * *******************************************************************************
<Date><Time> Server *
<Date><Time> Server * BEGIN STACK DUMP:
<Date><Time> Server * <Date><Time> spid #
<Date><Time> Server *
<Date><Time> Server * Non-yielding Scheduler
<Date><Time> Server *
<Date><Time> Server * *******************************************************************************
<Date><Time> Server Stack Signature for the dump is 0x000000000000002C
<Date><Time> Server External dump process return code 0x20000001.
External dump process returned no errors.
<Date><Time> Server Process 0:0:0 (0x1670) Worker 0x0000000004EDE160 appears to be non-yielding on Scheduler 5. Thread creation time: 13014210386757. Approx Thread CPU Used: kernel 0 ms, user 70106 ms. Process Utilization 16%. System Idle 77%. Interval: 70140 ms.
<Date><Time> Server Process 0:0:0 (0x1670) Worker 0x0000000004EDE160 appears to be non-yielding on Scheduler 5. Thread creation time:
Cause
This issue occurs because of an error in SQL Server 2012 Database Engine. The text columns in the TVP are implemented as insert strings or buffers, and an increase in the number of rows increases the number of buffers. However, the process that releases buffers is time-consuming. Therefore, a non-yielding scheduler error is generated when release and shrink operations occur.
Note A similar issue has been addressed in the following Microsoft Knowledge base article. However, the cumulative update package that is described in the article is not for a query time-out issue.
2520808 FIX: Non-yielding scheduler error when you run a query that uses a TVP in SQL Server 2008 or in SQL Server 2008 R2 if SQL Profiler or SQL Server Extended Events is used
Resolution
Cumulative update information
Cumulative update package 5 for SQL Server 2012 Service Pack 1 (SP1)
The fix for this issue was first released in Cumulative Update 5. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:
2861107 Cumulative update package 5 for SQL Server 2012Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2772858 The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
Workaround
To work around this issue, use one of the following methods:
-
Do not use SQL Profiler or SQL Server Extended Events.
-
Click to clear the RPC:Starting and RPC:Completed check boxes in the trace definition.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
