A consulta do SQL Server 2016 trava o servidor

O SQL Server 2016 trava continuamente ao executar uma consulta de origem ETL. A memória começa a consumir a uma taxa fantástica, atinge o máximo e o serviço é desligado. Isso parece ridículo, especialmente porque a consulta é executada perfeitamente no SQL Server 2014 com menos memória e discos mais lentos. < O que poderia ter mudado e, mais importante, existe uma maneira de alterar a consulta do servidor ou as configurações de memória em 2016 que evitará esse cenário de travamento? Alguém pode restringir o problema (e confirmar que é um bug) ou fornecer uma solução mais robusta?

MAXDOP é definido como 4, a memória mínima é 8000, a máxima é 24000 com 28 GB no sistema. A máquina SQL 2014 também tinha o SharePoint em execução (instância SQL diferente) e, como resultado, tem uma memória máxima inferior (12533 ​​MB). A máquina SQL 2016 é uma cópia exata, exceto que não possui a instalação do SharePoint e foi atualizada para SSDs.

A consulta tem apenas 9 colunas (6 INTs) para 7.122.737 registros com duas pequenas junções de tabelas e filtragem mínima... não há nada que possa mudar com a consulta e novamente estava funcionando perfeitamente no SQL 2014. Na verdade, ele roda um minuto mais rápido no SQL 2014 vs SQL 2016, apesar dos SSDs e quase o dobro das adições de memória para uma versão supostamente mais rápida!

Portanto, depois de muita pesquisa, várias instalações diferentes, revertendo para um banco de dados, são instaladas apenas em locais padrão. Identifiquei principalmente o problema. Uma das colunas que está sendo hash é uma VARCHAR(MAX)que aciona uma solicitação de memória massiva de HASHBYTES. Não é totalmente irracional, embora a coluna mais longa tenha apenas 49 caracteres. Sistema de origem ... a solução alternativa será lançá-lo dinamicamente, eu acho.

Se o SQL Server atingir a memória máxima, que chega rapidamente com essa consulta, ele trava. Isso parece imprevisível e pode gerar outros erros, deixando o servidor ativo. Adicionar DISTINCT garante o comportamento de travamento, a menos que quantidades excessivas de memória estejam presentes.

Criei um problema do Microsoft Connect aqui se você tiver testes ou ideias adicionais ou puder reproduzir o problema. Eu diria que esta é apenas parcialmente a resposta, pois ainda está meramente restrita a uma gama de possibilidades, que não tenho tempo ou capacidade para identificar completamente. No entanto, não parece ser uma pressão de memória geral ou um resultado de configuração e serviços gerais do SQL 2016.

O seguinte pode reproduzir o problema.

CREATE TABLE Testing (
    VarCharMAX VARCHAR(MAX) NULL,
    VarCharRealistic VARCHAR(100) NULL
) ON PRIMARY
GO

SELECT TOP 1000000
    IDENTITY(INT, 1, 1) AS Number
INTO Numbers
FROM master.dbo.syscolumns x
CROSS JOIN master.dbo.syscolumns
GO --Ensure 1 million rows were inserted

INSERT INTO Testing
        (VarCharMAX, VarCharRealistic)
SELECT
     CAST(Number As VARCHAR(MAX)) + 'a' AS VarCharMAX
    ,CAST(Number As VARCHAR(100)) + 'a' AS VarCharRealistic
FROM Numbers
GO

--Server crash under ~16GB memory
SELECT DISTINCT
    HASHBYTES('md5', VarCharMAX)
FROM dbo.Testing