bug em database_scoped_configurations

Isso é um bug do SQL Server 2016?

Sim. Definitivamente, este não é um comportamento correto. Eu relatei aqui e está corrigido no SQL Server 2016 SP2 CU9 .

Como diz Mikael Eriksson nos comentários sys.database_scoped_configurationse sys.dm_exec_sessionssão implementadas como views no formato

SELECT ...  
FROM OpenRowset(TABLE xxxx)  

No entanto, comparando os dois planos abaixo, há uma diferença óbvia.

DBCC TRACEON(3604);

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );


DECLARE @dm_exec_sessions TABLE(x INT);

INSERT INTO @dm_exec_sessions
SELECT session_id
FROM   sys.dm_exec_sessions
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

A saída do sinalizador de rastreamento 8619 para ambas as consultas mostra

Aplicar Regra: EnforceHPandAccCard - x0-> Spool ou Top (x0)

Aparentemente, o SQL Server não é capaz de verificar se a origem do TVF também não é o destino de inserção, portanto, requer proteção do Dia das Bruxas.

No caso das sessões, isso foi implementado como um spool que captura todas as linhas primeiro. No database_scoped_configurationsadicionando um TOP 1ao plano. O uso de TOPpara proteção de Halloween é discutido neste artigo . O artigo também menciona um sinalizador de rastreamento não documentado para forçar um spool em vez de TOPfuncionar conforme o esperado.

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8692)

Um problema óbvio com o uso TOP 1em vez de um spool é que ele limitará arbitrariamente o número de linhas inseridas. Portanto, isso só seria válido se o número de linhas retornado pela função fosse <=1.

O memorando inicial é assim

Compare isso com o memorando inicial para a consulta 2

Se bem entendi o que foi dito acima, ele pensa que o primeiro TVF pode retornar no máximo uma linha e, portanto, aplica uma otimização incorreta. O máximo para a segunda consulta é definido como 1.34078E+154( 2^512).

Não tenho ideia de onde essa contagem máxima de linhas é derivada. Talvez metadados fornecidos pelo autor do DMV? Também é estranho que a TOP(50)solução alternativa não seja reescrita TOP(1)porque TOP(50)não impediria que o problema do Halloween ocorresse (embora o impedisse de continuar indefinidamente)

Por favor, pare de usar sp_MSForEachDB. Não é suportado, não é documentado e tem bugs - o que pode ser o problema aqui. Minha substituição demonstra o mesmo problema aqui, mas em geral é uma coisa mais segura de usar.

Para coisas como essa, prefiro gerar SQL dinâmico do que entregar um único comando para um procedimento executar várias vezes (até mesmo meu procedimento, no qual confio muito mais), dessa forma posso simplesmente imprimir os comandos em vez de executá-los e certifique-se de que todos farão o que dizem.

Tomando emprestado a observação de que o código subjacente à visão do sistema implementa um TOP (1), podemos tentar desta maneira:

DROP TABLE IF EXISTS #h;

CREATE TABLE #h(dbname sysname, configuration_id INT, name sysname, 
  value SQL_VARIANT,  value_for_secondary SQL_VARIANT);

DECLARE @sql nvarchar(max) = N'', @base nvarchar(max) = N'insert into #h
  (dbname, configuration_id, name, value,value_for_secondary)  SELECT TOP ($c$) 
  $db$ as dbname, * FROM $qdb$.sys.database_scoped_configurations;';

SELECT @sql += REPLACE(REPLACE(REPLACE(@base, N'$qdb$', QUOTENAME(name)), 
  N'$db$', CHAR(39) + name + CHAR(39)), N'$c$', RTRIM(COUNT(*) OVER()))
FROM sys.databases WHERE state = 0;

PRINT @sql;
EXEC sys.sp_executesql @sql;
SELECT * FROM #h;

Observe que não uso USEaqui, mas sim prefixo a sysvisualização do catálogo com o nome do banco de dados.

Por que a visão funciona de maneira mágica, não sei; Não sei se você obterá uma boa resposta aqui, pois provavelmente requer comentários da Microsoft (ou de qualquer pessoa com acesso ao código-fonte ou disposto a iniciar um depurador).

Obrigado por comunicar este problema!

Este é realmente um bug na forma como o Query Optimizer gera um plano para a sys.database_scoped_configurationsvisualização do catálogo. Abordaremos isso em uma das próximas atualizações do SQL Server 2016 e no Banco de Dados SQL do Azure.

Como solução alternativa, você pode adicionar uma TOPcláusula na SELECTparte de sua inserção para obter o plano correto, por exemplo:

DECLARE @database_scoped_configurations TABLE(x INT); 
INSERT INTO @database_scoped_configurations 
SELECT **TOP 100** configuration_id 
FROM sys.database_scoped_configurations 

Concordo que isso é muito estranho e um bug em potencial, mas adicionar um TOP(50), por exemplo, ao seu select realmente retorna todas as linhas, de modo que pelo menos o ajudaria. O resultado parece vir de uma função de valor de tabela do sistema ([DB_SCOPED_CONFIG]), então não posso dizer o que está acontecendo.

Estarei de olho neste tópico para ver se as pessoas 'mais inteligentes' sabem POR QUE isso está acontecendo.