StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat does it indicate when a table spool has more than one
text
Body
copied!<p><img src="https://i.stack.imgur.com/me3Vr.png" alt="Table spool"></p> <p>According to <a href="http://msdn.microsoft.com/en-us/library/ms181032.aspx" rel="nofollow noreferrer">MSDN</a>,</p> <blockquote> <p>The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. </p> </blockquote> <p>First, there's no input in the plan before this step... This text indicates that it should and <a href="http://www.simple-talk.com/sql/learn-sql-server/operator-of-the-week---spools,-eager-spool/" rel="nofollow noreferrer">other resources</a> online have said as much.</p> <blockquote> <p>The spool operators are always used together with another operator. As it stores values, it needs to know what these values are, and so it must receive them from another operator. </p> </blockquote> <p>Second, when the output list in the hover over contains more than one table. Are all of them being spooled to individual tables? and if so, why not show multiple steps? If they are all being spooled to one table, are they being joined prior to the spooling? A 'flattening' out of the tables, if you will.</p> <p>Third, as I explain below I think I have an answer for 1 and 2... but now I see the cost % for the original step is 0% and when that spool is reused in another branch the cost % is 12%. Why would reading it back cost more than the original writing out?</p> <p>EDIT : Text Plan</p> <pre><code>StmtText |--Compute Scalar(DEFINE:([so].[SLS_ORG_OPR_UNT_SRC_KEY]=[Expr1021], [so].[SLS_ORG_PROD_SRC_KEY]=[Expr1022], [cal].[WEEK_END_DATE]=[Expr1023], [so].[SLS_ORG_EMPL_SRC_KEY]=[Expr1024], [so].[SLS_ORG_JOBCODE]=[Expr1025])) |--Hash Match(Inner Join, HASH:([Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025])=([Expr1016], [Expr1017], [Expr1018], [Expr1019], [Expr1020]), RESIDUAL:([Expr1016] = [Expr1021] AND [Expr1017] = [Expr1022] AND [Expr1018] = [Expr1023] AND [Expr1019] = [Expr1024] AND [Expr1020] = [Expr1025])) |--Compute Scalar(DEFINE:([Expr1021]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_OPR_UNT_SRC_KEY] as [so].[SLS_ORG_OPR_UNT_SRC_KEY], [Expr1022]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_PROD_SRC_KEY] as [so].[SLS_ORG_PROD_SRC_KEY], [Expr1023]=[DMRPT].[dbo].[DIM_CALENDAR].[WEEK_END_DATE] as [cal].[WEEK_END_DATE], [Expr1024]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_EMPL_SRC_KEY] as [so].[SLS_ORG_EMPL_SRC_KEY], [Expr1025]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_JOBCODE] as [so].[SLS_ORG_JOBCODE])) | |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1059],0))) | |--Stream Aggregate(GROUP BY:([so].[SLS_ORG_OPR_UNT_SRC_KEY], [so].[SLS_ORG_PROD_SRC_KEY], [cal].[WEEK_END_DATE], [so].[SLS_ORG_EMPL_SRC_KEY], [so].[SLS_ORG_JOBCODE]) DEFINE:([Expr1059]=COUNT([Expr1015]))) | |--Sort(DISTINCT ORDER BY:([so].[SLS_ORG_OPR_UNT_SRC_KEY] ASC, [so].[SLS_ORG_PROD_SRC_KEY] ASC, [cal].[WEEK_END_DATE] ASC, [so].[SLS_ORG_EMPL_SRC_KEY] ASC, [so].[SLS_ORG_JOBCODE] ASC, [Expr1015] ASC)) | |--Table Spool | |--Compute Scalar(DEFINE:([Expr1015]=[Expr1013]+[Expr1014])) | |--Hash Match(Inner Join, HASH:([so].[SLS_ORG_GK])=([f].[SLS_ORG_GK]), RESIDUAL:([DMRPT].[dbo].[FACT_PRODUCER_SPREAD_HOURS].[SLS_ORG_GK] as [f].[SLS_ORG_GK]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_GK] as [so].[SLS_ORG_GK])) | |--Clustered Index Scan(OBJECT:([DMRPT].[dbo].[DIM_SALES_ORG].[SYS_SLS_ORG_GK] AS [so])) | |--Hash Match(Inner Join, HASH:([c].[CONTR_GK])=([f].[CONTR_GK]), RESIDUAL:([DMRPT].[dbo].[FACT_PRODUCER_SPREAD_HOURS].[CONTR_GK] as [f].[CONTR_GK]=[DMRPT].[dbo].[DIM_CONTRACTOR].[CONTR_GK] as [c].[CONTR_GK])) | |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(numeric(38,0),[DMRPT].[dbo].[DIM_CONTRACTOR].[CONTR_SRC_KEY] as [c].[CONTR_SRC_KEY],0))) | | |--Index Scan(OBJECT:([DMRPT].[dbo].[DIM_CONTRACTOR].[nc_csk] AS [c])) | |--Hash Match(Inner Join, HASH:([r].[REQ_POS_GK])=([f].[REQ_POS_GK]), RESIDUAL:([DMRPT].[dbo].[FACT_PRODUCER_SPREAD_HOURS].[REQ_POS_GK] as [f].[REQ_POS_GK]=[DMRPT].[dbo].[DIM_REQUISITION_POS].[REQ_POS_GK] as [r].[REQ_POS_GK])) | |--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(numeric(38,0),[DMRPT].[dbo].[DIM_REQUISITION_POS].[REQ_POS_JOB_REQ_SRC_KEY] as [r].[REQ_POS_JOB_REQ_SRC_KEY],0)+[DMRPT].[dbo].[DIM_REQUISITION_POS].[REQ_POS_POSITION_NO] as [r].[REQ_POS_POSITION_NO])) | | |--Clustered Index Scan(OBJECT:([DMRPT].[dbo].[DIM_REQUISITION_POS].[SYS_C00600262] AS [r])) | |--Hash Match(Inner Join, HASH:([cal].[DATE_GK])=([f].[WEEK_END_DATE_GK]), RESIDUAL:([DMRPT].[dbo].[FACT_PRODUCER_SPREAD_HOURS].[WEEK_END_DATE_GK] as [f].[WEEK_END_DATE_GK]=[DMRPT].[dbo].[DIM_CALENDAR].[DATE_GK] as [cal].[DATE_GK])) | |--Clustered Index Scan(OBJECT:([DMRPT].[dbo].[DIM_CALENDAR].[SYS_C00599872] AS [cal]), WHERE:([DMRPT].[dbo].[DIM_CALENDAR].[FULL_DATE] as [cal].[FULL_DATE]>=getdate()-'1900-01-31 00:00:00.000')) | |--Clustered Index Scan(OBJECT:([DMRPT].[dbo].[FACT_PRODUCER_SPREAD_HOURS].[PK_FCT_PRDCR_SPRD_HRS] AS [f])) |--Compute Scalar(DEFINE:([Expr1016]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_OPR_UNT_SRC_KEY] as [so].[SLS_ORG_OPR_UNT_SRC_KEY], [Expr1017]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_PROD_SRC_KEY] as [so].[SLS_ORG_PROD_SRC_KEY], [Expr1018]=[DMRPT].[dbo].[DIM_CALENDAR].[WEEK_END_DATE] as [cal].[WEEK_END_DATE], [Expr1019]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_EMPL_SRC_KEY] as [so].[SLS_ORG_EMPL_SRC_KEY], [Expr1020]=[DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_JOBCODE] as [so].[SLS_ORG_JOBCODE])) |--Hash Match(Aggregate, HASH:([so].[SLS_ORG_OPR_UNT_SRC_KEY], [so].[SLS_ORG_PROD_SRC_KEY], [cal].[WEEK_END_DATE], [so].[SLS_ORG_EMPL_SRC_KEY], [so].[SLS_ORG_JOBCODE]), RESIDUAL:([DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_OPR_UNT_SRC_KEY] as [so].[SLS_ORG_OPR_UNT_SRC_KEY] = [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_OPR_UNT_SRC_KEY] as [so].[SLS_ORG_OPR_UNT_SRC_KEY] AND [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_PROD_SRC_KEY] as [so].[SLS_ORG_PROD_SRC_KEY] = [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_PROD_SRC_KEY] as [so].[SLS_ORG_PROD_SRC_KEY] AND [DMRPT].[dbo].[DIM_CALENDAR].[WEEK_END_DATE] as [cal].[WEEK_END_DATE] = [DMRPT].[dbo].[DIM_CALENDAR].[WEEK_END_DATE] as [cal].[WEEK_END_DATE] AND [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_EMPL_SRC_KEY] as [so].[SLS_ORG_EMPL_SRC_KEY] = [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_EMPL_SRC_KEY] as [so].[SLS_ORG_EMPL_SRC_KEY] AND [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_JOBCODE] as [so].[SLS_ORG_JOBCODE] = [DMRPT].[dbo].[DIM_SALES_ORG].[SLS_ORG_JOBCODE] as [so].[SLS_ORG_JOBCODE]) DEFINE:([Expr1010]=SUM([DMRPT].[dbo].[FACT_PRODUCER_SPREAD_HOURS].[SPREAD_LCL] as [f].[SPREAD_LCL]))) |--Table Spool </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload