Thanks all for your replies.

I just tested the "inner hash join" (Jonathan), but the table/index scan is still used.

@steve-2: I am using database scoped configuration for LEGACY_CARDINALITY_ESTIMATION

This will...

CREATE TABLE [dbo].[AttribINT](
[OID] [bigint] NOT NULL,
[AID] [int] NOT NULL,
[Category] [tinyint] NOT NULL,
[AttrFlag] [smallint] NOT NULL,
[Value] [int] NULL,
[AttrStatusFlag] [tinyint] NOT NULL,
CONSTRAINT [PK__IAttribINT] PRIMARY KEY CLUSTERED
(
[OID] ASC,
[AID] ASC
)WITH (PAD_INDEX...

• This reply was modified 3 years, 10 months ago by  Steve Jones - SSC Editor.

... some more info:

- switching back 'Maximum server memory' to old value will NOT change query plan back to old version - the better version with 'Index Seek' is still...

Hi, I am working on a similar problem. A select statement as part of a SP with some joins performed very bad because it used a 'Clustered Index Scan' where...