Azure SQL: problemas de desempenho do hierarchyid

(Desculpas pela postagem longa - primeira postagem no SE)

Tenho usado o tipo hierarchyid em vários aplicativos por cerca de 10 anos e, no geral, estou satisfeito com o desempenho. Ocasionalmente, me deparei com relatos de desempenho ruim com hierarchyid e sempre presumi que era devido a índices ou consultas configurados incorretamente que poderiam ser otimizados. Mas agora eu mesmo me deparei com o problema.

Azure SQL, nível de compatibilidade de banco de dados definido como 160 (SQL Server 2022).

Tenho a seguinte tabela:

create table [Dim]
(
    [Key] [int] not null,
    [Path] [hierarchyid] null,
    [Name] [nvarchar](256) null,
    [ParentKey] [int] null,
    [SortOrder] [int] null,
    [IsLeaf] [bit] null,
    constraint PK_Dim primary key clustered ([Key]),
    index IX_Dim_Name unique ([Name]) include ([Path]),
    index IX_Dim_ParentKey unique ([ParentKey], [SortOrder]),
    index IX_Dim_Path ([Path]),
    index IX_Dim_Leaf ([IsLeaf], [Path])
)

A tabela é projetada para modelar uma hierarquia pai-filho. O campo Path reflete a hierarquia, com base no Path pai e no SortOrder do membro atual. Há 10.010 registros no conjunto de dados de teste com uma profundidade de hierarquia máxima de 8. Executamos a seguinte consulta:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].IsDescendantOf(r.[Path]) = 1
        and d.[IsLeaf] = 1);

A consulta leva 14 segundos para ser executada e retorna 3.053 linhas. A subconsulta retorna 1.298 linhas. Abaixo está o plano de execução: e um link: https://www.brentozar.com/pastetheplan/?id=SydK3qIk1l

que parece muito com o que eu esperava, com exceção da varredura de índice em IX_Dim_Leaf, que lê 6.861.228 linhas.

Agora, se adicionarmos uma coluna adicional à tabela que contém a representação de string do campo Caminho e os dois índices correspondentes no novo campo:

alter table [Dim] add [PathStr] varchar(256);
go
update [Dim] set [PathStr] = [Path].ToString();
create index [IX_Dim_PathStr] on [Dim] ([PathStr]);
create index [IX_Dim_LeafStr] on [Dim] ([IsLeaf], [PathStr]);
go

e então reescreva a consulta para usar PathStr em vez de Path:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[PathStr]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[PathStr]
    from [Dim] d
    inner join [Dim] p on (d.[PathStr] like p.[PathStr] + '%')
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[PathStr] like r.[PathStr] + '%'
    and d.[IsLeaf] = 1);

A nova consulta é executada em 0,064 segundos. O plano de execução está abaixo: e o link: https://www.brentozar.com/pastetheplan/?id=Sy5oT5Uyye

Na verdade, é mais complexo que o primeiro plano de consulta, pois o novo campo não está incluído no índice IX_Dim_Name e é preciso adicionar a string '%', mas a grande diferença está na varredura do índice externo, onde apenas 3.053 linhas são lidas em vez de 6,8 milhões.

Não faz sentido para mim que um campo de string tenha um desempenho melhor do que um campo hierarchyid que teoricamente é otimizado para esse tipo de consulta hierárquica. Estou fazendo algo errado ou o SQL Server é simplesmente incapaz de lidar com hierarchyids em subconsultas e devemos continuar com um campo de string?

Observação: armazenar os resultados da subconsulta em uma tabela var e, em seguida, unir a tabela var com a tabela Dim tem um desempenho marginalmente melhor ao usar hierarchyid, mas infelizmente isso não é uma opção.

Edição: seguindo a sugestão do Charlieface abaixo, também tentei esta consulta:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].GetAncestor(1) = r.[Path]
        and d.[IsLeaf] = 1);

que executa em 38 ms ( execução do plano ). Parece que apenas IsDescendantOf() é um problema.