Why does a clustered Key Lookup on a primary key have a high estimated rows per execution?

Question

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.)

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

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

Answer 1

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)