SQL Server 存儲中間結果集

在執行查詢語句時，有一些操作符會産生中間結果集，例如：Hash Join和Hash Aggregate操作産生的Hash Table，排序和分組操作會産生中間結果集等，後續的操作符對中間結果集執行操作，以響應使用者的請求。

SQL Server的Spool（假脫機）操作符，用于把前一個操作符處理的資料（又稱作中間結果集）存儲到一個隐藏的臨時結構中，以便在執行過程中重用這些資料。這個臨時結構都建立在tempdb中，通常的結構是工作表（worktable）和工作檔案（workfile）。假脫機運算符會取出表或索引結構中的一部分的資料集，将他們存放在tempdb資料庫的臨時結構裡，我推測：如果這個臨時結構用于存儲表資料，稱作worktable；如果這個臨時結構存儲的是Hash表，稱作workfile。

一，Spool 操作符

在 SQL Server的執行計劃中，Spool主要分為Eager Spool 和 Lazy Spool 這兩個邏輯操作符：

• Eager Spool：一次性把所有資料存儲到臨時結構中，它是一個阻塞性的操作符，這意味着它需要讀取輸入中的所有資料，然後處理完所有的資料行之後，才向下一個操作符傳回結果，也就是說，除非Eager Spool把所有的資料行都處理完成，否則無法通路到這些資料。
• Lazy Spool：逐行把資料存儲到臨時結構中，它是一個非阻塞性的操作符，這意味着它可以邊讀取資料，邊向下一個操作符輸出資料，也就是說，在Lazy Spool讀取完所有的資料之前，可以通路這些資料。

如果執行計劃中出現這兩個操作符，表明 SQL Server 需要将中間結果集儲存到臨時存儲區，在SQL Server中，臨時存儲區是指Worktable 或 Workfile，都建立在tempdb中， 用于臨時存儲中間結果集，以便後續的操作符重用資料：

• Workfile： 用于存儲Hash Join 和 Hash 聚合産生的哈希表，小的臨時結果集存儲在Memory中；
• Worktable： 用于存儲 Query Spool（table spool, index spool, and so on），Lob 變量，XML變量和遊标的臨時結果集；

WorkFile和WorkTable都建立在記憶體中，如果臨時結果集太大，配置設定的Memory容納不了，那麼臨時結果集就會被寫入到tempdb的檔案中，即：spill to disk（tempdb files），也就是說，調用IO子系統把資料寫入到硬碟中，這會降低查詢語句的執行性能。一般來說，如果SQL Server在執行計劃中使用WorkFile 或 WorkTable，那麼表明查詢語句的性能較低

引用MSDN中對workfile 和 worktable的描述：

Work files could be used to store temporary results for hash joins and hash aggregates. The returned value should be less than 20. Tempdb work files are used in processing hash operations when the amount of data being processed is 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 be used to store temporary results for query spool, lob variables, XML variables, and cursors. The returned value should be less than 20. Worktables are used for queries that use various spools (table spool, index spool, and so on)." Possible problems: High values could cause general slowdown.

workfile和worktable用于存儲臨時資料，如果資料量超過20，那麼workfile和worktable中的資料會被寫入到tempdb的檔案中去。

二，Workfile 和 Worktable的應用場景

查詢優化器建立Workfile 和 Worktable 的目的是為了存儲臨時結果集，在執行查詢時，設定 “Set Statistics IO On” ，能夠看到查詢語句建立的 Workfile 和 Worktable，以及臨時存儲區的IO資訊，如圖：

一般情況下，如果臨時存儲對象（Workfile 和 Worktable）被建立，說明查詢的中間結果集太大，伺服器的記憶體資源将被耗盡，導緻SQL Server 必須使用Disk存儲中間結果集，才能使查詢操作繼續執行，但是，可用的記憶體耗盡和額外的IO開銷，都會降低查詢語句的執行性能。

SQL Server查詢優化器建立Workfile 和 Worktable存儲中間結果集，當中間結果集較小時，中間結果集會被儲存在記憶體中；如果結果集過大，為了減輕系統記憶體的壓力，SQL Server會把中間結果集轉存到tempdb的資料檔案中，也就是說，把資料集從記憶體寫入到Disk檔案中。在重新使用中間結果集時，SQL Server從Disk File把資料讀取到記憶體中，這回産生額外的Disk IO，降低SQL Server的查詢性能。

1，使用Worktable

worktable是一個内部的關系表，用于存儲中間結果集。關系引擎在執行 order by、group by或者union操作時，會建立Worktable，例如，在執行Order by 操作時，如果表沒有在排序字段上建立索引，那麼SQL Server查詢優化器會在tempdb中建立一個Worktable來緩存執行計劃産生的中間結果集，并對中間結果集進行排序。SQL Server在有需要時自動在tempdb中建立Worktable；當不再需要中間資料時，SQL Servere會自動從tempdb中删除worktable。

引用MSDN中關于使用worktable的描述：

The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

優化方法：為了減少Worktable的使用，應該優化TSQL語句，減少不必要的group by，order by或Union操作，在select 子句中剔去不必要的column，使傳回的結果集盡可能的小。

2，使用Workfile

SQL Server在執行Hash Join 或 Hash聚合操作時，建立Workfiles來存儲哈希表，哈希表是一個鍊式結構。

Hash Join 能夠有效處理大量，未排序，未索引的輸入；為了減少Workfile的使用，在和大表進行Join查詢時，盡可能地使用索引列，進行Hash Join或Hash 聚合操作。