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,
Leila
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
|||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.googlegr oups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>
> 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:
> 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:
> 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