SQL Server – Scalable Way to Simulate HASHBYTES Using SQL CLR Scalar Function

etlhashingsql serversql-clrsql-server-2016

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded.

The comparison is based on the unique key of the table and some kind of hash of all of the other columns. We currently use HASHBYTES with the SHA2_256 algorithm and have found that it does not scale on large servers if many concurrent worker threads are all calling HASHBYTES.

Throughput measured in hashes per second does not increase past 16 concurrent threads when testing on a 96 core server. I test by changing the number of concurrent MAXDOP 8 queries from 1 – 12. Testing with MAXDOP 1 showed the same scalability bottleneck.

As a workaround I want to try a SQL CLR solution. Here is my attempt to state the requirements:

The function must be able to participate in parallel queries
The function must be deterministic
The function must take an input of an NVARCHAR or VARBINARY string (all relevant columns are concatenated together)
The typical input size of the string will be 100 – 20000 characters in length. 20000 is not a max
The chance of a hash collision should be roughly equal to or better than the MD5 algorithm. CHECKSUM does not work for us because there are too many collisions.
The function must scale well on large servers (throughput per thread should not significantly decrease as the number of threads increases)

For Application Reasons™, assume that I cannot save off the value of the hash for the reporting table. It's a CCI which doesn't support triggers or computed columns (there are other problems as well that I don't want to get into).

What is a scalable way to simulate HASHBYTES using a SQL CLR function? My goal can be expressed as getting as many hashes per second as I can on a large server, so performance matters as well. I am terrible with CLR so I don't know how to accomplish this. If it motivates anyone to answer, I plan on adding a bounty to this question as soon as I am able. Below is an example query which very roughly illustrates the use case:

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);

To simplify things a bit, I'll probably use something like the following for benchmarking. I'll post results with HASHBYTES on Monday:

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);

Best Answer

Since you're just looking for changes, you don't need a cryptographic hash function.

You could choose from one of the faster non-cryptographic hashes in the open-source Data.HashFunction library by Brandon Dahler, licensed under the permissive and OSI approved MIT license. SpookyHash is a popular choice.

Example implementation

Source Code

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;
    }
}

The source provides two functions, one for inputs of 8000 bytes or less, and a LOB version. The non-LOB version should be significantly quicker.

You might be able to wrap a LOB binary in COMPRESS to get it under the 8000 byte limit, if that turns out to be worthwhile for performance. Alternatively, you could break the LOB up into sub-8000 byte segments, or simply reserve the use of HASHBYTES for the LOB case (since longer inputs scale better).

Pre-built code

You can obviously grab the package for yourself and compile everything, but I built the assemblies below to make quick testing easier:

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

T-SQL functions

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

Usage

An example use given the sample data in the question:

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)
    );

When using the LOB version, the first parameter should be cast or converted to varbinary(max).

Execution plan

Safe Spooky

The Data.HashFunction library uses a number of CLR language features that are considered UNSAFE by SQL Server. It is possible to write a basic Spooky Hash compatible with SAFE status. An example I wrote based on Jon Hanna's SpookilySharp is below:

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

Related Solutions

Sql-server – Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way

Overall, no, you did not do anything wrong with your .NET code. There is a minor technical issue, but we will get to that in a moment.

I was able to reproduce this (both error and non-error scenarios) in SQL Server 2008 R2 RTM. At first, in the "non-error" scenario, I was getting another error for:

specified cast is invalid

This error was a result of the query passing back an INT and the C# code attempting to cast it to a bool. I changed the query to be:

... then CONVERT(BIT, 1) else CONVERT(BIT, 0) end ...

That forces the datatype of the value to be returned to be something that can be cast into bool. This was the "minor error" that I was referring to, and I consider it "minor" and not really an error because I suspect your code originally attempted to cast into int and returned a SqlInt32 since you claim that passing in an empty string allowed it to return successfully. There is no way this would ever return successfully, even with an empty string instead of NULL for the input value, if your code was attempting to cast to bool and return SqlBoolean. And yes, I could have changed the cast to be (int) and the return type to be SqlInt32, but this was less of a structural change.

I tested several types of changes: removing the execution, removing the @d parameter and hard-coding the null into the query but still having the SqlParameter defined, removing the @d parameter and the SqlParameter, etc. What it finally came down to is this: merely the existence of a SqlParameter of a LOB type (i.e. XML, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) ), even if unused in the query, caused the "severe error" when passing in a NULL. However, if any of those MAX types were changed to be their maximum non-MAX lengths (i.e. 8000, 4000, and 8000 respectively), then there was no error when passing in a NULL. Hence the following reduced code should be enough to reproduce the error when passing in a NULL to the SQLCLR scalar function:

using (var cmd = new SqlCommand(@"SELECT 1;", c))
{
    var p = cmd.Parameters.Add("@d", SqlDbType.NVarChar, (int)SqlMetaData.Max);

    cmd.ExecuteScalar();
}

Now, I was not able to reproduce the error with the original code in SQL Server 2012 SP3 or SQL Server 2014 SP1 :-). Therefore, I suspect that this was an issue with CLR 2.0 and/or the .NET Framework versions associated with CLR 2.0 (i.e. "2.0", "3.0", and "3.5"). OR, another aspect to consider is that it with this failing ( declare @res bit; set @res = testing.TestTest(null); ) while this works ( declare @res bit; set @res = (select testing.TestTest(null)); ) that could point to a problem with the Query Optimizer and not with the CLR version. Or maybe it is a combination of the two. Or perhaps it is Descartes's pesky Evil Demon. Either way. ^{( ** please see comment at the end )}

If you are able to move up to SQL Server 2012 or newer, then the problem should "magically" disappear. BUT, if stuck on SQL Server 2008 / 2008 R2, I was able to work around the issue by declaring the parameter to be a non-MAX type if the input is NULL, which should be fine since there won't be any risk of truncation when passing through a NULL. And, in the case of the input parameter datatype of the T-SQL object being called being XML, it still works to define the parameter datatype in the .NET code as NVARCHAR(4000) as it will implicitly convert that into XML when called. The following code worked for me in SQL Server 2008 R2 when passing in a NULL:

SqlParameter _XmlData;

if (Data.IsNull)
{
    // Parameter can't be a LOB type if input is NULL
    _XmlData = new SqlParameter("@d", SqlDbType.NVarChar, 4000);
    _XmlData.Value = DBNull.Value;
}
else
{
    _XmlData = new SqlParameter("@d", SqlDbType.Xml);
    _XmlData.Value = Data.Value;
}

cmd.Parameters.Add(_XmlData);

** @MartinSmith left this illuminating comment:

Specifically the error seems to be "Access violation reading location 0x0000000000000000" inside sqlservr.exe!CClrXvarProxy::EnsureBufferInit so looks like a null pointer bug but googling those method names doesn't return any KB article.

SQL Server – Adding Multiple CLR Functions in the Same Assembly

There is nothing wrong with your code, at least not the code that is posted in the question. I copied and pasted it into Visual Studio 2015 and it compiled and deployed without errors. I thought perhaps you might have an overloaded method for CanReplaceMultiWord since those are not allowed, but I tested that scenario by creating an empty method of nearly the same signature, just changing SqlString into SqlChars. It also compiled and deployed just fine but this time did not create dbo.CanReplaceMultiWord. I tried creating manually via your CREATE FUNCTION statement but got an error about there being "more than one method" for CanReplaceMultiWord and not being able to overload; I did not get anything about "cannot find method".

So, I think perhaps you might, somehow, have a non-printable character (control character maybe?) in that method name in the CREATE FUNCTION statement. Try deleting that EXTERNAL NAME AS line and re-typing it. Although, I did copy and paste your code from the "edit" page so if you copied and pasted into the question, then it usually gets copied there as well (unless their either don't or maybe get cleaned on the way into the system, not sure). Still, I still find it most likely that something is wrong with the method name in the CREATE FUNCTION statement (somehow, since it works on my system) since if you have the wrong Assembly name, you get:

Assembly 'xxxxxxxxxx' was not found in the SQL catalog of database 'your_db_name'.

And the correct Assembly name but wrong Class name returns:

Could not find Type 'yyyyyyyyyyy' in assembly 'xxxxxxxxxx'.

Also, I highly recommend using Visual Studio to do the compilation. The "Community Edition" is free, so no (good) reason to not be using it. There are several options (many really) for passing into csc.exe that it handles nicely. Even if you don't use it for final deployment (I don't), it is great for development and testing.

P.S. One benefit of using VS is that it deploys by converting the DLL bytes into a hex string so that it can do CREATE ASSEMBLY FROM 0x4D...., which is not only more portable, but you can't possibly have the wrong version of the Assembly (assuming you are using the most recent deploy / create script).

SIDE NOTE:

You want to be careful when using the RegexOptions.Compiled option with object-based RegEx due to the compiled definition getting discarded once the method goes out of scope. I could be reading your code wrong, but it looks like the RegEx is really only executed once, which is a bad case for using RegexOptions.Compiled. You either want to get rid of RegexOptions.Compiled or switch to the static method (unless, of course, I am misreading the code ;-). Please see the MSDN page for Regular Expression Options for more details.