Physical operators in SQL Server execution plans: what are rebinds, rewinds and number of executions?

Question

I'm trying to understand physical operators in SQL Server execution plans. This page is quite helpful:

http://technet.microsoft.com/en-us/library/ms191158.aspx

SSMS 2008 shows some physical operator properties that are not displayed in SSMS 2005: Estimated Number of Executions and Number of Executions. But what do these actually mean, and how are they related to rebinds and rewinds?

Estimated Number of Executions is particularly interesting because it doesn't seem to be stored in the XML. So how is it calculated? It seems to be equal to Estimated Rebinds + Estimated Rewinds + 1.0. But if that is the case why is Number of Executions not equal to Actual Rebinds + Actual Rewinds + 1?

Thanks.

Answer 1

The book is a little off in that description. Rewind and Rebind are only applicable within a loop join. They refer to the use of the probe values from the outer part of the loop to the inner part of the loop and are only reflected in certain operators (that are costly, so it's worth knowing how often they're getting recalled). Rebinds and Rewinds should correlate directly to the number of executions, not the number + 1.

Number of executions, estimated or actual, is the number of times that the operator is called. This can affected by a lot of things. For example in a loop join, you'll see multiple calls to the operators in the outer branch corresponding directly to the number of rows in the inner branch. You will absolutely see differences between the actual and the estimated. In the case of a loop (great example to beat on) you'll see an estimated value of one in the inner loop, but the actual number of executions will be, as already stated, equal to the number of rows in the outer.

Answer 2

I recommend you reading (and/or downloading) the eBook SQL Server Execution Plans by Grant Fritchey. The download seems to be free.

There's a section about rebinds and rewinds. I'm not an expert on this, so I just cite the bottom line of the corresponding chapter:

So how is this helpful to the DBA? Generally speaking, it is ideal if the rebind and rewind counts are as low as possible, as higher counts indicate more disk I/O. If the counts are high, it might indicate that a particular operator is working harder than it needs to, hurting 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. (p. 83)

The Number of Executions is a higher level indicator for the number of rebinds and rewinds. Here another quote:

Notice that, unlike for the text plans, which just displayed EstimateExecutions, the XML plan the estimated number of rebinds and rewinds. This can often give you a more accurate idea of what occurred within the query, such as how many times the operator was executed. (p. 103)