Why Apps Slow Down with 1,000 Users?

Scenario

The application works well with one user but becomes slow with 1,000 concurrent users. This usually indicates blocking, resource contention, connection-pool exhaustion, inefficient queries, or insufficient capacity.

Troubleshooting Steps

1. Reproduce with load testing

Gradually test with 10, 100, 500 and 1,000 users.

Measure:

• Average and P95 response time

• Requests per second

• Error and timeout rates

• SQL Server CPU and memory

• Active connections

• Blocking and deadlocks

2. Check blocking and long transactions

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    cpu_time,
    total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID;

Investigate:

• Long-running transactions

• Uncommitted transactions

• Large updates or deletes

• External calls performed inside transactions

Keep transactions short.

3. Analyze SQL Server waits

Important wait types:

4. Find expensive and frequently executed queries

Check Query Store and execution plans for:

• Table/index scans

• High logical reads

• Missing indexes

• Key lookups

• Incorrect row estimates

• Sort or hash spills

• Parameter sniffing

• N+1 database calls

A query that is fast individually may still overload SQL Server when executed thousands of times.

5. Check connection pooling

Investigate:

• Connections not properly disposed

• Data readers left open

• Transactions not committed or rolled back

• Requests waiting for a pooled connection

• Too many simultaneous database calls

Increasing the pool size alone may send more traffic to an already overloaded database.

6. Check server resources

Monitor:

• CPU

• Memory and memory grants

• Data-file latency

• Transaction-log latency

• TempDB usage

• Network latency

High concurrency may expose resource limitations that are invisible with one user.

7. Check application behavior

Look for:

• Synchronous blocking such as .Result and .Wait()

• Excessive retries

• Global application locks

• Repeated database calls

• Missing caching

• Unbounded parallel requests

For example, 1,000 requests with three retries can create up to 4,000 attempts.

8. Apply targeted solutions

Depending on the root cause:

• Add or improve indexes

• Optimize high-frequency queries

• Reduce transaction duration

• Fix connection leaks

• Remove N+1 queries

• Use asynchronous database calls

• Cache suitable reference data

• Add pagination

• Add rate or concurrency limits

• Scale application or database resources if genuinely required

Interview Answer

I would reproduce the problem through controlled load testing and determine when performance begins to degrade. Then I would check SQL Server blocking, wait statistics, expensive and frequently executed queries, execution plans, connection-pool usage, CPU, memory, storage, TempDB and transaction-log latency.

I would also inspect the application for connection leaks, synchronous blocking, N+1 queries and retry storms. After identifying the bottleneck, I would apply a targeted fix such as query optimization, indexing, shorter transactions, caching, throttling or infrastructure scaling, and validate it with the same load test.

Key Points

• Good single-user performance does not guarantee scalability.

• Check blocking and connection-pool exhaustion first.

• Optimize frequently executed queries.

• Keep transactions short.

• Retries can worsen overload.

• Validate the solution using repeatable load tests.