SQL Server spool, worktable, and Workfile

First, Spool

Spool's Chinese explanation is spool, offline is the translation of offline. The synonym for spooling, is not really online? This can be understood because the two devices are indeed online, but are running in the background.

The principle of spooling, in layman's terms, is the exchange of data between two devices, except that a device transmits data at a fast speed, and a device transmits data slowly. In order to make full use of the high-speed equipment, the low-speed device transmits the data to the buffer when it is necessary to pass the data, while the high-velocity device does not wait for the low-speed device, but goes straight to the other task, when the low-speed device passes all the data, the high-speed device then reads the data and quickly This allows fast devices to be idle and improve utilization. Spool in order to match the two devices, a buffer is needed, the low-speed device writes data to the buffer, and the high-speed device reads the data from the buffer. Therefore, the principle of spool technology is to use buffer dump data to match the processing performance of the device.

Analyzing the principle of spooling, you can see that the spool is used for an additional buffer and two Io (write and read).

Second, SQL Server's spool

Birch's blog is written in very detailed "spool in SQL Server", like one!

SQL Server's Spool logical operations are divided into eager Spool and lazy Spool, when both operators appear, indicating that SQL Server needs to save the computed intermediate result set to the staging store, which is created in tempdb. The temporary store is worktable or workfile, and the contents of the storage are different.

Work files could is used to the store temporary results for hash joins and hash aggregates.

Work tables could is used to the store temporary results for query spool, LOB variables, XML variables, and cursors.

"Work files could is used to the store temporary results for hash joins and hash aggregates. The returned value should is less than 20. Tempdb work files was used in processing hash operations when the amount of data being processed was too large to fit into The available memory. "
Possible Problems:high values can indicate thrash in the tempdb file as well as poorly coded queries.

"Work tables could is used to the store temporary results for query spool, LOB variables, XML variables, and cursors. The returned value should is less than 20. Worktables is used for queries this use various spools (table spool, index spool, and so on). "
Possible Problems:high values could cause general slowdown.

Third, view Workfile and worktable

The purpose of creating workfile and worktable is to temporarily store the intermediate result set, increasing the IO burden on the system, and you can view the Workfile and worktable created by the query statement through the SET STATISTICS IO on command. View the IO information for the temporary storage area.

Recommended reading:

spooling in SQL Server

SQL Server spool, worktable, and Workfile