3

I have the following tables(See below for complete reproducible SQL):

ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...)
CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...)

I have the following query:

SELECT *
FROM
ToBeTransferredTemp a 
INNER JOIN codes c on a.type = c.idstring

There is an index on ToBeTransferredTemp.type and unique index on CODES.idstring. There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

You would think SQL Server would gather from this, that the join will produce the same number of rows as is present in ToBeTransferredTemp. But it doesn't. There are 8500 rows in ToBeTransferredTemp, and the estimated number of rows after the join is 450, and "actual number of rows" is, not surprisingly, 8500 rows.

Any idea why SQL Server misses so terribly? I've checked that the foreign key constraint is trusted by SQL Server.

Could it be that SQL Server 2017 don't take foreign key and unique constraints into account when estimating cardinality of joins?

I know I could fix this easily by just doing a LEFT OUTER JOIN, but it feels so clumsy. I'm really interested to know why SQL Server can't figure this one out.

EDIT: queryplan: https://www.brentozar.com/pastetheplan/?id=ByL54PtNB

EDIT: SQL needed to reproduce. Note that you might need to re-run the query a few times. Sometimes the values form statistics that make it seem to work

CREATE TABLE dbo.TEST_CODES
(
    key1 varchar(60) NOT NULL,
    key2 varchar(60) NOT NULL,
    key3 datetime NOT NULL,
    Filler4 varchar(100),
    IDSTRING varchar(100)
);
ALTER TABLE [dbo].[TEST_CODES] ADD  CONSTRAINT [PK_CODES_TEST] PRIMARY KEY CLUSTERED 
(
    key1 ASC,
    key2 ASC,
    key3 ASC
)

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.TEST_Codes (IDSTRING);

CREATE TABLE dbo.TEST_ToBeTransferredTemp 
(
    ID varchar(100) NOT NULL,
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp_TEST PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type]) 
        REFERENCES dbo.TEST_CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.TEST_ToBeTransferredTemp ([type]);

INSERT INTO dbo.TEST_CODES
SELECT TOP (18139) 
    CAST(NEWID() AS varchar(60)),
    CAST(NEWID() AS varchar(60)),
    GETDATE(),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100))
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.TEST_ToBeTransferredTemp
SELECT TOP (8002)
    CAST(NEWID() AS varchar(100)),
    (SELECT TOP 1 IDSTRING FROM TEST_CODES order by newid()),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
     FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

SELECT *
FROM
TEST_ToBeTransferredTemp a 
inner join TEST_codes c on a.type = c.idstring
  • The real query is larger and gets slowed down due to the above CE failure. – Henrik Alstad Aug 20 '19 at 12:19
  • 1
    Define IDSTRING in CODES be NOT NULL as type in ToBeTransferredTemp is. – Denis Rubashkin Aug 20 '19 at 13:11
  • The problem stays exactly the same, I just removed everything not affecting the cardinality estimate to produce a easily debugged query plan. @DenisRubashkin I will test your suggestion tomorrow! It seems a bit unnecessary though? If ToBeTransferredTemp.type is defined NOT NULL and a foreign key relationship is defined to CODES.IDSTRING, then it logically follows that all values in ToBeTransferredTemp.type will be present. But you suspect for some reason SQL Server don't reason on this information alone? – Henrik Alstad Aug 20 '19 at 15:27
  • It is not exactly the same. IDSTRING is not the PK and more importantly it is nullable. – ypercubeᵀᴹ Aug 21 '19 at 6:49
  • @ypercubeᵀᴹ Agreed – Henrik Alstad Aug 21 '19 at 6:58
2

I can't reproduce your problem, which means there's likely something missing in your description (or I've missed something in your description!). If you want more detailed help with this issue, please suggest changes to this repro code that allow it to more closely match your situation.

Here's an attempt and recreating the tables and data based on your description:

There is an index on ToBeTransferredTemp.type
and unique index on CODES.idstring.
There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

In addition to those three pieces of information, I noticed that there is a clustered index on each of the two tables.

USE [master];
GO
DROP DATABASE IF EXISTS [245776];
CREATE DATABASE [245776];
GO

USE [245776];
GO

CREATE TABLE dbo.CODES
(
    IDSTRING varchar(100),
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_CODES PRIMARY KEY (IDSTRING)
);

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.Codes (IDSTRING);

CREATE TABLE dbo.ToBeTransferredTemp 
(
    ID varchar(100),
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type]) 
        REFERENCES dbo.CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.ToBeTransferredTemp ([type]);

Here are the two tables, with clustered primary keys, foreign key constraint, an NC index, and a unique index.

I have included some filler columns in order for the original key lookup plan shape to be possible.

INSERT INTO dbo.CODES
SELECT TOP (8500) 
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    v1.number
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.ToBeTransferredTemp
SELECT 
    CAST(NEWID() AS varchar(100)),
    c.IDSTRING,
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
FROM dbo.CODES c;

This loads exactly 8500 rows into each table.

The query as written in the OP produces a merge join plan for me:

SELECT *
FROM ToBeTransferredTemp a 
    INNER JOIN codes c
        ON a.[type] = c.idstring;

screenshot of merge join plan in SSMS

In order to get the plan shape from the OP, I have to hint the join type (INNER LOOP JOIN) and hint that the CODES table should be accessed by the unique index.

SELECT *
FROM ToBeTransferredTemp a 
    INNER LOOP JOIN codes c WITH (INDEX (UQ_Codes_IDSTRING)) 
        ON a.[type] = c.idstring;

screenshot of loop join plan in SSMS

This appears to be the right plan shape. However, the estimates are exactly right in this plan.

| improve this answer | |
  • When using the above SQL to reproduce, the query is indeed fixed by doing what Denis Rubashkin suggests in his comment to my answer. When making IDSTRING NOT NULL, the query optimizer correctly identifies and use the foreign key and unique index. So that's a way to fix this particular issue. However it still feels like a bug in SQL Server to me - logically, there is no need for the "NOT NULL" constraint to reason that the join will produce the same number of rows as the number of input rows – Henrik Alstad Aug 21 '19 at 6:55
  • Actually, it turns out I was wrong - adding NOT NULL does not fix the problem. I was just lucky with the random inserted values. – Henrik Alstad Aug 21 '19 at 8:47

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