The relationship between TCP retransmissions and SQL Server network wait time

Recently I've been looking into a application performance problem whose primary symptom was that network wait time was almost half of the wait time recorded by SQL Server, which is well beyond what is normally observed.

In addition to the application performance problems, it was observed that the transfer of backup files during database restore operations was also slow.  Subsequently, an update for the server's network adapter was located on the vendor's support site.  And after the network adapter update was applied to the server, I found the problem with high network wait time on SQL Server suddenly disappeared.  The network wait time was now a small percentage (1.5%) of total database wait time, which resulted in the application performing about twice as fast as it had before.

   

The problem with the network adapter caused client applications to retransmit packets when communicating with the database server.  The database server was dropping packets, but retransmits at the TCP layer kept the connection intact, which explains the high network wait time reported by SQL Server.  In conclusion, if high network wait time is observed on SQL Server it can be helpful to check whether TCP retransmits are occurring between the database server and its clients.

Intermittent SSL failures when using WCF endpoints with separate ports on the same load balancer

I recently had to troubleshoot an intermittent client authentication failure when trying to access services through an F5 load balancer.  These services are accessed on the same load balancer, but on different ports (e.g. 443 and 17433):

<endpoint address="https://loadbalancer:443/service1" bindingConfiguration="bigip" behaviorConfiguration="clientcert" "/>

<endpoint address="https://loadbalancer:17443/service2" bindingConfiguration="bigip" behaviorConfiguration="clientcert" />

The security on these services is SSL mutual authentication using a client certificate:

<binding name="bigip">
   <httpsTransport requireClientCertificate="true" />
</binding>

<behavior name="clientcert">
   <clientCredentials>
     <clientCertificate findValue="92972EB......" >
   </clientCredentials>
</behavior>

Intermittently SSL negotiation with the load balancer would fail with the error:

System.ServiceModel.Security.SecurityNegotiationException: Could not establish secure channel for SSL/TLS with authority. ---> System.Net.WebException: The request was aborted: Could not create SSL/TLS secure channel

Seeking more information on the cause of this failure I configured network tracing, which showed that the negotiated SSL credentials were cached and re-used within the worker process, but for the wrong port:

System.Net Information: 0 : [3352] SecureChannel#46751003 - Certificate is of type X509Certificate2 and contains the private key.
System.Net Information: 0 : [3352] Using the cached credential handle.
System.Net Information: 0 : [3352] InitializeSecurityContext(credential = System.Net.SafeFreeCredential_SECURITY, context = (null), targetName = pharmacy, inFlags = ReplayDetect, SequenceDetect, Confidentiality, AllocateMemory, InitManualCredValidation)

The conclusion that one must draw then is that WCF/.NET makes no distinction between separate ports on the same load balancer when checking for cached SSL negotiation credentials.  In fact, WCF only uses the certificate thumbprint and the server/load balancer name as a unique key to check for cached credentials.  So one solution to this problem is to configure WCF to use a different client certificate for each port on the load balancer:

<behavior name="clientcert443">
  <clientCredentials>
    <clientCertificate findValue="92972EB......"/>
  </clientCredentials>
</behavior>

<behavior name="clientcert17443">
  <clientCredentials>
    <clientCertificate findValue="0DB645D......"  />
  </clientCredentials>
</behavior>

<endpoint address="https://loadbalancer:443/service1" bindingConfiguration="bigip" behaviorConfiguration="clientcert443 "/>

<endpoint address="https://loadbalancer:17443/service2" bindingConfiguration="bigip" behaviorConfiguration="clientcert17433" />