You are currently offline, waiting for your internet to reconnect

Enable the "-k" startup parameter to control the rate that work files can spill to tempdb for SQL Server

Summary
Currently, there is no limit or throttle on the I/O operations that occur when work files spill to the temporary database (tempdb). This can cause I/O problems. After you install this update, you can use the -k startup parameter to control the rate at which work files can spill to tempdb.

Update information

This update is included in the following cumulative update packages of SQL Server:
Recommendation: Install the latest cumulative update for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Note You can find information about the latest SQL Server builds from Where to find information about the latest SQL Server builds.
More information
For more information about this problem, consider the following:
  • DBCC CHECKDB checks the consistency of objects in batches. These batches might consist of multiple indexes or tables.
  • All data points that belong to a single batch are stored in an internal table. The contents of this internal table have to be sorted by using the following statement:
    SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB) FROM { IRowset 0x50426F2A48000000 } GROUP BY ROWSET_COLUMN_FACT_KEY >> WITH ORDER BY                ROWSET_COLUMN_FACT_KEY,                ROWSET_COLUMN_SLOT_ID,                ROWSET_COLUMN_COMBINED_ID,                ROWSET_COLUMN_FACT_BLOB
  • The sort buffers have to spill to tempdb in the following situations:
    • There is not enough physical memory in the computer.
    • Not enough query execution memory (memory grant) can be obtained.
  • The extent of the spill to tempdb depends on the size of the internal table. This depends on the pages that are allocated to the indexes that are being checked in that single batch.
  • Currently, there is no limit or throttle on the I/O operations that are involved in this spill to tempdb. This can cause I/O problems.
References
See update 929240 to learn about how the –k parameter can control checkpoint rates.

Learn about the terminology that Microsoft uses to describe software updates.
Properties

Article ID: 3133055 - Last Review: 02/22/2016 17:53:00 - Revision: 2.0

Microsoft SQL Server 2012 Service Pack 3, Microsoft SQL Server 2014 Service Pack 1

  • kbqfe kbsurveynew kbfix kbexpertiseinter KB3133055
Feedback
ml>t> dy>r="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">