Rebind and Rewind in execution plans

http://www.scarydba.com/2011/06/15/rebind-and-rewind-in-execution-plans/

Ever looked at an execution plan? If you answered no, you can ' t possibly has ever tried to tune a query, or, you ' re doing it wrong. For every one else, no doubt you ' ve looked at the tool tips or the property sheets of a operator and you ' ve seen the Rebi nd & Rewind Properties and wondered what the heck they mean. Me too.

I learned as much as I could for the book on execution plans and I spent II pages describing it. Then, a little while ago, on the SQL Cruise, someone asked me to describe them and I was flummoxed. Specifically they said the explanation in the book is insufficiently clear, so I promised-put together a blog post on The topic, both to attempt to clarify my explanation and to reacquaint myself with the topic.

These properties reflect the number of times an operator is initialized, or started. It's that's easy for the initial definition. But it gets complicated quickly. Rebind & Rewind is only applicable when the dealing with a loop join. They don ' t apply to other situations, which are why they's re mostly zero when you see them. They is only applicable to the inner side of the loop.

Not only is Rebind and Rewind only involved with loop joins, but they only apply to certain operators:nonclustered Index Spool, Remote Query, Row Count Spool, Sort, Table Spool, and table-valued Function.

The difference between a rebind and rewind have to does with the values that is part of the loop join itself. As those values change, as different data are accessed in the outer part of the loop, you see rebinds. When those values aren ' t changed then you'll see a rewind. You'll also see that the aggregation of these values are equal to the number of rows in the outer join.

Here's an example query:

[SourceCode language= "SQL"] SELECT Sod. Salesorderdetailid from Sales.SalesOrderDetail as sod WHERE LineTotal < (SELECT AVG (Dos). LineTotal) from Sales.SalesOrderDetail as DOS WHERE dos. ModifiedDate < Sod. ModifiedDate) [/sourcecode] It puts out an execution plan that's pretty ugly, but at least it shoes a table spool in a loo P Join as can see:

If you are in the properties then we can see the values showing multiple rebinds and rewinds the number of rows in the outer part of the loop.

Rebind and Rewind in execution plans