ActualRebinds & ActualRewinds

Hi,
I have the following query:
use northwind
go
select c.companyname,o.orderdate, od.discount, p.productname from customers
c join orders o
on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on od.productid=p.productid
where c.customerid like 'a%' and o.shipcountry='germany'
order by c.city
I get a sort operator in graphical execution plan that has:
ActualRebinds=1 and
ActualRewinds=0
I read about these two in BOL (Physical Operators) but I couldn't understand
that about my query. What does it show?
Thanks in advance,
LeilaOn May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
> Hi,
> I have the following query:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't understand
> that about my query. What does it show?
> Thanks in advance,
> Leila
This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique,
This is exactly what I read in BOL. I cannot understand this:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178476038.970481.125060@.u30g2000hsc.googlegroups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>> Hi,
>> I have the following query:
>> use northwind
>> go
>> select c.companyname,o.orderdate, od.discount, p.productname from
>> customers
>> c join orders o
>> on c.customerid=o.customerid
>> join [order details] od on o.orderid=od.orderid
>> join products p on od.productid=p.productid
>> where c.customerid like 'a%' and o.shipcountry='germany'
>> order by c.city
>> I get a sort operator in graphical execution plan that has:
>> ActualRebinds=1 and
>> ActualRewinds=0
>> I read about these two in BOL (Physical Operators) but I couldn't
>> understand
>> that about my query. What does it show?
>> Thanks in advance,
>> Leila
>
> This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms191158.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Leila (Leilas@.hotpop.com) writes:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from
> customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't
> understand that about my query. What does it show?
Not much, it seems. Books Online says:
Unless an operator is on the inner side of a loop join, ActualRebinds
equals one and ActualRewinds equals zero.
In your case, you got these two for a Sort operator, so the result is to
be expected.
Interesting enough, I did not get any Sort operator when I ran your
query in my Northwind database on SQL 2005 SP2...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
I guess you have index on customers(city,companyname) that optimizer has
choosen that!
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
That's ok! But I'd like to know the meaning of these two items. For example
I cannot understand this from BOL:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9928EDA6A1317Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> use northwind
>> go
>> select c.companyname,o.orderdate, od.discount, p.productname from
>> customers
>> c join orders o
>> on c.customerid=o.customerid
>> join [order details] od on o.orderid=od.orderid
>> join products p on od.productid=p.productid
>> where c.customerid like 'a%' and o.shipcountry='germany'
>> order by c.city
>> I get a sort operator in graphical execution plan that has:
>> ActualRebinds=1 and
>> ActualRewinds=0
>> I read about these two in BOL (Physical Operators) but I couldn't
>> understand that about my query. What does it show?
> Not much, it seems. Books Online says:
> Unless an operator is on the inner side of a loop join, ActualRebinds
> equals one and ActualRewinds equals zero.
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> That's ok! But I'd like to know the meaning of these two items. For
> example I cannot understand this from BOL: A rebind means that one or
> more of the correlated parameters of the join changed and the inner side
> must be reevaluated. A rewind means that none of the correlated
> parameters changed and the prior inner result set may be reused
> How does the "correlated parameters of the join" can change during the
> execution?
I will have to admit that I'm quite much in the dark myself. It would
help to have a query where Actual Rebinds/Rewinds are non-zero (save for
sorting operations then). I've been trying to find such a query, but
since I don't know what I'm looking for, I have not been successful.
(But I did not spend the entire week looking. The week was busy, and when
I first tried, SQL Server did not want to cooperate at all. A corrupt
database, cause SQL Server to get a stalled scheduler already on
startup, and did not have the time to investigate that for a few
days.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
Exactly my problem!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992FE759593D0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> That's ok! But I'd like to know the meaning of these two items. For
>> example I cannot understand this from BOL: A rebind means that one or
>> more of the correlated parameters of the join changed and the inner side
>> must be reevaluated. A rewind means that none of the correlated
>> parameters changed and the prior inner result set may be reused
>> How does the "correlated parameters of the join" can change during the
>> execution?
> I will have to admit that I'm quite much in the dark myself. It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
> (But I did not spend the entire week looking. The week was busy, and when
> I first tried, SQL Server did not want to cooperate at all. A corrupt
> database, cause SQL Server to get a stalled scheduler already on
> startup, and did not have the time to investigate that for a few
> days.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.se> writes:
>I will have to admit that I'm quite much in the dark myself. It would
>help to have a query where Actual Rebinds/Rewinds are non-zero (save for
>sorting operations then). I've been trying to find such a query, but
>since I don't know what I'm looking for, I have not been successful.
OK, so here is an example where Actual Rewinds and Actual Rebinds are > 0.
I can't say that I understand exactly what is going on, but the
query has a Lazy Spool operator, and if you read about the Lazy Spool
operator in Books Online, you will see that it talks about rewinds and
rebinds. The query plan is in any case a disaster.
To try this, you first need to download the script for the Northgale
database, an inflated version of Northwind from
http://www.sommarskog.se/dynsearch/Northgale.sql.
The script first sets up some stuff, and then issues two query. It is
the first, slow, query which has the rewinds and the rebinds.
-- This is the setup.
SELECT *, GUID = newid() INTO Orders FROM Northgale..Orders
go
CREATE UNIQUE CLUSTERED INDEX clust ON Orders (CustomerID, OrderDate, EmployeeID, GUID)
ALTER TABLE Orders ADD CONSTRAINT pk_orders PRIMARY KEY (OrderID)
go
CREATE TABLE Orderlinks
(prevorderid int NOT NULL REFERENCES Orders(OrderID),
succorderid int NOT NULL REFERENCES Orders(OrderID),
filler char(16) NOT NULL DEFAULT ' ',
PRIMARY KEY (prevorderid, succorderid)
)
go
CREATE INDEX succorderid_ix ON Orderlinks(succorderid)
go
INSERT Orderlinks (prevorderid, succorderid)
SELECT a.OrderID, b.OrderID
FROM (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID ASC)
FROM Orders) AS a
JOIN (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID DESC)
FROM Orders) AS b ON a.rn = b.rn
WHERE a.rn <= 1000
go
UPDATE STATISTICS Orderlinks WITH FULLSCAN
go
DROP TABLE #temp1
CREATE TABLE #temp1
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
CREATE TABLE #temp2
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
go
-- Here starts the actual test.
SET STATISTICS IO ON
go
SELECT getdate()
go
-- Slow query.
INSERT #temp1(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(*)
FROM Orders O
WHERE EXISTS
(SELECT *
FROM Orderlinks L
WHERE O.OrderID IN (L.prevorderid, succorderid))
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
-- Logically the same query, but fast.
INSERT #temp2(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(DISTINCT O.OrderID)
FROM Orders O
JOIN Orderlinks L ON O.OrderID IN (L.prevorderid, succorderid)
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
SET STATISTICS IO OFF
Erland Sommarskog, Stockholm, esquel@.sommarskog.se