0

I'm looking into optimizing some queries in SQL Server 2016, and I ran into this situation which I couldn't understand.

This particular query is trying to get the name of two sets of Objects in the dbo.Objects table. To do this, the Query Plan shows SQL is using a Key Lookup in a Nested Loop Inner Join after retrieving the clustered indexes. However, doing the Key Lookup for the larger set of Objects has an estimated cost that is less than the Key Lookup for the smaller set of Objects. They're both operating on the same table, using the same clusterd index for the lookup, the same predicates, so I don't see what else could affect their cost estimates besides the row numbers, but that seems to have the opposite result.

The only differences I could find in the properties were the estimated/actual executions, and the estimated rebinds/rewinds. The smaller set had estimated 5854 rebinds and no rewinds, while the larger set estimated 1069.66 rebinds and 22356.9 rewinds. However, I don't know what those mean in the context of Key Lookups

Here are the tooltips for each operation to show what I'm talking about.

Tooltip for Larger Set

Tooltip for Smaller Set

Why might these operators have such different Estimated Operator Counts?

M. McCrary
  • 35
  • 3
  • It looks like your stats are severely out of whack .... you have "Estimated number of rows" = 1 in both cases, but actual number of rows is 2418 or 9672 rows in the two cases - this is an indication of a really really bad statistics situation - you should try to update your stats right away! – marc_s Jan 02 '20 at 19:12
  • 1
    The `Estimated Number of Rows` on a Key Lookup is _per lookup_. The `Estimated number of Executions` is how many times the lookup will be performed. See eg: https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-whats-a-key-lookup/ – David Browne - Microsoft Jan 02 '20 at 19:17
  • rebinds&rewinds are related to the nested loops operator. 5854 rebinds and no rewinds == the query/optimizer expects 5854 unique clustered (outer) values and corresponding number of lookups.. 1069.66 rebinds and 22356.9 rewinds == 1069 unique clustered values and 22356 (rewinds) are "duplicate" clustered values (the same clustered value is "looked-up" multiple times). Is there a difference for the nested loops operator in the two plans? Just a shot in the dark, check if either of the two is an optimized nested loops while the other isn't. – lptr Jan 03 '20 at 07:43

0 Answers0