Problema
Estou usando uma caixa SQL Server 2019 Standard Edition. Dada a edição, os níveis de compatibilidade não devem ser relevantes (os recursos do modo batch dependente do nível não são permitidos no Standard). Nesta caixa, tenho experimentado o hack de adicionar um índice columnstore filtrado e não clusterizado vazio a uma tabela temporária antes de juntá-lo a outras tabelas, permitindo assim que as consultas com a referida junção usem o modo em lote, apesar de não usarem nenhum índice columnstore . Quando faço isso, tenho encontrado regularmente o seguinte comportamento nas consultas que anteriormente não usavam o modo em lote:
- As partes dos planos de execução que agora usam o modo em lote são executadas muito mais rapidamente. Em particular, as funções de janela tornam-se incríveis em comparação com o que eram nos planos que não tinham peças em modo lote.
- Os planos com peças em modo batch terão sempre um custo muito menor do que as consultas que não o fazem, mesmo que demorem mais para serem executadas.
- Às vezes, o Gather Streams fica muito mais lento, mesmo que cada operador esteja sendo executado uniformemente em dois threads nos planos que possuem etapas no modo em lote.
- Consultas que anteriormente construíam um bitmap em uma tabela para fazer uma varredura barata e depois fazer hash em outra tabela, passando pela sonda de hash do bitmap na varredura, tornam-se muito mais lentas. Em vez de um bitmap dentro de uma varredura de índice, eles têm um operador de filtro muito caro após a varredura.
Existe alguma explicação para a observação final da minha lista? Mais importante ainda, como posso evitar a introdução do modo em lote, removendo bitmaps excelentes? Esse ponto ousado é o que quero que seja respondido aqui. Se eu pudesse ter os bitmaps e os excelentes benefícios do modo em lote em outro lugar da consulta (por exemplo, nas funções da janela), minhas consultas seriam muito mais rápidas. Aceito que provavelmente poderia indexar melhor as tabelas, mas já estava satisfeito com as junções de hash originais.
Exemplo reproduzível
Erik Darling, com razão, solicitou que eu colasse o plano, mas pensei em fazer melhor e dar um exemplo reproduzível. O seguinte usa o banco de dados StackOverflow2010 . Você pode carregar isso em um contêiner docker, se desejar.
SET STATISTICS XML OFF;
USE StackOverflow2010;
DROP TABLE IF EXISTS #IDs;
SELECT TOP (10000)
Id
INTO
#IDs
FROM
Comments;
ALTER TABLE #IDs ADD CONSTRAINT RowStorePK
PRIMARY KEY CLUSTERED (Id);
SET STATISTICS XML ON;
;WITH [NumberedPosts] AS
(
SELECT
PostID
,ROW_NUMBER() OVER (PARTITION BY PostID ORDER BY Score) RN
FROM
Comments
WHERE
PostID IN (SELECT Id FROM #IDs)
)
SELECT
PostID
FROM
NumberedPosts
WHERE
RN = 1;
/*
Hack to get batch mode for these queries despite
my real machine being on Standard Edition.
*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ColStoreNonClust ON #IDs (ID)
WHERE ID = 1 AND ID = 2;
-- Same query again.
;WITH [NumberedPosts] AS
(
SELECT
PostID
,ROW_NUMBER() OVER (PARTITION BY PostID ORDER BY Score) RN
FROM
Comments
WHERE
PostID IN (SELECT Id FROM #IDs)
)
SELECT
PostID
FROM
NumberedPosts
WHERE
RN = 1;
Este plano é anterior à criação do índice columnstore. Ele usa um bitmap. Este plano é posterior à criação do índice columnstore. Ele não usa um bitmap e é executado muito mais lentamente que a consulta que usa bitmap.
Já vi isso tantas vezes que me convenci de que é um padrão que deve ter alguma explicação geral ou etapas de prevenção.
Resposta curta
Você não pode. Você precisará atualizar da Standard Edition.
Resposta longa
Ambos podem ser chamados de bitmaps, mas os bitmaps de modo de linha são muito diferentes dos bitmaps de modo de lote .
Os bitmaps do modo em lote são criados apenas por hash joins no modo em lote. Eles foram originalmente implementados para uso somente com varreduras de colunas em modo lote.
Quando planos de modo misto (contendo operadores de modo de linha e de lote) foram adicionados, suporte específico foi incluído para avaliar a estrutura de bitmap de modo de lote diferente em um operador de filtro de modo de linha .
Esse apoio é minimamente necessário para fazer as coisas funcionarem. Otimizações como empurrar o filtro para dentro da varredura como um predicado residual ('não sarg'), ou ainda mais profundamente no mecanismo de armazenamento, simplesmente não foram implementadas.
Eles poderiam ter implementado essas otimizações, resultando no tipo de resultado que você está procurando. No caso, eles decidiram investir tempo e esforço na criação do Modo Lote no Rowstore (BMoR).
Conforme brevemente observado na documentação acima (ênfase adicionada), o BMoR permite que o bitmap do modo em lote seja avaliado em uma varredura no modo em lote de um índice rowstore. Ele oferece muitos outros benefícios gerais além disso, é claro.
Infelizmente, o BMoR é um recurso de desempenho premium e, portanto, não está disponível na Standard Edition.
Se você usar uma junção hash no modo em lote no Padrão, você terá um bitmap preso em um Filtro (a menos que o destino seja columnstore e acessado no modo em lote).
Se você usar uma junção hash no modo de linha, obterá um tipo diferente de bitmap que pode ser inserido profundamente no mecanismo de armazenamento (em circunstâncias limitadas).
Você não pode ter o melhor dos dois mundos no Standard. Você pode ter uma junção de hash no modo em lote e um filtro travado ou uma junção de hash no modo de linha e um bitmap que pode ser empurrado para baixo.
O nível de compatibilidade não é um fator, conforme mencionado na pergunta.
Embargo
Nenhuma das opções acima significa sugerir que você não pode ter o modo de lote em algumas partes do plano e o modo de linha em outras. É limitado ao ponto preciso sobre o comportamento dos bitmaps associados a uma junção hash.
O otimizador geralmente é capaz de escolher um plano com hash join em modo de linha mais bitmap, ao mesmo tempo em que executa outros operadores como Sort ou Window Aggregate em modo de lote. Também é perfeitamente capaz de não fazer o que deseja em nenhum caso específico. Tudo está sujeito a estimativas de custos e heurísticas.
Se você puder gerar a forma de plano desejada usando métodos suportados, poderá forçar esse plano usando o armazenamento de consultas ou guias de plano. Boa sorte com isso, em geral.
No seu exemplo, é extremamente improvável que o otimizador escolha o modo em lote para os operadores desejados, ao mesmo tempo que escolhe o modo de linha para a junção de hash. O plano acima serve apenas para mostrar que é uma saída tecnicamente válida do otimizador. Ele não foi gerado usando técnicas suportadas.
Bitmap com junção de mesclagem
A seguinte consulta de demonstração ajustada produz o bitmap desejado com uma junção de mesclagem:
BMoR não é usado. Todos os operadores acima da junção de mesclagem estão no modo em lote. O filtro de modo de linha muito seletivo é empurrado para a varredura paralela do mecanismo de armazenamento de Comments .