1

We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns.

The select is very simple and should lead to a trivial plan that finds either a step or range hit in the histogram. It worked wonderfully with the old cardinality estimator, but with the new estimator we get the EQ_ROWS value of the last step instead of the AVG_RANGE_ROW value of the next step when we hit a range. Interestingly, it works correctly in areas of the historgam where sequential GUIDs are present.

Histogram enter image description here

Following works as expected:

/*STEP MATCH, Estimates 2 Rows (Histogram EQ_ROWS)*/
SELECT count(*) FROM DOR WHERE RefIdGUID IN('3CA17739-7B51-EA11-810F-00155D642227')  
OPTION(RECOMPILE);

/*RANGE MATCH, Estimates 1.6666 Rows (Histogram AVG_RANGE_ROWS of the following Step)*/
SELECT count(*) FROM DOR WHERE RefIdGUID IN('3CA1773A-7B51-EA11-810F-00155D642227')  
OPTION (RECOMPILE);

And here it goes creepy:

/* STEP MATCH, Estimates 11 Rows (Histogram EQ_ROWS) */
SELECT count(*) FROM DOR WHERE RefIdGUID IN('8BB7B421-BE5F-428C-8A72-03E6B98AA711')   
OPTION(RECOMPILE);

/*RANGE MATCH, Estimates 11 Rows!!! But why? 
There is no exactly fitting step in the histogram and the AVG_RANGE_ROWS value of the next step shows 1.1 lines and not 11 */         
SELECT count(*) FROM DOR WHERE RefIdGUID IN('8BB7B422-BE5F-428C-8A72-03E6B98AA711')   
OPTION(RECOMPILE);

/* CE-Model 70, Estimates 1.1 Rows (Histogram AVG_RANGE_ROWS of the following Step) */
SELECT count(*) FROM DOR WHERE RefIdGUID IN('8BB7B422-BE5F-428C-8A72-03E6B98AA711')    
OPTION(RECOMPILE,USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

Do you have an idea why this is, or do you think this is a bug? (Tested on SQL Server 2016 SP2 and SQL Server 2019 => same result)


STEPS FOR REPRODUCTION

Test Setup Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 17763: )

-- set up table
IF OBJECT_ID ('dbo.MyTestTable', 'U') IS NOT NULL  
   DROP TABLE MyTestTable;  
GO 

CREATE TABLE MyTestTable (
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    RefIdGUID UNIQUEIDENTIFIER
    )
GO

--create index for test
CREATE NONCLUSTERED INDEX IDX_RefIdGuid ON MyTestTable (RefIDGuid) 
GO

-- insert some data
INSERT MyTestTable (RefIdGuid) VALUES (newid());
GO 100

-- insert specific data (50 times the same GUID)
DECLARE @MyGUID_A uniqueidentifier = 
(SELECT RefIdGUID
FROM MyTestTable
ORDER BY RefIdGUID
OFFSET (50) ROWS FETCH NEXT (1) ROWS ONLY)

INSERT INTO MyTestTable SELECT @MyGUID_A
GO 50

--update statistics
UPDATE STATISTICS MyTestTable WITH FULLSCAN

Testrun

-- creates a non-existent guid that follows the one with 51 entries
DECLARE @MyGUID_A uniqueidentifier = 
(SELECT RefIdGUID
FROM MyTestTable
GROUP BY RefIdGUID HAVING COUNT(*) > 50)
DECLARE @MyGUID_UNDER_TEST uniqueidentifier = (SELECT CAST('FFFFFFFF' + RIGHT(CAST(@MyGUID_A as varchar(max)), 28) as uniqueidentifier))

-- SELECT on non-existing GUID
-- New CE estimate: 51 records
SELECT * FROM MyTestTable WHERE RefIdGUID = @MyGUID_UNDER_TEST 
OPTION (RECOMPILE)

-- Old CE estimate: 1 record
SELECT * FROM MyTestTable WHERE RefIdGUID = @MyGUID_UNDER_TEST 
OPTION (RECOMPILE, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))     

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.