11

We have a query with a Key Lookup which is estimating thousands of rows per execution. As I understand it, there should only ever be one row per execution. I understand statistics can be misleading but doesn't the optimizer understand that a primary key would be unique?

The table involved in this query has a clustered primary key of this form:

/****** Object:  Index [PK_Table_Name]    Script Date: 6/16/2021 9:52:12 AM ******/
ALTER TABLE [dbo].[Table_Name] ADD  CONSTRAINT [PK_Table_Name] PRIMARY KEY CLUSTERED 
(
    [Table_Name_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

As I understand it, a primary key provides a unique reference to a single row in the table.

So for every row it finds in the non-clustered index, it should be able to use that index's reference to the clustered index to retrieve the single row it needs to satisfy the rest of the query filtering for the row it's acting on. (This is a Nested Loops join operator.)

So why does it estimate almost 4000 rows will be returned as part of the Key Lookup? (Not "for All Executions", which at around 36,000,000 is the product of that 4000 and the 9000 rows it expects from the non-clustered index seek.)

Query Plan Showing Large Rows Per Execution for Key Lookup

The runtime statistics show 2851 rows and 2851 executions for that clustered index seek, which is what I would have expected.

Runtime Statistics showing One Row Per Execution for Clustered Index Seek

In case it helps, this is in Azure SQL Database, with @@version:

Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Apr 29 2021 13:52:20 
    Copyright (C) 2019 Microsoft Corporation
0

1 Answer 1

11

Check out Paul White's blog post Cardinality Estimation Bug with Lookups for the answer to your question:

Estimated row counts on Key or RID Lookups where a filtering predicate is applied can be wrong in SSMS execution plans.

This error does not affect the optimizer’s ultimate plan selection, but it does look odd.

The blog post goes on to say that a fix was planned back in August of 2020 to address this under the new 160 compatibility level:

There is a fix for this planned for the release after SQL Server 2019 (compatibility level 160)

3
  • 1
    Yes, this appears to be the issue. Thanks for the explanation. Amazing that it's been an issue for that long and it's just getting fixed. I suppose since it's only an issue for us humans trying to optimize queries, as long as we're aware, we can work around it in our heads. Commented Jun 16, 2021 at 16:27
  • @RileyMajor I know, right?! I suppose it falls down the priority list for just the reason you stated (it's not a functional issue per se, just one more esoteric thing we have to remember haha). Commented Jun 16, 2021 at 18:20
  • 3
    It's apparently more involved than it appears from the outside. In some ways I will be sad to see the issue fixed. It is a bit like an old friend now...
    – Paul White
    Commented Jun 16, 2021 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.