Joe Obbish's questions

Temos um grupo de disponibilidade que consiste em 42 bancos de dados com uma réplica síncrona e uma réplica assíncrona. A réplica síncrona é capaz de acompanhar a primária (por definição), mas a maioria dos bancos de dados na réplica assíncrona efetivamente congela seu progresso no início do dia útil e não faz progresso até perto do fim do dia útil. Abaixo está um gráfico mostrando o atraso do AG por banco de dados, calculado last_commit_timepor sys.dm_hadr_database_replica_states

Para a maioria dos bancos de dados, o last_commit_timefica por volta das 9:00 AM até quase o fim do dia útil. A citação abaixo é de Troubleshoot: Potential data loss with asynchronous-commit availability-group replicas

As seções a seguir descrevem as causas comuns para alto potencial de perda de dados de uma réplica secundária de confirmação assíncrona, supondo que você não tenha um problema de desempenho sistêmico na sua instância de servidor que não esteja relacionado a grupos de disponibilidade.

1. Alta latência de rede ou baixa taxa de transferência de rede causa acúmulo de log na réplica primária

2. O gargalo de E/S do disco retarda o endurecimento do log na réplica secundária

Cerca de metade dos bancos de dados usam os arquivos de dados do SQL Server no recurso Microsoft Azure. O desempenho de E/S é muito ruim para esses bancos de dados a ponto de às vezes eu ver avisos de E/S de 15 segundos no log de erros. A parte que me confunde é que a maioria dos bancos de dados com o pior atraso não está no armazenamento do Azure. O único padrão que consigo ver é que os bancos de dados com o melhor desempenho de E/S parecem ter o maior atraso.

Considerando vários bancos de dados em um grupo de disponibilidade, é possível que um banco de dados com baixo desempenho de E/S cause atraso em um banco de dados com bom desempenho de E/S?

Alguns detalhes técnicos adicionais sobre minha situação, se necessário:

• A versão do SQL Server é Microsoft SQL Server 2019 (RTM-CU30).

• O data center que hospeda a réplica assíncrona está a 1800 milhas de distância do primário. Ambos os data centers estão nos EUA.

• O ping da réplica assíncrona para o primário é de 40-50 ms. Já me disseram muitas vezes que não estamos atingindo algum tipo de limitação geral de throughput de rede. Um teste netttcp mostra capacidade de largura de banda de 25-30 Mbit/seg.

• De acordo com o hadr_database_flow_control_actionevento estendido, os bancos de dados que não fazem progresso durante o dia útil gastam efetivamente 100% do tempo esperando no controle de fluxo. Os bancos de dados com menos lag não gastam tempo esperando no controle de fluxo.

• De acordo com o hadr_transport_flow_control_actionevento estendido, não há nenhuma espera no controle de fluxo no nível de transporte. O Sends to Transport/seccontador perfmon parece validar isso.

• O sinalizador de rastreamento 12310 foi habilitado de qualquer maneira e não teve nenhum efeito perceptível.

• A latência de gravação para os bancos de dados assíncronos com armazenamento não no Azure é entre 1-10 ms. A latência de gravação para os bancos de dados com armazenamento no Azure chega a 400 ms.

• Usando o Bytes Sent to Replica/seccontador perfmon, a réplica assíncrona consegue manter o ritmo até por volta das 8:30 da manhã. Depois, ela fica para trás e só recupera o atraso depois de algumas grandes explosões de progresso após horas.

• O Log Bytes Flushed/seccontador perfmon (dados ausentes por parte do dia) parece corresponder ao que vemos com bytes enviados. A atividade do usuário final começa a cair por volta das 16h30, que é quando a réplica assíncrona faz mais progresso em recuperação.

• Gerei um relatório de latência AG em 02/06/2025 e os gargalos são envio e controle de fluxo no primário. Não sei como acompanhar esses resultados:

• Estou ciente de que colocar bancos de dados usando os arquivos de dados do SQL Server no recurso Microsoft Azure em um AG assíncrono pode ser uma configuração incomum. Nós efetivamente enviamos os dados 1800 milhas apenas para enviá-los para a nuvem. A documentação da Microsoft sobre o recurso sugere que isso pode não ser necessário:

Benefícios de alta disponibilidade e recuperação de desastres: Usar o recurso SQL Server Data Files no Microsoft Azure pode simplificar as soluções de alta disponibilidade e recuperação de desastres. Por exemplo, se uma máquina virtual no Microsoft Azure ou uma instância do SQL Server travar, você pode recriar seus bancos de dados em uma nova instância do SQL Server apenas restabelecendo links para os blobs.

Eu tenho um processo que inicia e interrompe sessões de eventos estendidos para transferir dados de arquivos de eventos para tabelas SQL. Em um servidor SQL Server 2022 RTM, vejo muitas linhas gravadas nos arquivos de log por este processo:

Gostaria que o início e a interrupção das sessões XE não fossem gravados no arquivo de log porque meus arquivos de log agora estão esmagadoramente preenchidos com essas entradas. Esse comportamento não ocorre em uma instância do Microsoft SQL Server 2019 (RTM-CU22-GDR).

Como posso evitar que o SQL Server registre o início e a interrupção das sessões XE nos arquivos de log do servidor?

Recentemente, configuramos uma nova instância gerenciada por meio do programa que permite que você experimente gratuitamente . Ao tentar executar o seguinte código da documentação da Microsoft :

EXECUTE sp_execute_external_script
  @language =N'Python',
  @script=N'import sys; print("\n".join(sys.path))'

O código falha após 300 segundos com o seguinte erro:

Msg 39012, Nível 16, Estado 14, Linha 0

Não é possível comunicar-se com o tempo de execução do script 'Python' para o ID de solicitação: A1D8A9DA-DBB1-4EDE-B589-3AAFD4241D18. Verifique os requisitos do tempo de execução 'Python'.

Mensagens STDERR de script externo:

SQLSatellite Run() falhou. Código de erro:0x8007271d.

Erro SqlSatelliteCall: SQLSatellite Run() falhou. Código de erro:0x8007271d.

Mensagens STDOUT de script externo:

A função SqlSatelliteCall falhou. Consulte a saída do console para obter mais informações.

Traceback (última chamada mais recente):

Arquivo "D:\WFRoot\Ext\Python.9.4.8.3\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", linha 605, em rx_sql_satellite_call

rx_native_call("SqlSatelliteCall", parâmetros)

Arquivo "D:\WFRoot\Ext\Python.9.4.8.3\lib\site-packages\revoscalepy\RxSerializable.py", linha 375, em rx_native_call

ret = px_call(nome da função, parâmetros)

RuntimeError: falha na função revoscalepy.

Como resolvo esse erro?

Eu tenho uma coluna VARCHAR(MAX) em uma tabela que pode armazenar dados em vários formatos diferentes, incluindo o formato GUID. Tentei o seguinte código ao ingressar em uma coluna UNIQUEIDENTIFIER em outra tabela:

TRY_CAST(b.val AS uniqueidentifier) = a.guid_col

Estou tendo o erro a seguir:

Msg 8152, Nível 16, Estado 10, Linha 73

String ou dados binários seriam truncados.

A documentação para TRY_CAST diz que erros podem ser lançados em algumas circunstâncias:

No entanto, se você solicitar uma conversão explicitamente não permitida, TRY_CAST falhará com um erro.

No entanto, uma conversão de VARCHAR para UNIQUEIDENTIFIER é permitida pelo SQL Server:

Consegui encontrar um valor de exemplo que gera o erro. O valor é bastante longo, então incluí nele um violino . Comentários úteis forneceram uma reprodução mais simples do problema:

DECLARE @s VARCHAR(MAX) = REPLICATE(CAST('A' AS VARCHAR(MAX)), 8001);

SELECT TRY_CAST(@s AS UNIQUEIDENTIFIER);

Por que TRY_CAST lança um erro em vez de retornar um valor NULL ao tentar converter determinados valores VARCHAR em UNIQUEIDENTIFIER?

Minha versão do SQL Server é SQL Server 2019 (RTM-CU18). O código de reprodução a seguir requer que um grupo de arquivos na memória seja criado. Para quem está acompanhando, lembre-se de que um grupo de arquivos na memória não pode ser descartado de um banco de dados depois de criado.

Eu tenho uma tabela simples na memória na qual insiro números inteiros de 1 a 1200:

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem] (
    i [int] NOT NULL,
    CONSTRAINT [PK_InMem]  PRIMARY KEY NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );

INSERT INTO [dbo].[InMem]
SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Eu também tenho o seguinte procedimento armazenado compilado nativamente:

GO

CREATE OR ALTER PROCEDURE p1
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM dbo.[InMem] c1
    CROSS JOIN dbo.[InMem] c2
    CROSS JOIN dbo.[InMem] c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO  

O procedimento retorna uma linha quando executado. Na minha máquina, leva cerca de 32 segundos para ser concluído. Não consigo observar nenhum comportamento incomum em termos de uso de memória durante a execução.

Posso criar um tipo de tabela semelhante:

CREATE TYPE [dbo].[InMemType] AS TABLE(
i [int] NOT NULL,
INDEX [ix_WordBitMap] NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON );

bem como o mesmo procedimento armazenado, mas usando o tipo de tabela:

GO

CREATE OR ALTER PROCEDURE p2 (@t dbo.[InMemType] READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM @t c1
    CROSS JOIN @t c2
    CROSS JOIN @t c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO

O novo procedimento armazenado gera um erro após cerca de um minuto:

Msg 701, Nível 17, Estado 154, Procedimento p2, Linha 6 [Batch Start Line 57] Não há memória de sistema suficiente no pool de recursos 'padrão' para executar esta consulta.

Enquanto o procedimento é executado, posso ver a quantidade de memória usada pelo secretário de memória MEMORYCLERK_XTP aumentar para cerca de 2800 MB para o banco de dados consultando o sys.dm_os_memory_clerksdmv. De acordo com o sys.dm_db_xtp_memory_consumersDMV, quase todo o uso de memória parece ser do consumidor "pool de páginas de 64K":

Para referência, aqui está como executei o novo procedimento armazenado. Ele usa as mesmas 1200 linhas da tabela:

DECLARE @t dbo.[InMemType];

INSERT INTO @t (i)
SELECT i
from [dbo].[InMem];

EXEC p2 @t;

O plano de consulta resultante é um plano de loop aninhado simples sem operadores de bloqueio. Por solicitação, aqui está um plano de consulta estimado para o segundo procedimento armazenado.

Não entendo por que o uso de memória aumenta para mais de 2 GB para essa consulta quando uso um parâmetro com valor de tabela. Eu li vários pedaços de documentação e white papers OLTP na memória e não consigo encontrar nenhuma referência a esse comportamento.

Usando o rastreamento ETW, posso ver que o primeiro procedimento gasta a maior parte do tempo da CPU chamando hkengine!HkCursorHeapGetNexte o segundo procedimento gasta a maior parte do tempo da CPU chamando hkengine!HkCursorRangeGetNext. Também posso obter o código-fonte C para ambos os procedimentos. O primeiro procedimento está aqui e o segundo procedimento, com o problema de memória, está aqui . No entanto, não sei ler o código C, então não sei como investigar mais.

Por que um procedimento armazenado simples compilado nativamente usa mais de 2 GB de memória ao executar loops aninhados em um parâmetro com valor de tabela? O problema também ocorre quando executo a consulta fora de um procedimento armazenado.

O SQL Server 2016 adicionou o AT TIME ZONEoperador. Da documentação :

A implementação AT TIME ZONE depende de um mecanismo do Windows para converter valores de data e hora entre fusos horários.

AT TIME ZONEchama o mscorlib.ni!TimeZoneInfo.ConvertTimemétodo de acordo com o rastreamento ETW em uma consulta simples. Jonathan Kehayias tem uma postagem no blog onde ele extrai todas as regras de fuso horário da System.TimeZoneInfoclasse. Só consigo encontrar regras que tenham efeito em 01/01/2004 ou posterior na saída:

Rob Farley menciona que em um post de blog que uma mudança de regra de fuso horário no ano 2000 não parece ser respeitada por AT TIME ZONE:

Ele funciona usando o registro do Windows, que contém todas essas informações, mas, infelizmente, não é perfeito quando se olha para trás no tempo. A Austrália mudou as datas em 2008, e os EUA mudaram suas datas em 2005 – ambos os países economizando a luz do dia durante a maior parte do ano. AT TIME ZONE entende isso. Mas não parece apreciar que na Austrália no ano 2000, graças às Olimpíadas de Sydney, a Austrália começou o horário de verão cerca de dois meses antes.

Eu sinto que há uma grande quantidade de evidências circunstanciais de que o AT TIME ZONEoperador pode retornar resultados imprecisos para datas anteriores ao ano de 2004. No entanto, não consigo encontrar nenhuma documentação que AT TIME ZONEuse a System.TimeZoneInfoclasse, que AT TIME ZONEpossa ser imprecisa para datas mais antigas ou que a System.TimeZoneInfoclasse pode ser impreciso para datas mais antigas.

Existe uma limitação do produto SQL Server que resulta no AT TIME ZONEretorno de resultados imprecisos antes do ano de 2004?

Eu tenho um procedimento armazenado que tem um parâmetro de saída do CURSOR VARYINGtipo. Gostaria de verificar se o cursor de saída pode ser usado pelo código que chamou o procedimento armazenado. Parecia que CURSOR_STATUSera a função certa a ser usada, mas estou obtendo resultados inesperados ao aplicá-la ao meu cursor de saída. A função retorna um valor de -3 dentro do procedimento armazenado que o criou, mas funciona conforme o esperado fora do procedimento armazenado. Veja o código abaixo:

CREATE OR ALTER PROCEDURE dbo.OutputCursorTest  
(@Cursor_OUT CURSOR VARYING OUTPUT)
AS  
BEGIN
    SET NOCOUNT ON;

    SET @Cursor_OUT = CURSOR FORWARD_ONLY STATIC FOR
    SELECT [high]
    from master..spt_values

    OPEN @Cursor_OUT;

    SELECT CURSOR_STATUS('variable', '@Cursor_OUT'); -- this seems to always return -3

    -- possible workaround
    /*
    DECLARE @Cur_Copy CURSOR;
    SET @Cur_Copy =  @Cursor_OUT;
    SELECT CURSOR_STATUS('variable', '@Cur_Copy');
    DEALLOCATE @Cur_Copy;
    */

    RETURN;
END;

GO     

DECLARE @Cur CURSOR;
EXEC dbo.OutputCursorTest @Cursor_OUT = @Cur OUTPUT;
SELECT CURSOR_STATUS('variable', '@Cur'); -- this returns 1 as expected

Estou no SQL Server 2019 CU14 se for importante. Por que CURSOR_STATUSretorna um valor de -3 ("Um cursor com o nome especificado não existe.") dentro do procedimento armazenado?

Estou testando no SQL Server 2019 CU14. Eu tenho uma consulta de modo de linha pura que seleciona as 50 principais linhas de uma visualização complicada. A consulta completa leva 25.426 ms de tempo de CPU em MAXDOP 1 e 19.068 ms de tempo de CPU em MAXDOP 2. Não estou surpreso que a consulta paralela use menos tempo de CPU em geral. A consulta paralela é elegível para operadores de bitmap e o plano de consulta é diferente em alguns aspectos. No entanto, estou surpreso com uma grande diferença relatada nos tempos de operação para a classificação N superior.

No plano serial, é relatado que a classificação N superior levou cerca de 10 segundos de tempo de CPU pelas estatísticas de execução do operador:

O plano MAXDOP 2 relata cerca de 1,6 segundos de tempo de CPU para a mesma classificação N superior:

Não entendo por que uma diferença tão grande é relatada entre os dois planos de consulta diferentes. Os escalares de computação no operador pai são muito simples e não podem explicar a discrepância nos tempos do operador. Aqui está como eles se parecem:

[Expr1055] = Scalar Operator(CASE WHEN COLUMN_1 IS NULL THEN (0) ELSE datediff(day,COLUMN_1,getdate()) END),
[Expr1074] = Scalar Operator(CASE WHEN [Expr1074] IS NULL THEN (0) ELSE [Expr1074] END)

Existem outros escalares de computação em diferentes partes do plano. Carreguei planos reais anônimos para o plano serial e o plano paralelo, se alguém quiser revisá-los.

Quando carrego os resultados completos da consulta sem TOP em uma tabela temporária e executo uma classificação TOP 50 na tabela temporária, tanto o plano paralelo quanto o serial levam cerca de 1200 ms de tempo de CPU para executar a classificação. Portanto, o tempo do operador relatado para a classificação paralela na consulta completa parece razoável para mim. Os dez segundos para a consulta serial não.

Por que a classificação serial top N tem um tempo de CPU relatado muito maior do que a classificação paralela? É realmente muito menos eficiente ou isso pode ser um bug com as estatísticas de execução da operação?

Ao rodar sp_whoisactiveem um servidor pela primeira vez, fui recebido por algo inesperado:

Duas sessões estavam em andamento há 13 dias, mas ambas pareciam estar bloqueando uma à outra. Dando uma olhada em sys.dm_tran_locks:

O valor de configuração para a definição de limite de processo bloqueado é de 10 segundos. Outros deadlocks estão sendo resolvidos com sucesso no servidor por meio do monitor de deadlock.

Informações do @get_locksparâmetro:

<Database name="DB1">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="TBL1" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>


<Database name="DB1">
    <Locks>
        <Lock request_mode="S" request_status="GRANT" request_count="1" />
    </Locks>
    <Objects>
        <Object name="TBL2" schema_name="dbo">
            <Locks>
                <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="2" />
            </Locks>
        </Object>
        <Object name="TBL1" schema_name="dbo">
            <Locks>
                <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="5" />
                <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="GRANT" request_count="33701" />
                <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="WAIT" request_count="1" />
            </Locks>
        </Object>
    </Objects>
</Database>

Eu ocasionalmente vi o termo "impasse não detectado", mas não tenho nenhuma experiência direta com eles. Minhas perguntas são:

1. Isso poderia ser um exemplo de um impasse não detectado? Não consigo ver como a situação é resolvida sem a intervenção do thread do monitor de deadlock, mas por algum motivo isso ainda não aconteceu.
2. Há algo a fazer além de atualizar para a CU mais recente e esperar que o problema não ocorra novamente? O servidor está atualmente em 2017 CU10 que eu sei que está um pouco desatualizado.

Parte do código do nosso aplicativo converte os tipos de dados BINARYpara fins de hash. Por exemplo, convertemos BIGINTvalores para BINARY(8). A documentação avisa que as conversões podem mudar entre as versões do SQL Server:

Não há garantia de que as conversões entre qualquer tipo de dados e os tipos de dados binários sejam as mesmas entre as versões do SQL Server.

Como defesa contra isso, sempre que começamos a oferecer suporte a uma nova versão do SQL Server, tentamos executar testes para validar se todas as nossas conversões ainda são válidas. Para algo como BIGINT, verificamos o comprimento do valor convertido para os valores mínimo e máximo permitidos BIGINT. Esperamos que isso signifique que saberíamos se, por exemplo, o SQL Server 2019 começou a exigir BINARY(9)o ajuste de todos os valores possíveis de BIGINT.

Como podemos fazer esse tipo de análise para FLOAT(53)? No momento, pensamos que todos os valores possíveis são mapeados para valores únicos que se encaixam em um BINARY(8), mas não sei como validar isso. Isso pode não ser tão simples quanto apenas verificar o número de bytes necessários para o tipo de dados. Por exemplo, o TIMEtipo de dados requer 5 bytes para armazenamento, mas deve ser convertido em a BINARY(6)para evitar erros. Talvez isso seja irrelevante, mas também me deixa nervoso por BINARYnão poder ser convertido novamente para FLOAT. Admito que posso estar pensando demais no problema, então aceito desafios de enquadramento como respostas.

Como posso escrever código para validar que todas as entradas possíveis para FLOAT(53)não estourem a BINARY(8)e que dois FLOAT(53)valores diferentes não sejam convertidos no mesmo BINARY(8)valor?

A documentação para sys.dm_exec_query_statsafirma o seguinte:

Uma consulta inicial de sys.dm_exec_query_stats pode produzir resultados imprecisos se houver uma carga de trabalho em execução no servidor. Resultados mais precisos podem ser determinados executando novamente a consulta.

Às vezes, consulto esse DMV durante uma carga de trabalho ativa e prefiro resultados precisos. Não sei como aplicar o aviso acima na prática. Devo sempre consultar o DMV duas vezes e usar o segundo conjunto de resultados porque isso será mais preciso? Isso parece um pouco exagerado. Preciso estar ciente das maneiras pelas quais o DMV pode ser impreciso para poder levar isso em consideração na minha análise? Em caso afirmativo, que tipo de imprecisões podem aparecer: linhas ausentes, valores desatualizados, linhas inconsistentes ou outra coisa?

Quais são as práticas recomendadas ao usar sys.dm_exec_query_statsdurante uma carga de trabalho ativa?

Eu tenho um UDF escalar CLR implementado por meio de código C#. Percebi que usar o Stringtipo de dados para parâmetros de entrada melhora significativamente o desempenho em comparação com o SqlStringtipo de dados. Em Stairway to SQLCLR Level 5: Development (Usando .NET no SQL Server) , Solomon Rutzky menciona os seguintes motivos para preferir os tipos de dados SQL para strings:

A principal diferença entre os tipos de dados CLR (Common Language Runtime) nativos e os tipos de dados do SQL Server é que os primeiros não permitem valores NULL, enquanto os últimos fornecem semântica NULL completa.

...

Os valores de streaming podem ser obtidos via SqlChars para N[VAR]CHAR, SqlBytes para [VAR]BINARY e SqlXml.CreateReader() para XML...

...

Ao usar SqlString (não string ou mesmo SqlChars) você pode acessar as propriedades CompareInfo, CultureInfo, LCID e SqlCompareOptions...

Eu sei que minha entrada nunca será NULL, não preciso transmitir os valores e nunca verificarei as propriedades de agrupamento. Meu caso poderia ser uma exceção em que é melhor usar Stringem vez de SqlString? Se eu for com essa abordagem, há algo em particular que eu deva observar?

Se for importante, estou usando o agrupamento padrão do SQL Server. Aqui está parte do meu código-fonte, s1sendo o parâmetro de entrada:

fixed (char* chptr = s1)
{
    char* cp = (char*)current;

    for (int i = 0; i < s1.Length; i++)
    {
        cp[i] = chptr[i];
    }
}

Parte da minha carga de trabalho usa uma função CLR que implementa o algoritmo de hash assustador para comparar linhas para ver se algum valor de coluna foi alterado. A função CLR usa uma string binária como entrada, então preciso de uma maneira rápida de converter linhas em uma string binária. Espero fazer hash de cerca de 10 bilhões de linhas durante a carga de trabalho completa, então gostaria que esse código fosse o mais rápido possível.

Tenho cerca de 300 tabelas com esquemas diferentes. Para os propósitos desta questão, suponha uma estrutura de tabela simples de 32 INTcolunas anuláveis. Forneci dados de exemplo, bem como uma maneira de comparar os resultados na parte inferior desta pergunta.

As linhas devem ser convertidas na mesma string binária se todos os valores das colunas forem iguais. As linhas devem ser convertidas em strings binárias diferentes se algum valor de coluna for diferente. Por exemplo, código tão simples quanto o seguinte não funcionará:

CAST(COL1 AS BINARY(4)) + CAST(COL2 AS BINARY(4)) + ..

Ele não manipula NULLs corretamente. Se COL1for NULL para a linha 1 e COL2for NULL para a linha 2, ambas as linhas serão convertidas em uma string NULL. Acredito que o tratamento correto de NULLs é a parte mais difícil de converter a linha inteira corretamente. Todos os valores permitidos para as colunas INT são possíveis.

Para antecipar algumas perguntas:

• Se for importante, na maioria das vezes (90%+) as colunas não serão NULL.
• Eu tenho que usar o CLR.
• Eu tenho que hash tantas linhas. Não consigo persistir os hashes.
• Acredito que não posso usar o modo batch para a conversão devido à presença da função CLR.

Qual é a maneira mais rápida de converter 32 INTcolunas anuláveis ​​em uma string BINARY(X)ou ?VARBINARY(X)

Dados de amostra e código conforme prometido:

-- create sample data
DROP TABLE IF EXISTS dbo.TABLE_OF_32_INTS;

CREATE TABLE dbo.TABLE_OF_32_INTS (
    COL1 INT NULL,
    COL2 INT NULL,
    COL3 INT NULL,
    COL4 INT NULL,
    COL5 INT NULL,
    COL6 INT NULL,
    COL7 INT NULL,
    COL8 INT NULL,
    COL9 INT NULL,
    COL10 INT NULL,
    COL11 INT NULL,
    COL12 INT NULL,
    COL13 INT NULL,
    COL14 INT NULL,
    COL15 INT NULL,
    COL16 INT NULL,
    COL17 INT NULL,
    COL18 INT NULL,
    COL19 INT NULL,
    COL20 INT NULL,
    COL21 INT NULL,
    COL22 INT NULL,
    COL23 INT NULL,
    COL24 INT NULL,
    COL25 INT NULL,
    COL26 INT NULL,
    COL27 INT NULL,
    COL28 INT NULL,
    COL29 INT NULL,
    COL30 INT NULL,
    COL31 INT NULL,
    COL32 INT NULL
);

INSERT INTO dbo.TABLE_OF_32_INTS WITH (TABLOCK)
SELECT 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, NULL, -876545321
FROM
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);


GO


-- procedure to test performance
CREATE OR ALTER PROCEDURE #p AS 
BEGIN

SET NOCOUNT ON;

DECLARE
@counter INT = 0,
@dummy VARBINARY(8000);

WHILE @counter < 10
BEGIN
    SELECT @dummy = -- this code is clearly incomplete as it does not handle NULLs
        CAST(COL1 AS BINARY(4)) + 
        CAST(COL2 AS BINARY(4)) + 
        CAST(COL3 AS BINARY(4)) + 
        CAST(COL4 AS BINARY(4)) + 
        CAST(COL5 AS BINARY(4)) + 
        CAST(COL6 AS BINARY(4)) + 
        CAST(COL7 AS BINARY(4)) + 
        CAST(COL8 AS BINARY(4)) + 
        CAST(COL9 AS BINARY(4)) + 
        CAST(COL10 AS BINARY(4)) + 
        CAST(COL11 AS BINARY(4)) + 
        CAST(COL12 AS BINARY(4)) + 
        CAST(COL13 AS BINARY(4)) + 
        CAST(COL14 AS BINARY(4)) + 
        CAST(COL15 AS BINARY(4)) + 
        CAST(COL16 AS BINARY(4)) + 
        CAST(COL17 AS BINARY(4)) + 
        CAST(COL18 AS BINARY(4)) + 
        CAST(COL19 AS BINARY(4)) + 
        CAST(COL20 AS BINARY(4)) + 
        CAST(COL21 AS BINARY(4)) + 
        CAST(COL22 AS BINARY(4)) + 
        CAST(COL23 AS BINARY(4)) + 
        CAST(COL24 AS BINARY(4)) + 
        CAST(COL25 AS BINARY(4)) + 
        CAST(COL26 AS BINARY(4)) + 
        CAST(COL27 AS BINARY(4)) + 
        CAST(COL28 AS BINARY(4)) + 
        CAST(COL29 AS BINARY(4)) + 
        CAST(COL30 AS BINARY(4)) + 
        CAST(COL31 AS BINARY(4)) + 
        CAST(COL32 AS BINARY(4))
    FROM dbo.TABLE_OF_32_INTS
    OPTION (MAXDOP 1);

    SET @counter = @counter + 1;
END;

SELECT cpu_time
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

END;

GO

-- run procedure
EXEC #p;

(Ainda estarei usando o hash assustador neste resultado binário. A carga de trabalho usa junções de hash e o valor de hash é usado para uma das compilações de hash. Não quero um valor binário longo na compilação de hash porque requer muito memória.)

O seguinte T-SQL leva cerca de 25 segundos na minha máquina com SSMS v17.9:

DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);

SET NOCOUNT ON;

WHILE @outer_loop < 50000000
BEGIN
    SET @big_string_for_u = 'ZZZZZZZZZZ';
    SET @outer_loop = @outer_loop + 1;
END;

Acumula 532 ms de ASYNC_NETWORK_IOesperas de acordo com sys.dm_exec_session_wait_statse sys.dm_os_wait_stats. O tempo total de espera aumenta à medida que o número de iterações de loop aumenta. Usando o wait_completedevento estendido, posso ver que a espera acontece aproximadamente a cada 43 ms, com algumas exceções:

Além disso, posso obter as pilhas de chamadas que ocorrem logo antes da ASYNC_NETWORK_IOespera:

sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9

Finalmente, notei que o SSMS usa uma quantidade surpreendente de CPU durante o loop (cerca de meio núcleo em média). Não consigo descobrir o que o SSMS está fazendo durante esse período.

Por que um loop simples causa ASYNC_NETWORK_IOesperas quando executado por meio do SSMS? A única saída que pareço obter do cliente dessa execução de consulta é "Comandos concluídos com êxito". mensagem.

Considere a seguinte consulta que insere linhas de uma tabela de origem somente se elas ainda não estiverem na tabela de destino:

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

Uma forma de plano possível inclui uma junção de mesclagem e um spool antecipado. O operador de carretel ansioso está presente para resolver o problema do Halloween :

Na minha máquina, o código acima é executado em cerca de 6900 ms. O código de reprodução para criar as tabelas está incluído na parte inferior da pergunta. Se estou insatisfeito com o desempenho, posso tentar carregar as linhas a serem inseridas em uma tabela temporária em vez de depender do spool ansioso. Aqui está uma implementação possível:

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

O novo código é executado em cerca de 4400 ms. Posso obter planos reais e usar Actual Time Statistics™ para examinar onde o tempo é gasto no nível do operador. Observe que solicitar um plano real adiciona uma sobrecarga significativa para essas consultas, de modo que os totais não corresponderão aos resultados anteriores.

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

O plano de consulta com o spool ansioso parece gastar muito mais tempo nos operadores de inserção e spool em comparação com o plano que usa a tabela temporária.

Por que o plano com a tabela temporária é mais eficiente? Um carretel ansioso não é principalmente apenas uma tabela temporária interna? Acredito que estou procurando respostas que se concentrem nos internos. Consigo ver como as pilhas de chamadas são diferentes, mas não consigo entender o quadro geral.

Estou no SQL Server 2017 CU 11 caso alguém queira saber. Aqui está o código para preencher as tabelas usadas nas consultas acima:

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Como parte de nosso processo de ETL, comparamos as linhas do teste com o banco de dados de relatórios para descobrir se alguma das colunas realmente mudou desde que os dados foram carregados pela última vez.

A comparação é baseada na chave exclusiva da tabela e em algum tipo de hash de todas as outras colunas. Atualmente, usamos HASHBYTEScom o SHA2_256algoritmo e descobrimos que ele não é dimensionado em servidores grandes se muitos threads de trabalho simultâneos estiverem chamando HASHBYTES.

A taxa de transferência medida em hashes por segundo não aumenta além de 16 threads simultâneos ao testar em um servidor de 96 núcleos. Eu testo alterando o número de MAXDOP 8consultas simultâneas de 1 a 12. O teste com MAXDOP 1mostrou o mesmo gargalo de escalabilidade.

Como solução, quero tentar uma solução SQL CLR. Aqui está minha tentativa de declarar os requisitos:

• A função deve ser capaz de participar de consultas paralelas
• A função deve ser determinística
• A função deve receber uma entrada de uma string NVARCHARou (todas as colunas relevantes são concatenadas)VARBINARY
• O tamanho de entrada típico da string será de 100 a 20.000 caracteres. 20000 não é um máximo
• A chance de uma colisão de hash deve ser aproximadamente igual ou melhor que o algoritmo MD5. CHECKSUMnão funciona para nós porque há muitas colisões.
• A função deve ser bem dimensionada em servidores grandes (a taxa de transferência por encadeamento não deve diminuir significativamente à medida que o número de encadeamentos aumenta)

Para Application Reasons™, suponha que não posso salvar o valor do hash para a tabela de relatórios. É um CCI que não suporta gatilhos ou colunas computadas (há outros problemas também nos quais não quero entrar).

O que é uma maneira escalável de simular HASHBYTESusando uma função SQL CLR? Meu objetivo pode ser expresso como obter o maior número possível de hashes por segundo em um servidor grande, portanto, o desempenho também importa. Eu sou terrível com CLR, então não sei como fazer isso. Se isso motivar alguém a responder, pretendo adicionar uma recompensa a essa pergunta assim que puder. Abaixo está um exemplo de consulta que ilustra muito aproximadamente o caso de uso:

DROP TABLE IF EXISTS #CHANGED_IDS;

SELECT stg.ID INTO #CHANGED_IDS
FROM (
    SELECT ID,
    CAST( HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)))
     AS BINARY(32)) HASH1
    FROM HB_TBL WITH (TABLOCK)
) stg
INNER JOIN (
    SELECT ID,
    CAST(HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)) )
 AS BINARY(32)) HASH1
    FROM HB_TBL_2 WITH (TABLOCK)
) rpt ON rpt.ID = stg.ID
WHERE rpt.HASH1 <> stg.HASH1
OPTION (MAXDOP 8);

Para simplificar um pouco as coisas, provavelmente usarei algo como o seguinte para benchmarking. Vou postar resultados com HASHBYTESna segunda-feira:

CREATE TABLE dbo.HASH_ME (
    ID BIGINT NOT NULL,
    FK1 BIGINT NOT NULL,
    FK2 BIGINT NOT NULL,
    FK3 BIGINT NOT NULL,
    FK4 BIGINT NOT NULL,
    FK5 BIGINT NOT NULL,
    FK6 BIGINT NOT NULL,
    FK7 BIGINT NOT NULL,
    FK8 BIGINT NOT NULL,
    FK9 BIGINT NOT NULL,
    FK10 BIGINT NOT NULL,
    FK11 BIGINT NOT NULL,
    FK12 BIGINT NOT NULL,
    FK13 BIGINT NOT NULL,
    FK14 BIGINT NOT NULL,
    FK15 BIGINT NOT NULL,
    STR1 NVARCHAR(500) NOT NULL,
    STR2 NVARCHAR(500) NOT NULL,
    STR3 NVARCHAR(500) NOT NULL,
    STR4 NVARCHAR(500) NOT NULL,
    STR5 NVARCHAR(2000) NOT NULL,
    COMP1 TINYINT NOT NULL,
    COMP2 TINYINT NOT NULL,
    COMP3 TINYINT NOT NULL,
    COMP4 TINYINT NOT NULL,
    COMP5 TINYINT NOT NULL
);

INSERT INTO dbo.HASH_ME WITH (TABLOCK)
SELECT RN,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 1000),
0,1,0,1,0
FROM (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

SELECT MAX(HASHBYTES('SHA2_256',
CAST(N'' AS NVARCHAR(MAX)) + N'|' +
CAST(FK1 AS NVARCHAR(19)) + N'|' +
CAST(FK2 AS NVARCHAR(19)) + N'|' +
CAST(FK3 AS NVARCHAR(19)) + N'|' +
CAST(FK4 AS NVARCHAR(19)) + N'|' +
CAST(FK5 AS NVARCHAR(19)) + N'|' +
CAST(FK6 AS NVARCHAR(19)) + N'|' +
CAST(FK7 AS NVARCHAR(19)) + N'|' +
CAST(FK8 AS NVARCHAR(19)) + N'|' +
CAST(FK9 AS NVARCHAR(19)) + N'|' +
CAST(FK10 AS NVARCHAR(19)) + N'|' +
CAST(FK11 AS NVARCHAR(19)) + N'|' +
CAST(FK12 AS NVARCHAR(19)) + N'|' +
CAST(FK13 AS NVARCHAR(19)) + N'|' +
CAST(FK14 AS NVARCHAR(19)) + N'|' +
CAST(FK15 AS NVARCHAR(19)) + N'|' +
CAST(STR1 AS NVARCHAR(500)) + N'|' +
CAST(STR2 AS NVARCHAR(500)) + N'|' +
CAST(STR3 AS NVARCHAR(500)) + N'|' +
CAST(STR4 AS NVARCHAR(500)) + N'|' +
CAST(STR5 AS NVARCHAR(2000)) + N'|' +
CAST(COMP1 AS NVARCHAR(1)) + N'|' +
CAST(COMP2 AS NVARCHAR(1)) + N'|' +
CAST(COMP3 AS NVARCHAR(1)) + N'|' +
CAST(COMP4 AS NVARCHAR(1)) + N'|' +
CAST(COMP5 AS NVARCHAR(1)) )
)
FROM dbo.HASH_ME
OPTION (MAXDOP 1);

Consegui reproduzir um problema de desempenho de consulta que descreveria como inesperado. Estou procurando uma resposta focada em internos.

Na minha máquina, a consulta a seguir faz uma verificação de índice clusterizado e leva cerca de 6,8 segundos de tempo de CPU:

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

A consulta a seguir faz uma busca de índice clusterizado (a única diferença é remover a FORCESCANdica), mas leva cerca de 18,2 segundos de tempo de CPU:

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

Os planos de consulta são bastante semelhantes. Para ambas as consultas, há 120000001 linhas lidas do índice clusterizado:

Estou no SQL Server 2017 CU 10. Aqui está o código para criar e preencher a two_col_key_testtabela:

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);


INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

Espero uma resposta que faça mais do que relatórios de pilha de chamadas. Por exemplo, posso ver que sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataXleva significativamente mais ciclos de CPU na consulta lenta em comparação com a rápida:

Em vez de parar por aí, gostaria de entender o que é isso e por que há uma diferença tão grande entre as duas consultas.

Por que há uma grande diferença no tempo de CPU para essas duas consultas?

Eu estava trabalhando em uma demonstração envolvendo CCIs quando notei que algumas das minhas inserções estavam demorando mais do que o esperado. Definições de tabela para reproduzir:

DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_1048576
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

Para os testes estou inserindo todas as 1048576 linhas da tabela de teste. Isso é suficiente para preencher exatamente um rowgroup compactado, desde que ele não seja cortado por algum motivo.

Se eu inserir todos os inteiros mod 17000, leva menos de um segundo:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 17000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

Tempos de execução do SQL Server: tempo de CPU = 359 ms, tempo decorrido = 364 ms.

No entanto, se eu inserir os mesmos inteiros mod 16000, às vezes leva mais de 30 segundos:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

Tempos de execução do SQL Server: tempo de CPU = 32.062 ms, tempo decorrido = 32.511 ms.

Este é um teste repetível que foi feito em várias máquinas. Parece haver um padrão claro no tempo decorrido à medida que o valor do mod muda:

MOD_NUM TIME_IN_MS
1000    2036
2000    3857
3000    5463
4000    6930
5000    8414
6000    10270
7000    12350
8000    13936
9000    17470
10000   19946
11000   21373
12000   24950
13000   28677
14000   31030
15000   34040
16000   37000
17000   563
18000   583
19000   576
20000   584

Se você quiser executar testes, sinta-se à vontade para modificar o código de teste que escrevi aqui .

Não consegui encontrar nada interessante em sys.dm_os_wait_stats para a inserção do mod 16000:

╔════════════════════════════════════╦══════════════╗
║             wait_type              ║ diff_wait_ms ║
╠════════════════════════════════════╬══════════════╣
║ XE_DISPATCHER_WAIT                 ║       164406 ║
║ QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   ║       120002 ║
║ LAZYWRITER_SLEEP                   ║        97718 ║
║ LOGMGR_QUEUE                       ║        97298 ║
║ DIRTY_PAGE_POLL                    ║        97254 ║
║ HADR_FILESTREAM_IOMGR_IOCOMPLETION ║        97111 ║
║ SQLTRACE_INCREMENTAL_FLUSH_SLEEP   ║        96008 ║
║ REQUEST_FOR_DEADLOCK_SEARCH        ║        95001 ║
║ XE_TIMER_EVENT                     ║        94689 ║
║ SLEEP_TASK                         ║        48308 ║
║ BROKER_TO_FLUSH                    ║        48264 ║
║ CHECKPOINT_QUEUE                   ║        35589 ║
║ SOS_SCHEDULER_YIELD                ║           13 ║
╚════════════════════════════════════╩══════════════╝

Por que a inserção de ID % 16000demora muito mais do que a inserção de ID % 17000?

Estou fazendo esta pergunta para entender melhor o comportamento do otimizador e entender os limites em torno dos spools de índice. Suponha que eu coloque inteiros de 1 a 10000 em um heap:

CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;

INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

E force uma junção de loop aninhado com MAXDOP 1:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

Esta é uma ação bastante hostil para o SQL Server. As junções de loop aninhadas geralmente não são uma boa opção quando ambas as tabelas não têm índices relevantes. Aqui está o plano:

A consulta leva 13 segundos na minha máquina com 100000000 linhas buscadas no spool da tabela. No entanto, não vejo por que a consulta deve ser lenta. O otimizador de consulta tem a capacidade de criar índices em tempo real por meio de spools de índice . Esta consulta parece ser um candidato perfeito para um spool de índice.

A consulta a seguir retorna os mesmos resultados que a primeira, tem um spool de índice e termina em menos de um segundo:

SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);

Essa consulta também possui um spool de índice e termina em menos de um segundo:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);

Por que a consulta original não tem um spool de índice? Existe algum conjunto de dicas documentadas ou não documentadas ou sinalizadores de rastreamento que fornecerão um spool de índice? Encontrei esta pergunta relacionada , mas ela não responde totalmente à minha pergunta e não consigo fazer com que o sinalizador de rastreamento misterioso funcione para essa consulta.

Isso é um pouco de desvio do problema real. Se fornecer contexto ajudar, a geração desses dados pode ser útil para formas de teste de desempenho de processamento de strings, para gerar strings que precisam ter alguma operação aplicada a elas dentro de um cursor ou para gerar substituições de nomes anônimos exclusivos para dados confidenciais. Estou interessado apenas em maneiras eficientes de gerar os dados dentro de SQL Servers, por favor, não pergunte por que preciso gerar esses dados.

Vou tentar começar com uma definição um tanto formal. Uma string é incluída na série se consistir apenas em letras maiúsculas de A - Z. O primeiro termo da série é "A". A série consiste em todas as strings válidas classificadas primeiro por comprimento e depois por ordem alfabética típica. Se as strings estivessem em uma tabela em uma coluna chamada STRING_COL, a ordem poderia ser definida em T-SQL como ORDER BY LEN(STRING_COL) ASC, STRING_COL ASC.

Para dar uma definição menos formal, dê uma olhada nos cabeçalhos das colunas alfabéticas no Excel. A série é o mesmo padrão. Considere como você pode converter um número inteiro em um número de base 26:

1 -> A, 2 -> B, 3 -> C, ... , 25 -> Y, 26 -> Z, 27 -> AA, 28 -> AB, ...

A analogia não é perfeita porque "A" se comporta de maneira diferente de 0 na base dez. Abaixo está uma tabela de valores selecionados que esperamos tornar mais claro:

╔════════════╦════════╗
║ ROW_NUMBER ║ STRING ║
╠════════════╬════════╣
║          1 ║ A      ║
║          2 ║ B      ║
║         25 ║ Y      ║
║         26 ║ Z      ║
║         27 ║ AA     ║
║         28 ║ AB     ║
║         51 ║ AY     ║
║         52 ║ AZ     ║
║         53 ║ BA     ║
║         54 ║ BB     ║
║      18278 ║ ZZZ    ║
║      18279 ║ AAAA   ║
║     475253 ║ ZZZY   ║
║     475254 ║ ZZZZ   ║
║     475255 ║ AAAAA  ║
║  100000000 ║ HJUNYV ║
╚════════════╩════════╝

O objetivo é escrever uma SELECTconsulta que retorne as primeiras 100000000 strings na ordem definida acima. Fiz meus testes executando consultas no SSMS com o conjunto de resultados descartado em vez de salvá-lo em uma tabela:

Idealmente, a consulta será razoavelmente eficiente. Aqui estou definindo eficiente como tempo de CPU para uma consulta serial e tempo decorrido para uma consulta paralela. Você pode usar quaisquer truques não documentados que desejar. Confiar em comportamento indefinido ou não garantido também é bom, mas seria apreciado se você chamasse isso em sua resposta.

Quais são alguns métodos para gerar eficientemente o conjunto de dados descrito acima? Martin Smith apontou que um procedimento armazenado CLR provavelmente não é uma boa abordagem devido à sobrecarga de processamento de tantas linhas.