Início / dba / Perguntas / 228789
Accepted
Joe Obbish
Asked: 2019-02-04 08:57:07 +0800 CST

Qual é uma maneira escalável de simular HASHBYTES usando uma função escalar SQL CLR?

  • 772

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);
sql-server sql-server-2016

4 respostas

  1. Best Answer

    Como você está apenas procurando por alterações, não precisa de uma função hash criptográfica.

    Você pode escolher um dos hashes não criptográficos mais rápidos da biblioteca Data.HashFunction de código aberto de Brandon Dahler, licenciado sob a licença MIT permissiva e aprovada pela OSI . SpookyHashé uma escolha popular.

    Exemplo de implementação

    Código fonte

    using Microsoft.SqlServer.Server;
using System.Data.HashFunction.SpookyHash;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
            DataAccess = DataAccessKind.None,
            SystemDataAccess = SystemDataAccessKind.None,
            IsDeterministic = true,
            IsPrecise = true
        )
    ]
    public static byte[] SpookyHash
        (
            [SqlFacet (MaxSize = 8000)]
            SqlBinary Input
        )
    {
        ISpookyHashV2 sh = SpookyHashV2Factory.Instance.Create();
        return sh.ComputeHash(Input.Value).Hash;
    }

    [SqlFunction
        (
            DataAccess = DataAccessKind.None,
            IsDeterministic = true,
            IsPrecise = true,
            SystemDataAccess = SystemDataAccessKind.None
        )
    ]
    public static byte[] SpookyHashLOB
        (
            [SqlFacet (MaxSize = -1)]
            SqlBinary Input
        )
    {
        ISpookyHashV2 sh = SpookyHashV2Factory.Instance.Create();
        return sh.ComputeHash(Input.Value).Hash;
    }
}

    A fonte fornece duas funções, uma para entradas de 8000 bytes ou menos, e uma versão LOB. A versão não-LOB deve ser significativamente mais rápida.

    Você pode envolver um binário LOB COMPRESSpara colocá-lo abaixo do limite de 8.000 bytes, se isso valer a pena para o desempenho. Alternativamente, você pode dividir o LOB em segmentos abaixo de 8000 bytes, ou simplesmente reservar o uso de HASHBYTESpara o caso LOB (já que entradas mais longas escalam melhor).

    Código pré-construído

    Obviamente, você pode pegar o pacote para si mesmo e compilar tudo, mas eu construí os assemblies abaixo para facilitar o teste rápido:

    https://gist.github.com/SQLKiwi/365b265b476bf86754457fc9514b2300

    Funções T-SQL

    CREATE FUNCTION dbo.SpookyHash
(
    @Input varbinary(8000)
)
RETURNS binary(16)
WITH 
    RETURNS NULL ON NULL INPUT, 
    EXECUTE AS OWNER
AS EXTERNAL NAME Spooky.UserDefinedFunctions.SpookyHash;
GO
CREATE FUNCTION dbo.SpookyHashLOB
(
    @Input varbinary(max)
)
RETURNS binary(16)
WITH 
    RETURNS NULL ON NULL INPUT, 
    EXECUTE AS OWNER
AS EXTERNAL NAME Spooky.UserDefinedFunctions.SpookyHashLOB;
GO

    Uso

    Um exemplo de uso dado os dados de exemplo na pergunta:

    SELECT
    HT1.ID
FROM dbo.HB_TBL AS HT1
JOIN dbo.HB_TBL_2 AS HT2
    ON HT2.ID = HT1.ID
    AND dbo.SpookyHash
    (
        CONVERT(binary(8), HT2.FK1) + 0x7C +
        CONVERT(binary(8), HT2.FK2) + 0x7C +
        CONVERT(binary(8), HT2.FK3) + 0x7C +
        CONVERT(binary(8), HT2.FK4) + 0x7C +
        CONVERT(binary(8), HT2.FK5) + 0x7C +
        CONVERT(binary(8), HT2.FK6) + 0x7C +
        CONVERT(binary(8), HT2.FK7) + 0x7C +
        CONVERT(binary(8), HT2.FK8) + 0x7C +
        CONVERT(binary(8), HT2.FK9) + 0x7C +
        CONVERT(binary(8), HT2.FK10) + 0x7C +
        CONVERT(binary(8), HT2.FK11) + 0x7C +
        CONVERT(binary(8), HT2.FK12) + 0x7C +
        CONVERT(binary(8), HT2.FK13) + 0x7C +
        CONVERT(binary(8), HT2.FK14) + 0x7C +
        CONVERT(binary(8), HT2.FK15) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR1) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR2) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR3) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR4) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR5) + 0x7C +
        CONVERT(binary(1), HT2.COMP1) + 0x7C +
        CONVERT(binary(1), HT2.COMP2) + 0x7C +
        CONVERT(binary(1), HT2.COMP3) + 0x7C +
        CONVERT(binary(1), HT2.COMP4) + 0x7C +
        CONVERT(binary(1), HT2.COMP5)
    )
    <> dbo.SpookyHash
    (
        CONVERT(binary(8), HT1.FK1) + 0x7C +
        CONVERT(binary(8), HT1.FK2) + 0x7C +
        CONVERT(binary(8), HT1.FK3) + 0x7C +
        CONVERT(binary(8), HT1.FK4) + 0x7C +
        CONVERT(binary(8), HT1.FK5) + 0x7C +
        CONVERT(binary(8), HT1.FK6) + 0x7C +
        CONVERT(binary(8), HT1.FK7) + 0x7C +
        CONVERT(binary(8), HT1.FK8) + 0x7C +
        CONVERT(binary(8), HT1.FK9) + 0x7C +
        CONVERT(binary(8), HT1.FK10) + 0x7C +
        CONVERT(binary(8), HT1.FK11) + 0x7C +
        CONVERT(binary(8), HT1.FK12) + 0x7C +
        CONVERT(binary(8), HT1.FK13) + 0x7C +
        CONVERT(binary(8), HT1.FK14) + 0x7C +
        CONVERT(binary(8), HT1.FK15) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR1) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR2) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR3) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR4) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR5) + 0x7C +
        CONVERT(binary(1), HT1.COMP1) + 0x7C +
        CONVERT(binary(1), HT1.COMP2) + 0x7C +
        CONVERT(binary(1), HT1.COMP3) + 0x7C +
        CONVERT(binary(1), HT1.COMP4) + 0x7C +
        CONVERT(binary(1), HT1.COMP5)
    );

    Ao usar a versão LOB, o primeiro parâmetro deve ser convertido ou convertido paravarbinary(max) .

    Plano de execução

    plan

    Assustador Seguro

    A biblioteca Data.HashFunction usa vários recursos de linguagem CLR que são considerados UNSAFEpelo SQL Server. É possível escrever um Spooky Hash básico compatível com o SAFEstatus. Um exemplo que escrevi baseado no SpookilySharp de Jon Hanna está abaixo:

    https://gist.github.com/SQLKiwi/7a5bb26b0bee56f6d28a1d26669ce8f2

    • 21

  2. Não tenho certeza se o paralelismo será significativamente melhor com SQLCLR. No entanto, é realmente fácil de testar, pois existe uma função hash na versão gratuita da biblioteca SQL# SQLCLR (que eu escrevi) chamada Util_HashBinary . Os algoritmos suportados são: MD5, SHA1, SHA256, SHA384 e SHA512.

    Ele recebe um VARBINARY(MAX)valor como entrada, então você pode concatenar a versão da string de cada campo (como está fazendo atualmente) e depois converter para VARBINARY(MAX), ou você pode ir diretamente VARBINARYpara cada coluna e concatenar os valores convertidos (isso pode ser mais rápido, pois você não está lidando com strings ou a conversão extra de string para VARBINARY). Abaixo está um exemplo mostrando ambas as opções. Ele também mostra a HASHBYTESfunção para que você possa ver que os valores são os mesmos entre ela e SQL#.Util_HashBinary .

    Observe que os resultados de hash ao concatenar os VARBINARYvalores não corresponderão aos resultados de hash ao concatenar os NVARCHARvalores. Isso ocorre porque a forma binária do INTvalor "1" é 0x00000001, enquanto a forma UTF-16LE (ou seja NVARCHAR, ) do INTvalor de "1" (em forma binária, pois é sobre isso que uma função de hash operará) é 0x3100.

    SELECT so.[object_id],
       SQL#.Util_HashBinary(N'SHA256',
                            CONVERT(VARBINARY(MAX),
                                    CONCAT(so.[name], so.[schema_id], so.[create_date])
                                   )
                           ) AS [SQLCLR-ConcatStrings],
       HASHBYTES(N'SHA2_256',
                 CONVERT(VARBINARY(MAX),
                         CONCAT(so.[name], so.[schema_id], so.[create_date])
                        )
                ) AS [BuiltIn-ConcatStrings]
FROM sys.objects so;


SELECT so.[object_id],
       SQL#.Util_HashBinary(N'SHA256',
                            CONVERT(VARBINARY(500), so.[name]) + 
                            CONVERT(VARBINARY(500), so.[schema_id]) +
                            CONVERT(VARBINARY(500), so.[create_date])
                           ) AS [SQLCLR-ConcatVarBinaries],
       HASHBYTES(N'SHA2_256',
                 CONVERT(VARBINARY(500), so.[name]) + 
                 CONVERT(VARBINARY(500), so.[schema_id]) +
                 CONVERT(VARBINARY(500), so.[create_date])
                ) AS [BuiltIn-ConcatVarBinaries]
FROM sys.objects so;

    Você pode testar algo mais comparável ao Spooky não-LOB usando:

    CREATE FUNCTION [SQL#].[Util_HashBinary8k]
(@Algorithm [nvarchar](50), @BaseData [varbinary](8000))
RETURNS [varbinary](8000) 
WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SQL#].[UTILITY].[HashBinary];

    Observação: Util_HashBinary usa o algoritmo SHA256 gerenciado que está integrado ao .NET e não deve usar a biblioteca "bcrypt".

    Além desse aspecto da pergunta, existem alguns pensamentos adicionais que podem ajudar nesse processo:

    Pensamento Adicional nº 1 (pré-calcular hashes, pelo menos alguns)

    Você mencionou algumas coisas:

    1. 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.

      e:

    2. Não consigo salvar o valor do hash para a tabela de relatórios. É um CCI que não suporta gatilhos ou colunas computadas

      e:

    3. as tabelas podem ser atualizadas fora do processo ETL

    Parece que os dados nesta tabela de relatórios são estáveis ​​por um período de tempo e só são modificados por esse processo de ETL.

    Se nada mais modificar esta tabela, então realmente não precisamos de um gatilho ou exibição indexada (originalmente pensei que você poderia).

    Como você não pode modificar o esquema da tabela de relatórios, seria possível ao menos criar uma tabela relacionada para conter o hash pré-calculado (e o horário UTC de quando foi calculado)? Isso permitiria que você tivesse um valor pré-calculado para comparar com a próxima vez, deixando apenas o valor de entrada que requer o cálculo do hash. Isso reduziria o número de chamadas para um HASHBYTESou SQL#.Util_HashBinarypela metade. Você simplesmente se juntaria a essa tabela de hashes durante o processo de importação.

    Você também criaria um procedimento armazenado separado que simplesmente atualiza os hashes dessa tabela. Ele apenas atualiza os hashes de qualquer linha relacionada que foi alterada para atual e atualiza o carimbo de data/hora dessas linhas modificadas. Este proc pode/deve ser executado ao final de qualquer outro processo que atualize esta tabela. Ele também pode ser programado para ser executado de 30 a 60 minutos antes do início desse ETL (dependendo de quanto tempo leva para ser executado e quando qualquer um desses outros processos pode ser executado). Ele pode até ser executado manualmente se você suspeitar que pode haver linhas fora de sincronia.

    Notou-se então que:

    são mais de 500 mesas

    Essas muitas tabelas tornam mais difícil ter uma tabela extra por cada uma para conter os hashes atuais, mas isso não é impossível, pois poderia ser script, pois seria um esquema padrão. O script precisaria apenas levar em conta o nome da tabela de origem e a descoberta da(s) coluna(s) PK da tabela de origem.

    Ainda assim, independentemente de qual algoritmo de hash prova ser o mais escalável, ainda recomendo encontrar pelo menos algumas tabelas (talvez haja algumas que sejam MUITO maiores que o restante das 500 tabelas) e configurar uma tabela relacionada para capturar hashes atuais para que os valores "atuais" possam ser conhecidos antes do processo ETL. Mesmo a função mais rápida não pode superar nunca ter que chamá-la em primeiro lugar ;-).

    Pensamento Adicional nº 2 ( VARBINARYem vez de NVARCHAR)

    Independentemente de SQLCLR vs built-in HASHBYTES, eu ainda recomendaria converter diretamente para VARBINARY, pois isso deve ser mais rápido. Concatenar strings não é muito eficiente. E , além de converter valores não string em strings em primeiro lugar, o que requer esforço extra (suponho que a quantidade de esforço varia de acordo com o tipo de base: DATETIMEexigindo mais de BIGINT), enquanto a conversão para VARBINARYsimplesmente fornece o valor subjacente (na maioria dos casos).

    E, de fato, testando o mesmo conjunto de dados que os outros testes usaram, e usando HASHBYTES(N'SHA2_256',...), mostrou um aumento de 23,415% no total de hashes calculado em um minuto. E esse aumento foi por não fazer nada além de usar VARBINARYem vez de NVARCHAR! ? (consulte a resposta do wiki da comunidade para obter detalhes)

    Pensamento Adicional nº 3 (esteja atento aos parâmetros de entrada)

    Testes adicionais mostraram que uma área que afeta o desempenho (sobre esse volume de execuções) são os parâmetros de entrada: quantos e quais tipos.

    A função Util_HashBinary SQLCLR que está atualmente na minha biblioteca SQL# tem dois parâmetros de entrada: um VARBINARY(o valor para hash) e um NVARCHAR(o algoritmo a ser usado). Isso se deve ao meu espelhamento da assinatura da HASHBYTESfunção. No entanto, descobri que, se eu removesse o NVARCHARparâmetro e criasse uma função que fizesse apenas SHA256, o desempenho melhoraria bastante. Suponho que mesmo mudar o NVARCHARparâmetro para INTteria ajudado, mas também suponho que nem mesmo ter o INTparâmetro extra é pelo menos um pouco mais rápido.

    Além disso, SqlBytes.Valuepode ter um desempenho melhor do que SqlBinary.Value.

    Criei duas novas funções: Util_HashSHA256Binary e Util_HashSHA256Binary8k para este teste. Eles serão incluídos na próxima versão do SQL# (sem data definida para isso ainda).

    Também descobri que a metodologia de teste poderia ser ligeiramente melhorada, então atualizei o equipamento de teste na resposta do wiki da comunidade abaixo para incluir:

    1. pré-carregamento dos assemblies SQLCLR para garantir que a sobrecarga do tempo de carregamento não distorça os resultados.
    2. um procedimento de verificação para verificar colisões. Se algum for encontrado, ele exibirá o número de linhas exclusivas/distintas e o número total de linhas. Isso permite determinar se o número de colisões (se houver) está além do limite para o caso de uso especificado. Alguns casos de uso podem permitir um pequeno número de colisões, outros podem não exigir nenhuma. Uma função super-rápida é inútil se não puder detectar alterações no nível de precisão desejado. Por exemplo, usando o equipamento de teste fornecido pelo OP, aumentei a contagem de linhas para 100 mil linhas (originalmente eram 10 mil) e descobri que CHECKSUMregistrou mais de 9 mil colisões, o que é 9% (caramba).

    Pensamento Adicional nº 4 ( HASHBYTES+ SQLCLR juntos?)

    Dependendo de onde está o gargalo, pode até ajudar usar uma combinação de HASHBYTESUDF integrada e SQLCLR para fazer o mesmo hash. Se as funções internas forem restritas de maneira diferente/separadamente das operações SQLCLR, essa abordagem poderá ser mais efetiva do que qualquer uma delas.HASHBYTES ou SQLCLR individualmente. Com certeza vale a pena testar.

    Pensamento Adicional nº 5 (cache de objeto de hash?)

    O cache do objeto do algoritmo de hash, conforme sugerido na resposta de David Browne, certamente parece interessante, então tentei e encontrei os dois pontos de interesse a seguir:

    1. Por qualquer motivo, não parece fornecer muita melhoria de desempenho, se houver. Eu poderia ter feito algo errado, mas aqui está o que eu tentei:

      static readonly ConcurrentDictionary<int, SHA256Managed> hashers =
    new ConcurrentDictionary<int, SHA256Managed>();

[return: SqlFacet(MaxSize = 100)]
[SqlFunction(IsDeterministic = true)]
public static SqlBinary FastHash([SqlFacet(MaxSize = 1000)] SqlBytes Input)
{
    SHA256Managed sh = hashers.GetOrAdd(Thread.CurrentThread.ManagedThreadId,
                                        i => new SHA256Managed());

    return sh.ComputeHash(Input.Value);
}

    2. O ManagedThreadIdvalor parece ser o mesmo para todas as referências SQLCLR em uma consulta específica. Testei várias referências à mesma função, bem como uma referência a uma função diferente, todas as 3 recebendo valores de entrada diferentes e retornando valores de retorno diferentes (mas esperados). Para ambas as funções de teste, a saída foi uma string que incluía a ManagedThreadIdrepresentação em string do resultado do hash. oManagedThreadId valor era o mesmo para todas as referências de UDF na consulta e em todas as linhas. Mas, o resultado do hash foi o mesmo para a mesma string de entrada e diferente para diferentes strings de entrada.

      Embora eu não tenha visto nenhum resultado errôneo em meus testes, isso não aumentaria as chances de uma condição de corrida? Se a chave do dicionário for a mesma para todos os objetos SQLCLR chamados em uma consulta específica, eles compartilharão o mesmo valor ou objeto armazenado para essa chave, certo? A questão é que, mesmo que parecesse funcionar aqui (até certo ponto, novamente não parecia haver muito ganho de desempenho, mas funcionalmente nada quebrou), isso não me dá confiança de que essa abordagem funcionará em outros cenários.

    • 17

  3. Esta não é uma resposta tradicional, mas achei que seria útil postar benchmarks de algumas das técnicas mencionadas até agora. Estou testando em um servidor de 96 núcleos com SQL Server 2017 CU9.

    Many scalability problems are caused by concurrent threads contending over some global state. For example, consider classic PFS page contention. This can happen if too many worker threads need to modify the same page in memory. As code becomes more efficient it may request the latch faster. That increases contention. To put it simply, efficient code is more likely to lead to scalability issues because the global state is contended over more severely. Slow code is less likely to cause scalability issues because the global state isn't accessed as frequently.

    HASHBYTES scalability is partially based on the length of the input string. My theory was to why this occurs is that access to some global state is needed when the HASHBYTES function is called. The easy global state to observe is a memory page needs to be allocated per call on some versions of SQL Server. The harder one to observe is that there's some kind of OS contention. As a result, if HASHBYTES is called by the code less frequently then contention goes down. One way to reduce the rate of HASHBYTES calls is to increase the amount of hashing work needed per call. Hashing work is partially based on the length of the input string. To reproduce the scalability problem I saw in the application I needed to change the demo data. A reasonable worst case scenario is a table with 21 BIGINT columns. The definition of the table is included in the code at the bottom. To reduce Local Factors™, I'm using concurrent MAXDOP 1 queries that operate on relatively small tables. My quick benchmark code is at the bottom.

    Note the functions return different hash lengths. MD5 and SpookyHash are both 128 bit hashes, SHA256 is a 256 bit hash.

    RESULTS (NVARCHAR vs VARBINARY conversion and concatenation)

    In order to see if converting to, and concatenating, VARBINARY is truly more efficient / performant than NVARCHAR, an NVARCHAR version of the RUN_HASHBYTES_SHA2_256 stored procedure was created from the same template (see "Step 5" in BENCHMARKING CODE section below). The only differences are:

    1. Stored Procedure name ends in _NVC
    2. BINARY(8) for the CAST function was changed to be NVARCHAR(15)
    3. 0x7C was changed to be N'|'

    Resulting in:

    CAST(FK1 AS NVARCHAR(15)) + N'|' +

    instead of:

    CAST(FK1 AS BINARY(8)) + 0x7C +

    The table below contains the number of hashes performed in 1 minute. The tests were performed on a different server than was used for the other tests noted below.

    ╔════════════════╦══════════╦══════════════╗
║    Datatype    ║  Test #  ║ Total Hashes ║
╠════════════════╬══════════╬══════════════╣
║ NVARCHAR       ║        1 ║     10200000 ║
║ NVARCHAR       ║        2 ║     10300000 ║
║ NVARCHAR       ║  AVERAGE ║ * 10250000 * ║
║ -------------- ║ -------- ║ ------------ ║
║ VARBINARY      ║        1 ║     12500000 ║
║ VARBINARY      ║        2 ║     12800000 ║
║ VARBINARY      ║  AVERAGE ║ * 12650000 * ║
╚════════════════╩══════════╩══════════════╝

    Looking at just the averages, we can calculate the benefit of switching to VARBINARY:

    SELECT (12650000 - 10250000) AS [IncreaseAmount],
       ROUND(((126500000 - 10250000) / 10250000) * 100.0, 3) AS [IncreasePercentage]

    That returns:

    IncreaseAmount:    2400000.0
IncreasePercentage:   23.415

    RESULTS (hash algorithms and implementations)

    The table below contains the number of hashes performed in 1 minute. For example, using CHECKSUM with 84 concurrent queries resulted in over 2 billion hashes being performed before time ran out.

    ╔════════════════════╦════════════╦════════════╦════════════╗
║      Function      ║ 12 threads ║ 48 threads ║ 84 threads ║
╠════════════════════╬════════════╬════════════╬════════════╣
║ CHECKSUM           ║  281250000 ║ 1122440000 ║ 2040100000 ║
║ HASHBYTES MD5      ║   75940000 ║  106190000 ║  112750000 ║
║ HASHBYTES SHA2_256 ║   80210000 ║  117080000 ║  124790000 ║
║ CLR Spooky         ║  131250000 ║  505700000 ║  786150000 ║
║ CLR SpookyLOB      ║   17420000 ║   27160000 ║   31380000 ║
║ SQL# MD5           ║   17080000 ║   26450000 ║   29080000 ║
║ SQL# SHA2_256      ║   18370000 ║   28860000 ║   32590000 ║
║ SQL# MD5 8k        ║   24440000 ║   30560000 ║   32550000 ║
║ SQL# SHA2_256 8k   ║   87240000 ║  159310000 ║  155760000 ║
╚════════════════════╩════════════╩════════════╩════════════╝

    If you prefer to see the same numbers measured in terms of work per thread-second:

    ╔════════════════════╦════════════════════════════╦════════════════════════════╦════════════════════════════╗
║      Function      ║ 12 threads per core-second ║ 48 threads per core-second ║ 84 threads per core-second ║
╠════════════════════╬════════════════════════════╬════════════════════════════╬════════════════════════════╣
║ CHECKSUM           ║                     390625 ║                     389736 ║                     404782 ║
║ HASHBYTES MD5      ║                     105472 ║                      36872 ║                      22371 ║
║ HASHBYTES SHA2_256 ║                     111403 ║                      40653 ║                      24760 ║
║ CLR Spooky         ║                     182292 ║                     175590 ║                     155982 ║
║ CLR SpookyLOB      ║                      24194 ║                       9431 ║                       6226 ║
║ SQL# MD5           ║                      23722 ║                       9184 ║                       5770 ║
║ SQL# SHA2_256      ║                      25514 ║                      10021 ║                       6466 ║
║ SQL# MD5 8k        ║                      33944 ║                      10611 ║                       6458 ║
║ SQL# SHA2_256 8k   ║                     121167 ║                      55316 ║                      30905 ║
╚════════════════════╩════════════════════════════╩════════════════════════════╩════════════════════════════╝

    Some quick thoughts on all of the methods:

    • CHECKSUM: very good scalability as expected
    • HASHBYTES: scalability issues include one memory allocation per call and a large amount of CPU spent in the OS
    • Spooky: surprisingly good scalability
    • Spooky LOB: the spinlock SOS_SELIST_SIZED_SLOCK spins out of control. I suspect this is a general issue with passing LOBs through CLR functions, but I'm not sure
    • Util_HashBinary: looks like it gets hit by the same spinlock. I haven't looked into this so far because there's probably not a lot that I can do about it:

    spin your lock

    • Util_HashBinary 8k: very surprising results, not sure what's going on here

    Final results tested on a smaller server:

    ╔═════════════════════════╦════════════════════════╦════════════════════════╗
║     Hash Algorithm      ║ Hashes over 11 threads ║ Hashes over 44 threads ║
╠═════════════════════════╬════════════════════════╬════════════════════════╣
║ HASHBYTES SHA2_256      ║               85220000 ║              167050000 ║
║ SpookyHash              ║              101200000 ║              239530000 ║
║ Util_HashSHA256Binary8k ║               90590000 ║              217170000 ║
║ SpookyHashLOB           ║               23490000 ║               38370000 ║
║ Util_HashSHA256Binary   ║               23430000 ║               36590000 ║
╚═════════════════════════╩════════════════════════╩════════════════════════╝

    BENCHMARKING CODE

    SETUP 1: Tables and Data

    DROP TABLE IF EXISTS dbo.HASH_SMALL;

CREATE TABLE dbo.HASH_SMALL (
    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,
    FK16 BIGINT NOT NULL,
    FK17 BIGINT NOT NULL,
    FK18 BIGINT NOT NULL,
    FK19 BIGINT NOT NULL,
    FK20 BIGINT NOT NULL
);

INSERT INTO dbo.HASH_SMALL WITH (TABLOCK)
SELECT RN,
4000000 - RN, 4000000 - RN
,200000000 - RN, 200000000 - RN
, RN % 500000 , RN % 500000 , RN % 500000
, RN % 500000 , RN % 500000 , RN % 500000 
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
FROM (
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.LOG_HASHES;
CREATE TABLE dbo.LOG_HASHES (
LOG_TIME DATETIME,
HASH_ALGORITHM INT,
SESSION_ID INT,
NUM_HASHES BIGINT
);

    SETUP 2: Master Execution Proc

    GO
CREATE OR ALTER PROCEDURE dbo.RUN_HASHES_FOR_ONE_MINUTE (@HashAlgorithm INT)
AS
BEGIN
DECLARE @target_end_time DATETIME = DATEADD(MINUTE, 1, GETDATE()),
        @query_execution_count INT = 0;

SET NOCOUNT ON;

DECLARE @ProcName NVARCHAR(261); -- schema_name + proc_name + '[].[]'

DECLARE @RowCount INT;
SELECT @RowCount = SUM(prtn.[row_count])
FROM   sys.dm_db_partition_stats prtn
WHERE  prtn.[object_id] = OBJECT_ID(N'dbo.HASH_SMALL')
AND    prtn.[index_id] < 2;


-- Load assembly if not loaded to prevent load time from skewing results
DECLARE @OptionalInitSQL NVARCHAR(MAX);
SET @OptionalInitSQL = CASE @HashAlgorithm
       WHEN 1 THEN N'SELECT @Dummy = dbo.SpookyHash(0x1234);'
       WHEN 2 THEN N'' -- HASHBYTES
       WHEN 3 THEN N'' -- HASHBYTES
       WHEN 4 THEN N'' -- CHECKSUM
       WHEN 5 THEN N'SELECT @Dummy = dbo.SpookyHashLOB(0x1234);'
       WHEN 6 THEN N'SELECT @Dummy = SQL#.Util_HashBinary(N''MD5'', 0x1234);'
       WHEN 7 THEN N'SELECT @Dummy = SQL#.Util_HashBinary(N''SHA256'', 0x1234);'
       WHEN 8 THEN N'SELECT @Dummy = SQL#.Util_HashBinary8k(N''MD5'', 0x1234);'
       WHEN 9 THEN N'SELECT @Dummy = SQL#.Util_HashBinary8k(N''SHA256'', 0x1234);'
/* -- BETA / non-public code
       WHEN 10 THEN N'SELECT @Dummy = SQL#.Util_HashSHA256Binary8k(0x1234);'
       WHEN 11 THEN N'SELECT @Dummy = SQL#.Util_HashSHA256Binary(0x1234);'
*/
   END;


IF (RTRIM(@OptionalInitSQL) <> N'')
BEGIN
    SET @OptionalInitSQL = N'
SET NOCOUNT ON;
DECLARE @Dummy VARBINARY(100);
' + @OptionalInitSQL;

    RAISERROR(N'** Executing optional initialization code:', 10, 1) WITH NOWAIT;
    RAISERROR(@OptionalInitSQL, 10, 1) WITH NOWAIT;
    EXEC (@OptionalInitSQL);
    RAISERROR(N'-------------------------------------------', 10, 1) WITH NOWAIT;
END;


SET @ProcName = CASE @HashAlgorithm
                    WHEN 1 THEN N'dbo.RUN_SpookyHash'
                    WHEN 2 THEN N'dbo.RUN_HASHBYTES_MD5'
                    WHEN 3 THEN N'dbo.RUN_HASHBYTES_SHA2_256'
                    WHEN 4 THEN N'dbo.RUN_CHECKSUM'
                    WHEN 5 THEN N'dbo.RUN_SpookyHashLOB'
                    WHEN 6 THEN N'dbo.RUN_SR_MD5'
                    WHEN 7 THEN N'dbo.RUN_SR_SHA256'
                    WHEN 8 THEN N'dbo.RUN_SR_MD5_8k'
                    WHEN 9 THEN N'dbo.RUN_SR_SHA256_8k'
/* -- BETA / non-public code
                    WHEN 10 THEN N'dbo.RUN_SR_SHA256_new'
                    WHEN 11 THEN N'dbo.RUN_SR_SHA256LOB_new'
*/
                    WHEN 13 THEN N'dbo.RUN_HASHBYTES_SHA2_256_NVC'
                END;

RAISERROR(N'** Executing proc: %s', 10, 1, @ProcName) WITH NOWAIT;

WHILE GETDATE() < @target_end_time
BEGIN
    EXEC @ProcName;

    SET @query_execution_count = @query_execution_count + 1;
END;

INSERT INTO dbo.LOG_HASHES
VALUES (GETDATE(), @HashAlgorithm, @@SPID, @RowCount * @query_execution_count);

END;
GO

    SETUP 3: Collision Detection Proc

    GO
CREATE OR ALTER PROCEDURE dbo.VERIFY_NO_COLLISIONS (@HashAlgorithm INT)
AS
SET NOCOUNT ON;

DECLARE @RowCount INT;
SELECT @RowCount = SUM(prtn.[row_count])
FROM   sys.dm_db_partition_stats prtn
WHERE  prtn.[object_id] = OBJECT_ID(N'dbo.HASH_SMALL')
AND    prtn.[index_id] < 2;


DECLARE @CollisionTestRows INT;
DECLARE @CollisionTestSQL NVARCHAR(MAX);
SET @CollisionTestSQL = N'
SELECT @RowsOut = COUNT(DISTINCT '
+ CASE @HashAlgorithm
       WHEN 1 THEN N'dbo.SpookyHash('
       WHEN 2 THEN N'HASHBYTES(''MD5'','
       WHEN 3 THEN N'HASHBYTES(''SHA2_256'','
       WHEN 4 THEN N'CHECKSUM('
       WHEN 5 THEN N'dbo.SpookyHashLOB('
       WHEN 6 THEN N'SQL#.Util_HashBinary(N''MD5'','
       WHEN 7 THEN N'SQL#.Util_HashBinary(N''SHA256'','
       WHEN 8 THEN N'SQL#.[Util_HashBinary8k](N''MD5'','
       WHEN 9 THEN N'SQL#.[Util_HashBinary8k](N''SHA256'','
--/* -- BETA / non-public code
       WHEN 10 THEN N'SQL#.[Util_HashSHA256Binary8k]('
       WHEN 11 THEN N'SQL#.[Util_HashSHA256Binary]('
--*/
   END
+ N'
    CAST(FK1 AS BINARY(8)) + 0x7C +
    CAST(FK2 AS BINARY(8)) + 0x7C +
    CAST(FK3 AS BINARY(8)) + 0x7C +
    CAST(FK4 AS BINARY(8)) + 0x7C +
    CAST(FK5 AS BINARY(8)) + 0x7C +
    CAST(FK6 AS BINARY(8)) + 0x7C +
    CAST(FK7 AS BINARY(8)) + 0x7C +
    CAST(FK8 AS BINARY(8)) + 0x7C +
    CAST(FK9 AS BINARY(8)) + 0x7C +
    CAST(FK10 AS BINARY(8)) + 0x7C +
    CAST(FK11 AS BINARY(8)) + 0x7C +
    CAST(FK12 AS BINARY(8)) + 0x7C +
    CAST(FK13 AS BINARY(8)) + 0x7C +
    CAST(FK14 AS BINARY(8)) + 0x7C +
    CAST(FK15 AS BINARY(8)) + 0x7C +
    CAST(FK16 AS BINARY(8)) + 0x7C +
    CAST(FK17 AS BINARY(8)) + 0x7C +
    CAST(FK18 AS BINARY(8)) + 0x7C +
    CAST(FK19 AS BINARY(8)) + 0x7C +
    CAST(FK20 AS BINARY(8))  ))
FROM dbo.HASH_SMALL;';

PRINT @CollisionTestSQL;

EXEC sp_executesql
  @CollisionTestSQL,
  N'@RowsOut INT OUTPUT',
  @RowsOut = @CollisionTestRows OUTPUT;


IF (@CollisionTestRows <> @RowCount)
BEGIN
    RAISERROR('Collisions for algorithm: %d!!!  %d unique rows out of %d.',
    16, 1, @HashAlgorithm, @CollisionTestRows, @RowCount);
END;
GO

    SETUP 4: Cleanup (DROP All Test Procs)

    DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += N'DROP PROCEDURE [dbo].' + QUOTENAME(sp.[name])
            + N';' + NCHAR(13) + NCHAR(10)
FROM  sys.objects sp
WHERE sp.[name] LIKE N'RUN[_]%'
AND   sp.[type_desc] = N'SQL_STORED_PROCEDURE'
AND   sp.[name] <> N'RUN_HASHES_FOR_ONE_MINUTE'

PRINT @SQL;

EXEC (@SQL);

    SETUP 5: Generate Test Procs

    SET NOCOUNT ON;

DECLARE @TestProcsToCreate TABLE
(
  ProcName sysname NOT NULL,
  CodeToExec NVARCHAR(261) NOT NULL
);
DECLARE @ProcName sysname,
        @CodeToExec NVARCHAR(261);

INSERT INTO @TestProcsToCreate VALUES
  (N'SpookyHash', N'dbo.SpookyHash('),
  (N'HASHBYTES_MD5', N'HASHBYTES(''MD5'','),
  (N'HASHBYTES_SHA2_256', N'HASHBYTES(''SHA2_256'','),
  (N'CHECKSUM', N'CHECKSUM('),
  (N'SpookyHashLOB', N'dbo.SpookyHashLOB('),
  (N'SR_MD5', N'SQL#.Util_HashBinary(N''MD5'','),
  (N'SR_SHA256', N'SQL#.Util_HashBinary(N''SHA256'','),
  (N'SR_MD5_8k', N'SQL#.[Util_HashBinary8k](N''MD5'','),
  (N'SR_SHA256_8k', N'SQL#.[Util_HashBinary8k](N''SHA256'',')
--/* -- BETA / non-public code
  , (N'SR_SHA256_new', N'SQL#.[Util_HashSHA256Binary8k]('),
  (N'SR_SHA256LOB_new', N'SQL#.[Util_HashSHA256Binary](');
--*/
DECLARE @ProcTemplate NVARCHAR(MAX),
        @ProcToCreate NVARCHAR(MAX);

SET @ProcTemplate = N'
CREATE OR ALTER PROCEDURE dbo.RUN_{{ProcName}}
AS
BEGIN
DECLARE @dummy INT;
SET NOCOUNT ON;

SELECT @dummy = COUNT({{CodeToExec}}
    CAST(FK1 AS BINARY(8)) + 0x7C +
    CAST(FK2 AS BINARY(8)) + 0x7C +
    CAST(FK3 AS BINARY(8)) + 0x7C +
    CAST(FK4 AS BINARY(8)) + 0x7C +
    CAST(FK5 AS BINARY(8)) + 0x7C +
    CAST(FK6 AS BINARY(8)) + 0x7C +
    CAST(FK7 AS BINARY(8)) + 0x7C +
    CAST(FK8 AS BINARY(8)) + 0x7C +
    CAST(FK9 AS BINARY(8)) + 0x7C +
    CAST(FK10 AS BINARY(8)) + 0x7C +
    CAST(FK11 AS BINARY(8)) + 0x7C +
    CAST(FK12 AS BINARY(8)) + 0x7C +
    CAST(FK13 AS BINARY(8)) + 0x7C +
    CAST(FK14 AS BINARY(8)) + 0x7C +
    CAST(FK15 AS BINARY(8)) + 0x7C +
    CAST(FK16 AS BINARY(8)) + 0x7C +
    CAST(FK17 AS BINARY(8)) + 0x7C +
    CAST(FK18 AS BINARY(8)) + 0x7C +
    CAST(FK19 AS BINARY(8)) + 0x7C +
    CAST(FK20 AS BINARY(8)) 
    )
    )
    FROM dbo.HASH_SMALL
    OPTION (MAXDOP 1);

END;
';

DECLARE CreateProcsCurs CURSOR READ_ONLY FORWARD_ONLY LOCAL FAST_FORWARD
FOR SELECT [ProcName], [CodeToExec]
    FROM @TestProcsToCreate;

OPEN [CreateProcsCurs];

FETCH NEXT
FROM  [CreateProcsCurs]
INTO  @ProcName, @CodeToExec;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- First: create VARBINARY version
    SET @ProcToCreate = REPLACE(REPLACE(@ProcTemplate,
                                        N'{{ProcName}}',
                                        @ProcName),
                                N'{{CodeToExec}}',
                                @CodeToExec);

    EXEC (@ProcToCreate);

    -- Second: create NVARCHAR version (optional: built-ins only)
    IF (CHARINDEX(N'.', @CodeToExec) = 0)
    BEGIN
        SET @ProcToCreate = REPLACE(REPLACE(REPLACE(@ProcToCreate,
                                                    N'dbo.RUN_' + @ProcName,
                                                    N'dbo.RUN_' + @ProcName + N'_NVC'),
                                            N'BINARY(8)',
                                            N'NVARCHAR(15)'),
                                    N'0x7C',
                                    N'N''|''');

        EXEC (@ProcToCreate);
    END;

    FETCH NEXT
    FROM  [CreateProcsCurs]
    INTO  @ProcName, @CodeToExec;
END;

CLOSE [CreateProcsCurs];
DEALLOCATE [CreateProcsCurs];

    TEST 1: Check For Collisions

    EXEC dbo.VERIFY_NO_COLLISIONS 1;
EXEC dbo.VERIFY_NO_COLLISIONS 2;
EXEC dbo.VERIFY_NO_COLLISIONS 3;
EXEC dbo.VERIFY_NO_COLLISIONS 4;
EXEC dbo.VERIFY_NO_COLLISIONS 5;
EXEC dbo.VERIFY_NO_COLLISIONS 6;
EXEC dbo.VERIFY_NO_COLLISIONS 7;
EXEC dbo.VERIFY_NO_COLLISIONS 8;
EXEC dbo.VERIFY_NO_COLLISIONS 9;
EXEC dbo.VERIFY_NO_COLLISIONS 10;
EXEC dbo.VERIFY_NO_COLLISIONS 11;

    TEST 2: Run Performance Tests

    EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 1;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 2;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 3; -- HASHBYTES('SHA2_256'
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 4;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 5;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 6;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 7;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 8;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 9;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 10;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 11;
EXEC dbo.RUN_HASHES_FOR_ONE_MINUTE 13; -- NVC version of #3


SELECT *
FROM   dbo.LOG_HASHES
ORDER BY [LOG_TIME] DESC;

    VALIDATION ISSUES TO RESOLVE

    While focusing on the performance testing of a singular SQLCLR UDF, two issues that were discussed early on were not incorporated into the tests, but ideally should be investigated in order to determine which approach meets all of the requirements.

    1. The function will be executed twice per each query (once for the import row, and once for the current row). The tests so far have only referenced the UDF one time in the test queries. This factor might not change the ranking of the options, but it shouldn't be ignored, just in case.

    2. In a comment that has since been deleted, Paul White had mentioned:

      One downside of replacing HASHBYTES with a CLR scalar function - it appears that CLR functions cannot use batch mode whereas HASHBYTES can. That might be important, performance-wise.

      So that is something to consider, and clearly requires testing. If the SQLCLR options do not provide any benefit over the built-in HASHBYTES, then that adds weight to Solomon's suggestion of capturing existing hashes (for at least the largest tables) into related tables.

    • 12

  4. You can probably improve the performance, and perhaps the scalability of all the .NET approaches by pooling and caching any objects created in the function call. EG for Paul White's code above:

    static readonly ConcurrentDictionary<int,ISpookyHashV2> hashers = new ConcurrentDictonary<ISpookyHashV2>()
public static byte[] SpookyHash([SqlFacet (MaxSize = 8000)] SqlBinary Input)
{
    ISpookyHashV2 sh = hashers.GetOrAdd(Thread.CurrentThread.ManagedThreadId, i => SpookyHashV2Factory.Instance.Create());

    return sh.ComputeHash(Input.Value).Hash;
}

    SQL CLR discourages and tries to prevent using static/shared variables, but it will let you use shared variables if you mark them as readonly. Which, of course, is meaningless as you can just assign a single instance of some mutable type, like ConcurrentDictionary.

    • 7

relate perguntas