SQL Server execution plan – Simplifying : Part 11

Hi Friends,

While checking SQL Server execution plans over last few weeks on this series; you would have seen these terms quite frequently;

• Actual Rebinds / Estimated Rebinds
• Actual Rewind/ Estimated Rewinds

Most of the time in this series, the value for both the rebinds and rewinds has been zero. In order to understand what these values mean, we need some background. Whenever a physical operator, such as the SORT operator in an execution plan occurs, three things happen;

1. Init() method is invoked i.e. physical operator is initialized and structures are setup. This happens for all cases once for an operator though possible to happen many times.
2. GetNext() method is called i.e. the physical operator receives the rows of data to act on. It may receive none depending on type of operator or many GetNext() calls.
3. Third, once the operator is done performing its functions, it needs to clean itself and shut down which we call Close() method. Any physical operator will only ever receive a single Close() call.

Now, a rebind or rewind is a count of the number of times the Init() method is called by any operator but they can do so under different scenarios. When one or more of the correlated parameters of a join change and the inner side must be reevaluated a rebind count occurs and when none of the correlated parameters change and the prior inner result set may be reused a rewind count occurs. Every-time either of these circumstances occurs, a rebind or rewind occurs, and increases their count.

As per above explanation, we would have seen value of one or may be more for the rebind and rewind in each ToolTips, properties screen for every physical operator. In reality we don’t, fact is that the rebind and rewind count values are only populated when particular physical operators occur and are not populated when other physical operators occur. From MSDN;

‘They are only populated for the Nonclustered Index Spool, Remote Query, Row Count Spool, Sort, Table Spool, and Table-valued Function operators. ActualRebinds and ActualRewinds may also be populated for the Assert and Filter operators when the StartupExpression attribute is set to TRUE.’

For all other physical operators they aren’t populated however this doesn’t mean that zero rebinds or rewinds occurred only that these values were not populated. This also explains why most of the time you see zero values for rebind and rewind.

If you happen to see an operator where rebind equals one and rewinds equals zero, this means that an Init() method was called one time on a physical operator that is NOT on the inner side of  loop join. If the physical operator is ON the inner side of a loop join used by an operator then the sum of the rebinds and rewinds will equal the number of rows process on the outer side of a join used by the operator.

Ideally rebind and rewind counts should be as low as possible, as higher counts indicate more pressure on I/O. If the counts are high it might indicate that a particular operator is working harder than it needs to hurt server performance. If this is the case it might be possible to rewrite the query or modify current indexing to use a different query plan that uses fewer rebinds and rewinds, reducing I/O and boosting performance.

With this I’ll conclude Simplifying Execution plans series. I hope this helped you to understand the basics on various execution plan operators. Thanks again for following this series with me.

Regards

Kanchan Bhattacharyya

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook