Ever looked at an execution plan? If you answered no, you can’t possibly have 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 an operator and you’ve seen the Rebind & 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 two 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 was insufficiently clear, so I promised to 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 was initialized, or started. It’s that easy for the initial definition. But it gets complicated quickly. Rebind & Rewind are only applicable when dealing with a loop join. They don’t apply to other situations, which is why they’re mostly zero when you see them. They are only applicable to the inner side of the loop.
Not only are 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 has to do with the values that are part of the loop join itself. As those values change, as different data is 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 is equal to the number of rows in the outer join.
Here’s an example query:
SELECTÂ sod.SalesOrderDetailID FROMÂ Â Â Sales.SalesOrderDetail AS sod WHEREÂ Â LineTotal < (SELECT AVG(dos.LineTotal) FROMÂ Â Sales.SalesOrderDetail AS dos WHEREÂ dos.ModifiedDate < sod.ModifiedDate)
It puts out an execution plan that’s pretty ugly, but at least it shoes a table spool in a loop join as you can see:
If you look at the properties then we can see the values showing multiple rebinds and rewinds that total up to the number of rows in the outer part of the loop.
That’s it in a nutshell. Hopefully I was able to clarify some from the original book. The book is actively undergoing an update and I’ll try to clarify that section further. For more on this topic, I recommend you look at this article by Fabiano Amorim (blog|twitter).
Oh. That is a better explanation than the Tech-Ed style explanation in your book. So we can sort of get a feel for an indexes selectivity by comparing the differences between Rebinds and Rewinds (or we can interrogate the index itself).
I’m still unclear about what to do about RB and RW’s. Why do they impact my life, except as a row count indicator and possibly indicating how many rows have unique data values.
Well, it shows how well the loop is working for you. If I understand it correctly (always an issue), you should generally see few rebinds & more rewinds for a data set that is appropriately using loops. I’ll have to experiment with that some more (in my copious spare time).
Since RB/RW only comes in with spooled conditions – as you stated – that means these (worker)objects are being materialized in tempdb.
IMO this may actually be one important conclusion to add to the clarification.
Thanks again for the elaboration.
[…] Rebind and Rewind in Execution Plans – I’m sure you’ve seen these mentioned in your execution plans but do you know what they actually mean? Grant Fritchey (Blog|Twitter) explains. […]
Hey grant, tks for the indication… Just a note my twitter is @mcflyamorim not @FabianoAmorim2 🙂
Rebind/Rewinds are a mess on tolltips… The important thing about it is, you should have more rewinds than rebinds, yes you were correct, but in case of an Index Spool, this works diferent.
Index Spools keeps the cached (rebinded) value in his cache, so when a diferent value from previews one is searched, the rewind occurs, but tooltips shows a rebind… That’s confuse I know… but I think you have more details on my article…
Another important thing is, sometimes you can optimize the number of rewinds by ordering the resultset, this will sort the values sequential, making more rewinds instead of rebinds…
Regards
Thank you for the clarification. Why do you think a Sort operator would usually show
Actual Rebinds = 1
I doubt it is important, but I’m curious nonetheless. Should it be attributed to the “messy” (quoting mr. Amorim) implementation of the counters?
You’re going to see counts of 1 for operators as part of initialization. It’s after that when things get tricky. And yeah, Fabiano has it nailed better than I do.
[…] For 4 part name, we have rebinds.  For best explanation of what rebinds are, please “Check out” Scary DBA’s Blog posting – Rebind and Rewind in Execution Plans (https://www.scarydba.com/2011/06/15/rebind-and-rewind-in-execution-plans/“😉 […]
I realize this is an old post but it is a confusing topic :).
Adam Machanic posted a great YouTube video about Query Plan analysis and at minute 38:00 he has a great explanation and demonstration of Rebind, Rewind. The entire video is great but even if it were not the five minutes about this topic would be worth the time.
https://www.youtube.com/watch?v=GSZPvF2u6WY