TDS Encryption Overhead

Some cipher suites apparently cause a massive overhead, at least when used by TDS.

For example, a benchmark described in this blog post runs 25% longer with TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384, while TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 introduces only a 3% overhead compared to a non-encrypted connection. Unsurprisingly, all this extra time is being spent on CPU.

But the tricky part is that the allowed algorithms and their priority order are defined by the operating system and can be modified, for instance, with a new OS build. In other words, the DBAs can be completely unaware of such changes. Therefore, it’s a good practice to measure the encryption overhead whenever the priority order changes. For that reason, it would be also good to monitor changes of the registry entries for the priority order. These values are burried under the subtree “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Cryptography\Providers”.

However, it’s not a server alone who picks the algorithm. That’s rather negotiated with the client during SSL handshake. So, before measuring the overhead you have to be sure which cipher suite is effectively being used. Unfortunately, SQL Server doesn’t expose this information. But we can get it by sniffing the network traffic during the SSL handshake.

The benchmark itself consists of measuring the following TSQL script’s execution time:

set nocount off

DECLARE @cnt int = 0 ;

BEGIN
    WHILE @cnt < 100000000
	BEGIN
	   SET @cnt = @cnt + 1;
	END;
END

GO

This script exploits an oddity – a simple TSQL loop causes a TCP storm to the client, which makes it a terrible performer but an excellent TDS encryption benchmark.

References:

• Microsoft Corp. (‎2018, May ‎16‎). Manage Transport Layer Security (TLS)
• Microsoft Corp. (‎2018, May ‎31‎). Cipher Suites in TLS/SSL (Schannel SSP)

Related blog posts:

• Which Cipher Suite is being used for TDS Encryption?)
• TCP Storm in SQL Server
• Excessive Context Switching due to TDS Encryption

Update March 11 2019:
@sqL_handLe kindly pointed out that the massive TCP messages will be generated only when “set nocount off”. Therefore, I updated the test script to explicitly set this.