KB3133055 - 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.

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 Database Engine Service Startup Options to learn about how the –k parameter can control checkpoint rates.

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