Clustered column store not aligning GUIDs to segments despite ordering

I have a data set that I want to place in a clustered column store and optimize it for segment elimination when accessing it by a column called SubjectId which is defined as uniqueidentifier type.

I'm using an Azure SQL database with a compatibility level of 16. I have confirmed this same behavior in SQL Server Developer Edition 2022. According to the documentation, this version should support segment elimination based on a uniqueidentifier column.

Following various best practices, I first take my data set and create a rowstore clustered index ordered by that SubjectId:

CREATE CLUSTERED INDEX [MyData_CCI] 
ON [dbo].[MyData_CCS] (SubjectId)
WITH (MAXDOP = 1);

I then create the clustered document store using the DROP EXISTING option:

CREATE CLUSTERED COLUMNSTORE INDEX [MyData_CCI]
ON [dbo].[MyData_CCS]
WITH (DROP_EXISTING = ON, MAXDOP = 1);

The resulting segments don't appear to be aligned whatsoever to this SubjectId:

And when I try and query the data using a single SubjectId in the where clause, the statistics show zero segment elimination:

(44 rows affected)
Table 'MyData_CCS'. Scan count 1, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 2769, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'MyData_CCS'. Segment reads 77, segment skipped 0.

What am I doing wrong here? Isn't SQL Server 2022+ (and therefore SQL Azure?) supposed to support uniqueidentifier for segment elimination and predicate push down? As I said, this also happens with SQL Dev Edition 2022, so I don't think it's an Azure thing.

You can repro this yourself with the following schema/data generation script:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyData]') AND type in (N'U'))
DROP TABLE [dbo].[MyData]
GO
CREATE TABLE [dbo].[MyData](
    [SubjectId] [uniqueidentifier] NOT NULL,
    [SomeDateTime] [datetime] NOT NULL,
    [SomeInteger] [int] NOT NULL,
    [SomeString] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Create a temporary table to store unique SubjectId values
DROP TABLE #SubjectIds
CREATE TABLE #SubjectIds (
    SubjectId UNIQUEIDENTIFIER
);
GO

WITH NumberSeries AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM NumberSeries
    WHERE Number < 42000
)
INSERT INTO #SubjectIds (SubjectId)
SELECT NEWID()
FROM NumberSeries
OPTION (MAXRECURSION 0);

-- Insert 21 million rows using cross join
INSERT INTO MyData (SubjectId, SomeDateTime, SomeInteger, SomeString)
SELECT 
    s.SubjectId,
    DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 31536000), '2020-01-01'), -- Random date within a year
    ABS(CHECKSUM(NEWID()) % 1000000), -- Random integer
    REPLICATE(N'A', 100) -- Example string, adjust as needed
FROM 
    #SubjectIds s
    CROSS JOIN (SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM master..spt_values) AS x;

CREATE CLUSTERED INDEX [MyData_CCI] 
ON [dbo].[MyData] (SubjectId)
WITH (MAXDOP = 1, DROP_EXISTING = ON);
GO

CREATE CLUSTERED COLUMNSTORE INDEX [MyData_CCI]
ON [dbo].[MyData]
WITH (DROP_EXISTING = ON, MAXDOP = 1);

--Show Segment data
select s.Name as SchemaName, 
       t.Name as TableName,
       i.Name as IndexName,
       c.name as ColumnName,
       c.column_id as ColumnId,
       cs.segment_id as SegmentId,
       cs.min_data_id as MinValue,
       cs.max_data_id as MaxValue
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id
  join sys.partitions as p  
    on p.object_id = t.object_id   
  join sys.indexes as I
    on i.object_id = p.object_id
   and i.index_id = p.index_id
  join sys.index_columns as ic
    on ic.[object_id] = I.[object_id]
   and ic.index_id = I.index_id   
  join sys.columns c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
  join sys.column_store_segments cs
    on cs.hobt_id = p.hobt_id
   and cs.column_id = ic.index_column_id 
WHERE t.Name = 'MyData' AND c.Name = 'SubjectId'
ORDER BY cs.segment_id

Update/Resolution

This was resolved via discovering that the min/max id meta data is only for numeric types. For GUIDs, strings, etc. post SQL 2022, you need to look at the min_deep_data and max_deep_data columns. In my case, these were not populated despite having created these column stores brand new on a level 16 compatibility DB in SQL Azure. I do not know why that's the case, but it is.

I was forced to do a REBUILD with MAXDOP 1 on the affected column stores to populate this meta data, after which I saw more appropriate segment elimination. Specifically:

ALTER INDEX [MyData_CCI] ON [dbo].[MyData] REBUILD WITH (MAXDOP = 1);

I dunno, this worked fine for me.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyData]') AND type in (N'U'))
DROP TABLE [dbo].[MyData]
GO
CREATE TABLE [dbo].[MyData](
    [SubjectId] [uniqueidentifier] NOT NULL,
    [SomeDateTime] [datetime] NOT NULL,
    [SomeInteger] [int] NOT NULL,
    [SomeString] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Create a temporary table to store unique SubjectId values
DROP TABLE #SubjectIds
CREATE TABLE #SubjectIds (
    SubjectId UNIQUEIDENTIFIER
);
GO

WITH NumberSeries AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM NumberSeries
    WHERE Number < 42000
)
INSERT INTO #SubjectIds (SubjectId)
SELECT NEWID()
FROM NumberSeries
OPTION (MAXRECURSION 0);

-- Insert 21 million rows using cross join
INSERT INTO MyData (SubjectId, SomeDateTime, SomeInteger, SomeString)
SELECT 
    s.SubjectId,
    DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 31536000), '2020-01-01'), -- Random date within a year
    ABS(CHECKSUM(NEWID()) % 1000000), -- Random integer
    REPLICATE(N'A', 100) -- Example string, adjust as needed
FROM 
    #SubjectIds s
    CROSS JOIN (SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM master..spt_values) AS x;

CREATE CLUSTERED INDEX [MyData_CCI] 
ON [dbo].[MyData] (SubjectId)
WITH (MAXDOP = 1);
GO

CREATE CLUSTERED COLUMNSTORE INDEX [MyData_CCI]
ON [dbo].[MyData]
WITH (DROP_EXISTING = ON, MAXDOP = 1);

set statistics io on

select *
from Mydata
where SubjectId = '15A4815B-DDD3-48BB-84DE-A578663D8D91'


/*
Table 'MyData'. Scan count 4, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 51224, lob physical reads 9, lob page server reads 0, lob read-ahead reads 80541, lob page server read-ahead reads 0.
Table 'MyData'. Segment reads 10, segment skipped 11.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

*/

SELECT @@version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 

Maybe the segment thingy depends on which GUIDs you are selecting?

Edit, had to do a new batch:

select *
from Mydata
where SubjectId = 'B52D5BC0-33B6-44F9-87C3-1CAFD7FDDEA5'

Output:

Table 'MyData'. Scan count 4, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 10751, lob physical reads 0, lob page server reads 0, lob read-ahead reads 10, lob page server read-ahead reads 0.
Table 'MyData'. Segment reads 8, segment skipped 13.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.