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
Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 29 2021 13:52:20 Copyright (C) 2019 Microsoft Corporation