【Complete Showplan Operators】Chapter 7: Spools – Non-Clustered Index Spool
"Indexes, Indexes, Indexes…" to misquote(vt. 错误地引用vi. 错误引证n. 引用错误) a certain prominent(adj. 突出的，显著的；杰出的；卓越的) Microsoft employee.Indexing is a key issue when we are talking about databases and general performance problems, and so it's time to feature the Non-Clustered Index Spool. It's important that you read the earlier chapter on Lazy Spool before you get too deeply into this chapter.
The Index Spool is used to improve the read performance of a table which is not indexed and, as with other types of Spool operators, it can be used in a "Lazy" or an "Eager" manner. So, when SQL Server needs to read a table that is not indexed, it can choose to create a "temporary index" using the Spool, which can result in a huge performance improvement in your queries. To get started with understanding Index Spool, we'll use the usual table, called Pedido ("Order" in Portuguese). The following script will create a table and populate it with some garbage data:
USE tempdb GO IF OBJECT_ID('Pedido') IS NOT NULL DROP TABLE Pedido GO CREATE TABLE Pedido ( ID INT IDENTITY(1, 1) , Cliente INT NOT NULL , Vendedor VARCHAR(30) NOT NULL , Quantidade SMALLINT NOT NULL , Valor NUMERIC(18, 2) NOT NULL , Data DATETIME NOT NULL ) GO CREATE CLUSTERED INDEX ix ON Pedido(ID) GO
DECLARE @I SMALLINT SET @I = 0 WHILE @I < 50 BEGIN INSERT INTO Pedido ( Cliente , Vendedor , Quantidade , Valor , Data ) SELECT ABS(CHECKSUM(NEWID()) / 100000000) , 'Fabiano' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , GETDATE() - ( CHECKSUM(NEWID()) / 1000000 ) INSERT INTO Pedido ( Cliente , Vendedor , Quantidade , Valor , Data ) SELECT ABS(CHECKSUM(NEWID()) / 100000000) , 'Amorim' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , GETDATE() - ( CHECKSUM(NEWID()) / 1000000 ) INSERT INTO Pedido ( Cliente , Vendedor , Quantidade , Valor , Data ) SELECT ABS(CHECKSUM(NEWID()) / 100000000) , 'Coragem' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , GETDATE() - ( CHECKSUM(NEWID()) / 1000000 ) SET @I = @I + 1 END SET @I = 0 WHILE @I < 2 BEGIN INSERT INTO Pedido ( Cliente , Vendedor , Quantidade , Valor , Data ) SELECT Cliente , Vendedor , Quantidade , Valor , Data FROM Pedido SET @I = @I + 1 END GO SELECT * FROM Pedido Ped1 WHERE Ped1.Valor > ( SELECT AVG(Ped2.Valor) FROM Pedido AS Ped2 WHERE Ped2.Data < Ped1.Data )
Below is what the data looks like in SSMS:
To help us understand the Index Spool, I've written a query that returns all orders with a sale value higher than the average, as compared to all sales before the date of the order in question:
SELECT * FROM Pedido Ped1 WHERE Ped1.Valor > ( SELECT AVG(Ped2.Valor) FROM Pedido AS Ped2 WHERE Ped2.Data < Ped1.Data)
Before we see the execution plan, let's make sure we understand the query a little better.
The SubQuery returns the average value of all sales (AVG(Ped2.Valor)) dated before the order we're comparing them to. After that, the average is compared with the principal query, which determines whether the sale value in question is actually bigger than the average. You'll notice that this query has a very similar form to the example in the previous chapter. So, now we have got the following execution plan:
|--Filter(WHERE:([Pedido].[Valor] as [Ped1].[Valor]>[Expr1004])) |--Nested Loops(Inner Join, OUTER REFERENCES:([Ped1].[Data])) |--Clustered Index Scan(OBJECT:([Pedido].[PK_Pedido] AS [Ped1])) |--Index Spool(SEEK:([Ped1].[Data]=[Pedido].[Data] as [Ped1].[Data])) |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012]/CONVERT_IMPLICIT(numeric(19,0),[Expr1011],0) END)) |--Stream Aggregate(DEFINE:([Expr1011]=Count(*),[Expr1012]=SUM([Pedido].[Valor] as [Ped2].[Valor]))) |--Index Spool(SEEK:([Ped2].[Data] < [Pedido].[Data] as [Ped1].[Data])) |--Clustered Index Scan(OBJECT:([Pedido].[PK_Pedido] AS [Ped2]))
This is a very interesting plan, and, as we can see, the Optimizer chose to use two Index Spool operators; one working as an Eager and the other as a Lazy spool. We can also see that, after the Nested Loops, SQL Server uses the Filter operator to select just the rows that satisfy the WHERE condition (WHERE PEd1.Valor > …).
This execution plan is actually simpler than it looks; frst, the Clustered Index Scan reads the rows in the Pedido table, returning the Data and Valor (Date and Value) columns to the Eager Index Spool. With these rows, the Optimizer uses the Index Spool to create a temporary non-clustered index on Data and Valor, and as it is an Eager spool, it will read all the rows from the clustered scan to create the index.
........................................................................................................................................................................................................
Quick Tip
If you create an index on the Pedido table covering Data and Valor, you will optimize the query because
the operator Index Spool (Eager) will not be necessary.
........................................................................................................................................................................................................
After that, the optimizer will calculate the average value of the sales, using the following rule: for each row of Ped1, the optimizer computes the average of any orders where the Ped2.Data is lower than Ped1.Data (i.e. the average of any orders which have a date earlier than the order in the given row of Ped1). To do this, SQL Server uses the Stream Aggregate and the Compute Scalar operators, in a manner similar to that discussed in the previous chapter.
I'll explain the Index Spool (Lazy) in just a moment, but for now I'll just say that it optimizes the Nested Loops join, which is joining the average calculated by the sub-query to the Ped1.Data column, and the result, as I mentioned, is then fltered to complete the query.
Now, let's look at what makes the Index Spool (Lazy) operator special. When SQL Server needs to read a value that it knows is repeated many times, then it can use a Spool to avoid having to do the same work each time it needs to fnd that value. For instance, suppose that the date column has a high density (i.e. it contains a lot of duplicated(v. 复制，复写) values); this will mean that SQL Server will have to do the same calculation more than once, since the Nested Loops operator will process the join row by row. However, if the value passed as a condition to the join is equal to a value that has already been calculated,you clearly shouldn't need to recalculate the same result each time. So how can we reuse(重用) the value that has already been found?
This is exactly what the Non-Clustered Index Spool operator (Lazy), is designed to do– optimize the process of the Join. It is optimized to predict(vt. 预报，预言；预知) precisely(adv. 精确地；恰恰) the case that I've described above, so that a value that has already been calculated will not be recalculated,but instead read from the index, which is cached (TempDb). So, from the point of view of the Spool, it is very important to know if the required value still needs to be calculated (rebind(vt. 重捆；重新装订)), or has already been calculated (rewind(vt. 倒回；重绕)). Now that this simple example has illustrated that point, it's time to dive(vi. 潜水；跳水；俯冲；急剧下降) a little deeper.
Understanding rebind and rewind
First of all you need understand that all types of Spool operators use temporary storage to cache the values used in the execution plan, although this temporary storage is truncated for each new read of the operator. This means that, if I use a Lazy Spool to calculate an aggregation and keep this calculated value in cache, I can use this cached data in many parts of the plan, and potentially(adv. 可能地，潜在地) work with just single chunk of data in all the plan's steps. However, to do this, we need reset the cache for each newly calculated value , otherwise by the end of the plan we'll be working with the whole table! Thus, for a spool, it is very important to distinguish(vi. 区别，区分；辨别) between executions need the same value (rewinds) andexecutions needing an different/new value (rebinds).
A rewind is defned as an execution using the same value as the immediately preceding(adj. 在前的；前述的) execution, whereas a rebind is defned as an execution using a different value. I know this is a little confusing to understand for the frst time, so I'll try and explain it step by step,with some code and practical examples.
Rebinds and rewinds with Table Spool (Lazy Spool)
To understand a little more about rebind and rewind, let's suppose our Pedido table has some rows in the Data (Date) column in the following order: "19831203", "19831203","20102206" and "19831203". A representation of rewind and rebind in a table spool operator would be something like this:
Value = "19831203" – A rebind occurs, since is the frst time the operator is called.
Value = "19831203" – A rewind occurs since this value was already read, and is in the spool cache.
Value = "20102206" – The value changes, so the cache is truncated and a rebind occurs,
since is the value "20102206" is not in the cache.
Value = "19831203" – A rebind occurs again, since the actual value in cache is "20100226",and the value that was read in step 1 was truncated in the step 3.So our fnal numbers are three rebinds (steps 1, 3 and 4) and just one rewind (step 2). To
show this in a practice, I've written a script to repopulate the table Pedido with four rows,exactly as I've mentioned above.
USE tempdb GO TRUNCATE TABLE Pedido GO SET IDENTITY_INSERT Pedido ON INSERT INTO Pedido ( ID , Cliente , Vendedor , Quantidade , Valor , Data ) SELECT 1 , ABS(CHECKSUM(NEWID()) / 100000000) , 'Fabiano' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , '19831203' INSERT INTO Pedido ( ID , Cliente , Vendedor , Quantidade , Valor , Data ) SELECT 2 , ABS(CHECKSUM(NEWID()) / 100000000) , 'Fabiano' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , '19831203' INSERT INTO Pedido ( ID , Cliente , Vendedor , Quantidade , Valor , Data ) SELECT 3 , ABS(CHECKSUM(NEWID()) / 100000000) , 'Fabiano' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , '20100622' INSERT INTO Pedido ( ID , Cliente , Vendedor , Quantidade , Valor , Data ) SELECT 4 , ABS(CHECKSUM(NEWID()) / 100000000) , 'Fabiano' , ABS(CHECKSUM(NEWID()) / 10000000) , ABS(CONVERT(NUMERIC(18, 2), ( CHECKSUM(NEWID()) / 1000000.5 ))) , '19831203' SET IDENTITY_INSERT Pedido OFF GO
This is what the data looks like in SSMS:
To illustrate the rebind and rewind using the Table Spool operator (which we already understand), I've written a query using the USE PLAN hint, to force a plan that uses the Table Spool operator. I'll omit(vt. 省略；遗漏；删除；疏忽) part of the code for brevity(n. 简洁，简短；短暂，短促), but you can get ti as follow. Note that the following query only runs in the TempDb database, because the XML plan is using this database.
-- Query using an Index Spool(Eager Spool) and an Table Spool(Lazy Spool) SELECT * FROM Pedido Ped1 WHERE Ped1.Valor > (SELECT AVG(Ped2.Valor) FROM Pedido AS Ped2 WHERE Ped2.Data < Ped1.Data) OPTION (USE PLAN N' <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="300000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="28062.8" StatementText="SELECT *
 FROM Pedido Ped1
 WHERE Ped1.Valor > (SELECT AVG(Ped2.Valor)
 FROM Pedido AS Ped2
 WHERE Ped2.Data < Ped1.Data)
OPTION (MAXDOP 1)" StatementType="SELECT" QueryHash="0x998B7C33E4C4CAF4" QueryPlanHash="0x5B66A7A4331A7718"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="0" MemoryGrant="114232" CachedPlanSize="32" CompileTime="7" CompileCPU="7" CompileMemory="328"> <RelOp AvgRowSize="53" EstimateCPU="0.48" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="300000" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="28062.8"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="44" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Filter StartupExpression="false"> <RelOp AvgRowSize="70" EstimateCPU="4.18" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="28062.3"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> <ColumnReference Column="Expr1006" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OuterReferences> <RelOp AvgRowSize="53" EstimateCPU="91.486" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="166.674"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="53" EstimateCPU="1.10016" EstimateIO="74.0765" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="75.1766" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </DefinedValue> </DefinedValues> <Object Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Index="[ix]" Alias="[Ped1]" IndexKind="Clustered" /> </IndexScan> </RelOp> </Sort> </RelOp> <RelOp AvgRowSize="24" EstimateCPU="0.00010028" EstimateIO="0.01" EstimateRebinds="50044" EstimateRewinds="949955" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="4" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="27891.4"> <OutputList> <ColumnReference Column="Expr1006" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRebinds="50" ActualRewinds="50" ActualRows="100" ActualEndOfScans="0" ActualExecutions="100" /> </RunTimeInformation> <Spool> <RelOp AvgRowSize="24" EstimateCPU="0.180001" EstimateIO="0" EstimateRebinds="50044" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="27290.8"> <OutputList> <ColumnReference Column="Expr1006" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1006" /> <ScalarOperator ScalarString="CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014]/CONVERT_IMPLICIT(numeric(19,0),[Expr1013],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1013" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1014" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="numeric" Precision="19" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1013" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="24" EstimateCPU="0.180001" EstimateIO="0" EstimateRebinds="50044" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="6" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="27290.8"> <OutputList> <ColumnReference Column="Expr1013" /> <ColumnReference Column="Expr1014" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="50" ActualEndOfScans="0" ActualExecutions="50" /> </RunTimeInformation> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1013" /> <ScalarOperator ScalarString="Count(*)"> <Aggregate AggType="countstar" Distinct="false" /> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1014" /> <ScalarOperator ScalarString="SUM([Complete_Showplan_Operators].[dbo].[Pedido].[Valor] as [Ped2].[Valor])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="16" EstimateCPU="1.33026" EstimateIO="27.8172" EstimateRebinds="50044" EstimateRewinds="0" EstimateRows="300000" LogicalOp="Eager Spool" NodeId="7" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="18282.7"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRebinds="50" ActualRewinds="0" ActualRows="2450" ActualEndOfScans="50" ActualExecutions="50" /> </RunTimeInformation> <Spool> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Data" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Complete_Showplan_Operators].[dbo].[Pedido].[Data] as [Ped1].[Data]"> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </Identifier> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> <RelOp AvgRowSize="24" EstimateCPU="1.10016" EstimateIO="74.0765" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="75.1766" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Data" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Data" /> </DefinedValue> </DefinedValues> <Object Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Index="[ix]" Alias="[Ped2]" IndexKind="Clustered" /> </IndexScan> </RelOp> </Spool> </RelOp> </StreamAggregate> </RelOp> </ComputeScalar> </RelOp> </Spool> </RelOp> </NestedLoops> </RelOp> <Predicate> <ScalarOperator ScalarString="[Complete_Showplan_Operators].[dbo].[Pedido].[Valor] as [Ped1].[Valor]>[Expr1006]"> <Compare CompareOp="GT"> <ScalarOperator> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1006" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </Filter> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>')
For the query above, we have the following execution plan:
Note that the plan above is using a Table Spool (Lazy Spool) to perform the query; for each row read in the Pedido (Ped1) table, SQL Server will call the table Spool to run the SubQuery. Let's look at the rebind and rewind properties, to see how many times SQL Server executes each task.
"Hey, wait a minute, Fabiano!" I hear you exclaim. "Didn't you said that we have three rebinds and one rewind? Why SQL is showing different values?"
Sharp-eyed(adj. 目光敏锐的) as ever, dear reader. Pay attention, now; do you notice that I marked the SORT operator with a green square? We should be asking ourselves what this is doing.
As I said earlier, to SQL Server, it is very important to distinguish between executions using the same value and executions using different values.
That means that if SQL Server reads the Pedido table and Sorts the values by Date, it will increase the chance of a rewind occurring, because the order of rows will go from:
• 19831203
• 19831203
• 20102206
• 19831203
to
• 19831203
• 19831203
• 19831203
• 20102206
That's why SQL Server only makes two rebinds and two rewinds. Pretty smart, huh?
Rebinds and rewinds with Index Spool (Lazy Spool)
Now let's see how the Index Spool works. Remember, index spool doesn't truncate its cache, even if a rebind occurs; instead it maintains(vt. 维持；继续；维修；) a temporary index with all "rebound" rows.
So a representation of rewind and rebind in an index spool operator would be something like this:
Value = "19831203" – A rebind occurs, as this is the frst time the operator is called.
Value = "19831203" – A rewind occurs, as this value was already read, and is in the spool cache.
Value = "20102206" – A rebind occurs, as the value "20102206" is not in the cache.
Value = "19831203" – A rewind occurs, as this value was read in step 1, and is still in the temporary index.
So our fnal numbers are two rebinds (steps 1 and 3) and just two rewinds (steps 2 and 4).The same numbers used in the plan above with the table spool operator.To illustrate rebind and rewind using the Index Spool operator, I've written a second
query using the USE PLAN hint to force a plan that uses the Index Spool operator. As before, I'll omit part of the code for the sake(n. 目的；利益；理由；日本米酒) of brevity, but you can can get ti as follow
-- Query using an Index Spool(Eager Spool) and an Index Spool(Lazy Spool) SELECT * FROM Pedido Ped1 WHERE Ped1.Valor > (SELECT AVG(Ped2.Valor) FROM Pedido AS Ped2 WHERE Ped2.Data < Ped1.Data) OPTION (USE PLAN N' <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="180" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.291836" StatementText="SELECT *
 FROM Pedido Ped1
 WHERE Ped1.Valor > (SELECT AVG(Ped2.Valor)
 FROM Pedido AS Ped2
 WHERE Ped2.Data < Ped1.Data)
OPTION (RECOMPILE, MAXDOP 1)" StatementType="SELECT" QueryHash="0x41F6986A953E7775" QueryPlanHash="0xC442491964523B90"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="32" CompileTime="5" CompileCPU="5" CompileMemory="328"> <RelOp AvgRowSize="53" EstimateCPU="0.000288" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="180" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.291836"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OutputList> <Filter StartupExpression="false"> <RelOp AvgRowSize="70" EstimateCPU="0.002508" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="600" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.291548"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> <ColumnReference Column="Expr1006" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OuterReferences> <RelOp AvgRowSize="53" EstimateCPU="0.000817" EstimateIO="0.00534722" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="600" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00616422" TableCardinality="600"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Cliente" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Vendedor" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Quantidade" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </DefinedValue> </DefinedValues> <Object Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Index="[ix]" Alias="[Ped1]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="24" EstimateCPU="0.000258351" EstimateIO="0.003125" EstimateRebinds="596" EstimateRewinds="3" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="3" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="0.282876"> <OutputList> <ColumnReference Column="Expr1006" /> </OutputList> <Spool> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Complete_Showplan_Operators].[dbo].[Pedido].[Data] as [Ped1].[Data]"> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> <RelOp AvgRowSize="24" EstimateCPU="0.0001085" EstimateIO="0" EstimateRebinds="150" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.124741"> <OutputList> <ColumnReference Column="Expr1006" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1006" /> <ScalarOperator ScalarString="CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014]/CONVERT_IMPLICIT(numeric(19,0),[Expr1013],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1013" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1014" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="numeric" Precision="19" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1013" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="24" EstimateCPU="0.0001085" EstimateIO="0" EstimateRebinds="150" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="5" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.124741"> <OutputList> <ColumnReference Column="Expr1013" /> <ColumnReference Column="Expr1014" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1013" /> <ScalarOperator ScalarString="Count(*)"> <Aggregate AggType="countstar" Distinct="false" /> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1014" /> <ScalarOperator ScalarString="SUM([Complete_Showplan_Operators].[dbo].[Pedido].[Valor] as [Ped2].[Valor])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="16" EstimateCPU="0.001055" EstimateIO="0.0266385" EstimateRebinds="150" EstimateRewinds="0" EstimateRows="180" LogicalOp="Eager Spool" NodeId="6" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="0.108358"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> </OutputList> <Spool> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Data" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Complete_Showplan_Operators].[dbo].[Pedido].[Data] as [Ped1].[Data]"> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Data" /> </Identifier> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> <RelOp AvgRowSize="24" EstimateCPU="0.000817" EstimateIO="0.00534722" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="600" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00616422" TableCardinality="600"> <OutputList> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Data" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Valor" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped2]" Column="Data" /> </DefinedValue> </DefinedValues> <Object Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Index="[ix]" Alias="[Ped2]" IndexKind="Clustered" /> </IndexScan> </RelOp> </Spool> </RelOp> </StreamAggregate> </RelOp> </ComputeScalar> </RelOp> </Spool> </RelOp> </NestedLoops> </RelOp> <Predicate> <ScalarOperator ScalarString="[Complete_Showplan_Operators].[dbo].[Pedido].[Valor] as [Ped1].[Valor]>[Expr1006]"> <Compare CompareOp="GT"> <ScalarOperator> <Identifier> <ColumnReference Database="[Complete_Showplan_Operators]" Schema="[dbo]" Table="[Pedido]" Alias="[Ped1]" Column="Valor" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1006" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </Filter> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>')
To query above we have the following execution plan:
Note that, as intended(adj. 故意的，有意的；打算中的), the plan above is using an Index Spool (Lazy Spool) to perform the query. For each row read in the Pedido (Ped1) table, SQL Server will call the Index Spool to run the SubQuery. Let's look at the Spool's rebind and rewind properties to see how many times SQL Server executes each task.
Maybe you are wondering, "Once again, Fabiano – your prediction is different from the real values. Could you explain that please? Why is the operator's properties showing three rebinds and one rewind, when you expected two rebinds and two rewinds?"
Well, this time it's because our Microsoft friends like to confuse us. That's right, my count is correct, and the displayed properties are actually wrong. To quote from his book, Inside Microsoft SQL Server 2005 Query Tuning and Optimization, this is what Craig Freedman wrote about this situation:
"Note that rewinds and rebinds are counted the same way for index and nonindex spools. As described previously, a reexecution is counted as a rewind only if the correlated(adj. 有相互关系的) parameter(s) remain the same as the immediately prior(adj. 优先的；在先的，在前的) execution, and is counted as a rebind if the correlated parameter(s) change from the prior execution. This is true even for reexecutions, in which the same correlated parameter(s) were encountered(vt. 遭遇，邂逅；遇到) in an earlier, though not the immediately prior, execution. However, since lazy index spools, like the one in this example, retain(vt. 保持；记住) results for all prior executions and all previously encountered correlated parameter values, the spool may treat some reported rebinds as rewinds. In other words, by failing to account for(对…负有责任；对…做出解释；说明……的原因；) correlated parameter(s) that were seen prior to the most recent(adj. 最近的；近代的) execution, the query plan statistics may overreport the number of rebinds for an index spool."
Summary
Generally, if you see a spool operator in your plan, you should take a closer look, because it can probably be optimized if you create the indexes properly. Doing this avoids the need for recalculations, prevents the query optimizer from having to create the indexes for you, and your query will perform better.
浙公网安备 33010602011771号