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

Common troubleshooting steps for high resource utilisation on SQL - which are behind NLB
Rohan Gupta
Common troubleshooting steps for high resource utilisation on SQL - which are behind NLB

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


  • 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 :


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).





Back to blog