SQL Server 2022/Azure SQL聚集列存储GUID列段消除失效，该如何解决？

我有一个数据集，希望存入聚集列存储，并通过uniqueidentifier类型的SubjectId列优化段消除（segment elimination）。使用的是兼容级别16的Azure SQL数据库，同时在SQL Server 2022开发者版中也验证了相同问题。根据文档，该版本应该支持基于uniqueidentifier列的段消除。

按照最佳实践，我先为数据集创建按SubjectId排序的行存储聚集索引：

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

随后使用DROP_EXISTING选项创建聚集列存储索引：

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

但生成的段似乎完全未按SubjectId对齐，且当我在WHERE子句中指定单个SubjectId查询数据时，统计信息显示段消除为0：

(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.

我哪里操作有误？SQL Server 2022+（以及Azure SQL）难道不支持uniqueidentifier列的段消除和谓词下推吗？如上所述，SQL Server 2022开发者版也存在此问题，因此并非Azure特有问题。

复现脚本 #

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

-- 创建临时表存储唯一的SubjectId值
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);

-- 通过交叉连接插入2100万行数据
INSERT INTO MyData (SubjectId, SomeDateTime, SomeInteger, SomeString)
SELECT 
    s.SubjectId,
    DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 31536000), '2020-01-01'), -- 一年内的随机日期
    ABS(CHECKSUM(NEWID()) % 1000000), -- 随机整数
    REPLICATE(N'A', 100) -- 示例字符串，可按需调整
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);

-- 查看段数据
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

更新/解决方案 #

问题已解决：发现min_data_id和max_data_id元数据仅适用于数值类型。SQL Server 2022之后，GUID、字符串等类型需要查看min_deep_data和max_deep_data列。在我的案例中，即使在Azure SQL的兼容级别16数据库中新建列存储，这些列也未填充。

我不得不对受影响的列存储执行MAXDOP=1的REBUILD操作以填充元数据，之后段恢复了正常的段消除效果。具体语句如下：

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

内容的提问来源于stack exchange，提问作者RMD