A Bug With OBJECTPROPERTYEX And Column Store Indexes

Posted on by Erik Darling

No, Not That Bug

There’s a known bug with partitioned tables, but this is different. This one is with rows in the delta store.

Here’s a quick repro:

USE tempdb;

DROP TABLE IF EXISTS dbo.busted;

CREATE TABLE dbo.busted ( id BIGINT, INDEX c CLUSTERED COLUMNSTORE );

INSERT INTO dbo.busted WITH ( TABLOCK )
SELECT     TOP ( 50000 )
           1
FROM       master..spt_values AS t1
CROSS JOIN master..spt_values AS t2
OPTION ( MAXDOP 1 );

-- reports 0, should be 50k
SELECT CAST(OBJECTPROPERTYEX(OBJECT_ID('dbo.busted'), N'Cardinality') AS BIGINT) AS [where_am_i?];
SELECT COUNT_BIG(*) AS records
FROM   dbo.busted;

INSERT INTO dbo.busted WITH ( TABLOCK )
SELECT     TOP ( 150000 )
           1
FROM       master..spt_values AS t1
CROSS JOIN master..spt_values AS t2
OPTION ( MAXDOP 1 );

-- reports 150k, should be 200k
SELECT CAST(OBJECTPROPERTYEX(OBJECT_ID('dbo.busted'), N'Cardinality') AS BIGINT) AS [where_am_i?];
SELECT COUNT_BIG(*) AS records
FROM   dbo.busted;

SELECT object_NAME(csrg.object_id) AS table_name, *
FROM sys.column_store_row_groups AS csrg
ORDER BY csrg.total_rows;

In Pictures

2019 09 05 17 26 10
In Particular

This is the interesting bit, because you can obviously see the difference between open and compressed row groups.

SQL Server Query Results
Give us money

The 50k rows in the delta store aren’t counted towards table cardinality.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts