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

SQL Server Agent Security

Claudio Silva explains how you can provide secure access to manage SQL Agent jobs: It is common having services accounts that are job owners so they can run within the proper context. In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the […]

Read More

Storing Passwords in the Database

Randolph West explains the problems with storing passwords in the database and explains the best alternative: If you are storing passwords in a database, you should stop doing that immediately. We, as software developers and data professionals, should never know what passwords our customers are using. The same goes for most sensitive data: we technical […]

Read More

Categories

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