Maybe you’re the type of SQL person that digs into execution plans. If so, you might have noticed that when you hover over an operator, that in the popup is an entry for “Actual Rebinds” and “Actual Rewinds” – and that they are usually 0. In this post, we will explore what a rebind / rewind is, when they are used, and how they differ from each other.
There are two types of operators in execution plans – Logical and Physical. The Logical operators describe conceptually what needs to be performed, and the physical operators do it.
There are three actions that a physical operator does:
1. It is initialized. This is performed by calling the Init() method. When the Init() method is called, the physical operator will instantiate itself, and set up any required data structures.
2. It collects data. This is performed by calling the GetNext() method to get the next row of data (or the first row if this is the first time it’s called).
3. It closes. This is performed by calling the Close() method. When called, the method performs object clean-up and shuts the operator down.
If your query has correlated values being passed from one object to another, then every time the value changes, the operator must be reevaluated. When this happens, the Init() method is called. The Rebind property counts the number of times that this reevalaution was necessary (the number of times that the Init() method was called). This will also be the number of unique sets of data in the outer query being passed to the inner query.
A Rewind simply means that the set of correlated values were not changed, so the inner result set can be reused.
Since this only deals with correlated queries, not all operators will report values for Rebind / Rewind. In fact, only a few operators will report values:
Nonclustered Index Spool
Remote Query
Row Count Spool
Sort
Table Spool
Table-valued Function
Additionally, Rebinds / Rewinds will only be present on operators on the inner side of a loop join.
Now that we know what a Rebind / Rewind is, let’s see this in action. For this test, we’ll use the AdventureWorks database.
In the Person.BusinessEntityAddress table, there are a few values for AddressTypeID. These values are defined in the Person.AddressType table.
To see the values, run this query:
|
1 2 3 |
SELECT AddressTypeID, COUNT(*) FROM Person.BusinessEntityAddress BEA GROUP BY BEA.AddressTypeID WITH CUBE; |
Running this query will show that there are three unique values, and the number of rows for each of these values. Additionally, the “WITH CUBE” returns a total row.
|
1 2 3 4 5 6 |
AddressTypeID ------------- ----------- 2 18774 3 805 5 35 NULL 19614 |
Since a Table-valued Function will allow a Rebind / Rewind, here is a short multi-statment table-valued function that will get the Name of the AddressType from the Person.AddressType table:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION Person.GetAddressType(@AddressTypeID INTEGER) RETURNS @Results TABLE (Name NVARCHAR(50)) AS BEGIN INSERT INTO @Results SELECT Name FROM Person.AddressType WHERE AddressTypeID = @AddressTypeID; RETURN END; GO |
Even though an Inline Table-Valued Function would perform much better, it’s work would be inlined into the query plan and the Table-valued Function operator would not be used. A multi-statement table-valued function is needed to show the Table-valued Function operator.
Now let’s turn on the Actual Execution plan, and then run the following query:
|
1 2 3 4 5 6 7 8 9 |
SET STATISTICS IO ON; GO SELECT BEA.AddressTypeID, ca.Name FROM Person.BusinessEntityAddress BEA CROSS APPLY Person.GetAddressType(AddressTypeID) ca ORDER BY BEA.AddressTypeID; GO SET STATISTICS IO OFF; GO |
When you look at the execution plan and hover over the Table-valued Function operator, you will see:
From the first query, we can see that we had three unique AddressTypeID values. Since the query is ordered by AddressTypeID, every time the value changed, a Rebind occurred. However, as long as the AddressTypeID value doesn’t change, other rows will trigger a rewind – to reuse the result set from the function with that value. The sum of the Rebinds and Rewinds is the number of rows applied to the operator from the outer query.
When you look at the IO statistics on the Messages tab, we can see that a table variable had 3 scans, for a total number of logical reads of the same number of rows that were applied to the function.
If the values were not in the AddressTypeID order, then additional rebinds might have to occur. Let’s change the query to:
|
1 2 3 4 |
SELECT BEA.AddressTypeID, ca.Name FROM Person.BusinessEntityAddress BEA CROSS APPLY Person.GetAddressType(AddressTypeID) ca ORDER BY BEA.BusinessEntityID; |
When it is run, it will now show:
The IO statistics also show 75 scans.
Since the AddressTypeID is in a more random order now, every time the value changed, the Table-valued Function operator had to be reinitialized. Obviously, the more rebinds you have, the more work that SQL Server needs to do when re-initializing.
Summary:
Rebind – the number of rows passed from an outer operator that caused a correlated inner operator to be reinitialized due to different values.
Rewind – the number of rows in the correlated inner operator that could be processed by reusing the inner result set.
Reference: Showplan Logical and Physical Operators
Related posts:
How do different types of functions compare performance wise?
