HASHBYTES Performance In SQL Server

Joe Obbish takes a look at how HASHBYTES doesn’t scale well:

The purpose of the MAX aggregate is to limit the size of the result set. This is a cheap aggregate because it can be implemented as a stream aggregate. The operator can simply keep the maximum value that it’s found so far, compare the next value to the max, and update the maximum value when necessary. On my test server, the query takes about 20 seconds. If I run the query without the HASHBYTES call it takes about 3 seconds. That matches intuitively what I would expect. Reading 11 million rows from a small table out of the buffer pool should be less expensive than calculating 11 million hashes.

From my naive point of view, I would expect this query to scale well as the number of concurrent queries increases. It doesn’t seem like there should be contention over any shared resources, so as long as every query gets on its own scheduler I wouldn’t expect a large degradation in overall run time as the number of queries increases.

Joe’s research isn’t complete, but he does have a conjecture as to why HASHBYTES doesn’t scale well.  That said, the most interesting thing in the post to me was to see Microsoft potentially using bcrypt under the covers for HASHBYTES calculation—if that’s really the case, there actually is a chance that sometime in the future, we’d be able to generate cryptographically secure hashes within SQL Server rather than the MD5, SHA1, and SHA2 hashes we have today.

Related Posts

The Prevalence of Persistent XSS

Adrian Colyer has a review of a security-minded paper: Does your web application make use of local storage? If so, then like many developers you may well be making the assumption that when you read from local storage, it will only contain the data that you put there. As Steffens et al. show in this […]

Read More

Using Windows Authentication on Non-Windows Devices

Drew Furgiuele shows us how to connect to SQL Server using Windows Authentication if you’re not coming from a Windows device: SQL Server supports different kinds of authentication mechanisms and protocols: the older NTLM protocol, and Kerberos. A lot of people cringe when you mention Kerberos because, well, Kerberos is hard. It’s arcane, it’s complex, and it’s […]

Read More

Categories

June 2018
MTWTFSS
« May Jul »
 123
45678910
11121314151617
18192021222324
252627282930