Common troubleshooting steps for high resource utilisation on SQL - which are behind NLB · Codebase

Where does it relate to

• SQL Server is using high Memory / CPU resources constantly.

• Load balancing is uneven across the EC2 instances

• Poor OS performance due to high resource utilisation

Scenario

Wehad 3 EC2 instances, installed with MS SQL Servers, spread across 2 AvailabilityZones
Trafficwas being distributed through AWS Network Load Balancer
Wenoticed, one of the instance was getting higher requests than the others.Resource utilisation was high on a particular instance

‍

Generic troubleshooting steps

The issue could be either with NLB configuration or with SQL + EC2 instance configuration.

‍

Verify EC2 Configuration

Check all the EC2 instances are of same instance-type and family.
Try recreating the instance of the same family - if not
CPU / memory configurations are same

‍

Verify SQL Server configuration

Verify the configuration, if you have manually configured SQL server on the OS. If the image was pre-loaded with the instance - then the configuration should remain constant across all the servers

• Logon to the servers.

• Open SQL Server Configuration Manager

• Under SQL Native Client Configuration – verify the configuration is same across all the nodes

• Under SQL Server Network Configuration – verify correct protocols for MSSQLServers are enabled.

‍

Verify Processor and Memory configuration for SQL services

Connect the DB through SSMS
Right click the Database >>select Properties
Click Memory - Verify Minimum andMaximum configured values.
ClickProcessor - Verify CPU minimum and maximum values

## This defines amount of resources allocated to the DB. If the min and max are equal, the DB will never flush the resources - causing performance issues

‍

Verify Load Balancer configuration

Cross-Zone Load Balancing : When the EC2 instances are unevenly distributed amongs multiple availability zone - make sure this option is enabled.

When enabled - it makes sure the traffic is distributed evenly amongst EC2 instances and not amongst Availability Zoneso
Reference :

https://docs.aws.amazon.com/elasticloadbalancing/latest/userguide/how-elastic-load-balancing-works.html#cross-zone-load-balancing

‍

Client DNS Cache

If TTL is configured high, clients cache the DNS entries locally. Hence they directly connect the Node with cached IP.
Try flushing the local DNS entries
For Linux clients using nscd for DNS caching, run one of the following commands:

sudo /etc/init.d/nscd restart

# service nscd restart

# service nscd reload

‍

‍For Linux clients using dnsmasq for DNS caching, run one of the following commands

$ sudo /etc/init.d/dnsmasq restart

# service dnsmasq restart

‍

For Linux clients using BIND for DNS caching, run one of the following commands:

# /etc/init.d/named restart

# rndc restart

# rndc exec

‍

For Windows clients, run the following command:

ipconfig /flushdns

In case if you are using lambda function - this is not relevant

‍

Check the configuration of sticky sessions

If you use duration-based session stickiness, configure an appropriate cookie expiration time for your specific use case. For more information, see:

Sticky Sessions (Application Load Balancers)
Duration-Based Session Stickiness (Classic Load Balancers)

If you set session stickiness from individual applications, use session cookies instead of persistent cookies where possible. For more information, see Application-Controlled Session Stickiness (Classic Load Balancers).

‍