SQL Server: Spool Operationen

Weiterhin neige ich dazu, Datenbank-Features anderer RDBMS mit dem Idealtypus Oracle zu vergleichen. Bei Rob Farley liest man zu Spooling Operationen in Execution Plans im SQL Server folgende Erläuterung:

The spool operator consumes a set of data, and stores it in a temporary structure, in the tempdb database. This structure is typically either a Table (ie, a heap), or an Index (ie, a b-tree). If no data is actually needed from it, then it could also be a Row Count spool, which only stores the number of rows that the spool operator consumes. A Window Spool is another option if the data being consumed is tightly linked to windows of data, such as when the ROWS/RANGE clause of the OVER clause is being used. You could maybe think about the type of spool being like whether the cotton is going onto a small bobbin to fit in the base of the sewing machine, or whether it’s a larger spool for the top.

A Table or Index Spool is either Eager or Lazy in nature. Eager and Lazy are Logical operators, which talk more about the behaviour, rather than the physical operation. If I’m sewing, I can either be all enthusiastic and get all my cotton onto the spool before I start, or I can do it as I need it. “Lazy” might not the be the best word to describe a person – in the SQL world it describes the idea of either fetching all the rows to build up the whole spool when the operator is called (Eager), or populating the spool only as it’s needed (Lazy).

Window Spools are both physical and logical. They’re eager on a per-window basis, but lazy between windows.

Demnach handelt es sich also um die temporäre Persistierung von Daten in der tempdb (also dem Äquivalent zum temporary tablespace in Oracle). In Oracle würden den Spooling Operationen demnach verschiedene Elemente im Execution Plan entsprechen - etwa die TEMP TABLE TRANSFORMATION bei der Zwischenspeicherung von CTEs. Im SQL Server sind diese Operationen vermutlich häufiger als in Oracle, da sie auch als Ersatz für eine solide Lesekonsistenz dienen müssen ("When data needs to be kept away from the original source").