Scaling Software Applications to Handle Increased Load: A Case Study

Recently, I was called into a critical meeting to address an outage situation for one of our customers who needed to scale their customer-hosted software product usage by 6X (number of users) to deal with the hurricane aftermath in the Gulf Shores region. The application was freezing up due to increased load and database deadlocks. Here’s how we resolved the issue.

The Initial Problem

The root cause of the problem was twofold:1. Application Freezing: The application was experiencing significant slowdowns due to the sudden increase in load.2. Database Deadlocks: The increased load was causing deadlocks in the database, further exacerbating the issue.

Understanding the Infrastructure

After a 30–45 minute briefing and troubleshooting session with our technical team and the customer’s IT and back-office Ops teams, I learned the following:- The application and the database were hosted on separate virtual machines (VMs).- The customer’s IT manager had already increased CPU and memory allocation to both the database and application VMs.

Key Insights and Solutions

1. CPU Configuration:- Issue: The SQL Server was only seeing 50% of the available CPU, which was arranged in an 8x2 configuration.- Solution: We changed the configuration from 8x2 to 4x4 during an overnight change management window. This adjustment allowed SQL Server to see and utilize all the available processors.

SQL Commands Used:

— To see the number of logical CPUs available to SQL ServerSELECT cpu_count AS [Logical CPU Count]FROM sys.dm_os_sys_info;

— To see the CPU utilization by SQL ServerSELECT scheduler_id, cpu_id, status, is_onlineFROM sys.dm_os_schedulersWHERE status = ‘VISIBLE ONLINE’;

2. Memory Utilization:- Issue: Despite having increased compute power, SQL Server was still witnessing deadlocks due to significant pagination. Memory utilization was only 25%-30% of the allocated memory.- Solution: I recommended increasing the ‘min memory per query’ from the default 1 MB to 2 MB and then to 8 MB. This change improved performance significantly.

Steps to Adjust Memory Settings Using SQL Server Management Studio (SSMS):1. Open SSMS and connect to your SQL Server instance.2. Right-click on the server name in Object Explorer and select “Properties.”3. In the Server Properties window, select the “Memory” page from the left-hand side.4. In the “Query Memory” section, adjust the “Minimum memory per query (KB)” option.5. Set the desired value and click “OK” to save the changes.

Purpose: Setting a minimum memory per query ensures that every query has enough memory to be executed efficiently, preventing memory starvation for queries that might otherwise not get enough memory to perform optimally.

Lessons Learned

From this experience, I reinforced a crucial lesson in building and maintaining SaaS platforms: optimizing algorithms and queries is essential, but so is managing the infrastructure. For medium-sized software applications or platforms, the four big parameters to account for on the infrastructure side are:1. Compute/CPU2. Memory/RAM3. I/O4. Network Connectivity and Latency

Effective management of these parameters can save tens or even hundreds of thousands of dollars in expensive application or platform reengineering. While these considerations might seem obvious for cloud apps, they are equally critical for apps hosted on VMs or bare metal servers.

Conclusion

By reconfiguring a few values in the database server, without any changes to the code, we were able to resolve a major issue our customer faced during a critical time. This case highlights the importance of understanding and managing infrastructure components effectively to ensure software applications can scale and perform under increased load.