Interview answer

A database can become a bottleneck when its available resources or processing capacity are insufficient to handle the workload generated by application servers.

Even if we add multiple ASP.NET Core instances, they may all send queries to the same database. This increases database CPU utilization, memory pressure, disk I/O, lock contention, and connection usage.

For example:

                 Load Balancer
                /      |      \
               ▼       ▼       ▼
             API 1    API 2    API 3
                \      |      /
                 \     |     /
                  ▼    ▼    ▼
                  SQL Server
                   CPU: 100%

Adding more API instances won't necessarily improve performance because all instances depend on the same overloaded database.

How would you solve it?

I'd first identify the actual bottleneck using database metrics and query execution plans. Depending on the findings, I would:

1. Optimize expensive SQL queries.
2. Add appropriate indexes.
3. Reduce unnecessary database calls.
4. Introduce caching for frequently accessed data.
5. Use read replicas for suitable read-heavy workloads.
6. Consider partitioning or sharding if the workload or data volume justifies it.

Key interview point: Always identify the bottleneck before selecting a scaling strategy.

Interview answer

SQL databases are generally relational databases that organize data into tables with defined schemas, relationships, and constraints. They support SQL queries and commonly provide transactions with ACID guarantees.

NoSQL is a broad category that includes document, key-value, wide-column, and graph databases. These systems offer different data models and consistency, transaction, and scaling capabilities.

Example

For an order-management system:

Customers
   │
   └── Orders
          │
          └── OrderItems

A relational database is a natural choice because these entities have relationships and transactional requirements.

For a distributed session store:

sessionId → sessionData

A key-value database such as Redis could be appropriate.

Important follow-up: Is NoSQL always faster than SQL?

No. Performance depends on the database implementation, data model, query patterns, workload, consistency requirements, and configuration.

Remember: Choose the database based on requirements and access patterns, not simply because one category is supposedly faster.

Interview answer

I'd choose SQL Server when the application has relational data, complex queries, transactional requirements, constraints, and a need for reliable consistency across related records.

For example, consider an order-processing application involving customers, orders, inventory, and payments.

A transaction may need to update multiple related records together. SQL Server supports ACID transactions and relational constraints that help maintain data integrity.

Example:

BEGIN TRANSACTION;

UPDATE Inventory
SET Quantity = Quantity - 1
WHERE ProductId = 100
  AND Quantity > 0;

-- Application must verify that exactly one row was updated.
-- Otherwise, roll back.

COMMIT TRANSACTION;

In a real implementation, the application must check the affected row count and roll back if the update did not succeed. Payment processing may also require idempotency and coordination with external services.

When might NoSQL be a better choice?

• Large-scale key-based lookups.
• Flexible document structures.
• High-volume event ingestion.
• Distributed workloads with specific access patterns.
• Workloads where a particular NoSQL database's scaling and consistency model is appropriate.

Strong conclusion

"I wouldn't choose SQL or NoSQL based on popularity. I'd evaluate the data relationships, query patterns, transaction boundaries, consistency needs, throughput, and operational cost. For a relational order-processing system, SQL Server would often be my starting point."

Interview answer

A database index is a data structure that helps the database locate records efficiently without scanning every row in a table.

Suppose we have a table containing millions of users:

SELECT *
FROM Users
WHERE Email = 'user@example.com';

Without a suitable index, SQL Server may need to scan many rows to find the matching email.

With an appropriate index:

CREATE INDEX IX_Users_Email
ON Users(Email);

SQL Server can use the index to locate matching records more efficiently, depending on the query plan and selectivity.

Conceptually:

Query: Find user by email

Index on Email

Locate the matching key

Matching row(s)

Common index types in SQL Server

• Clustered index: Organizes the table's data rows according to the clustered index key.
• Nonclustered index: Maintains a separate index structure with keys and row locators.
• Composite index: Uses multiple columns, with column order affecting which queries can benefit.
• Unique index: Enforces uniqueness of indexed key values.

Follow-up: Should every column have an index?

No. Indexes consume storage and add overhead to inserts, updates, and deletes. I'd create indexes based on frequently executed queries, filtering, joins, sorting, and measured execution plans.

Interview answer

Indexes improve certain read operations but introduce storage and maintenance costs.

Every time an indexed column changes, the database may need to update the corresponding index structure.

Too many indexes can cause:

1. Slower writes: Inserts, updates, and deletes may need to maintain multiple indexes.
2. More storage: Indexes consume disk space and potentially memory.
3. Maintenance overhead: Indexes may require maintenance and statistics updates.
4. Query-planning complexity: Additional indexes can create more possible access paths without necessarily improving a query.
5. Cache pressure: Index pages can compete for memory with other useful data.

Example

Suppose an orders table receives thousands of inserts per second.

Orders table
   ├── Index on CustomerId
   ├── Index on OrderDate
   ├── Index on Status
   ├── Index on Amount
   └── Index on another column

If several indexes aren't useful to actual queries, they may add overhead without providing meaningful benefits.

How would you decide which indexes to retain?

I'd examine:

• Query execution plans.
• Query frequency and duration.
• Index usage statistics.
• Write volume.
• Existing clustered and composite indexes.

Interview takeaway: Indexing is a trade-off between read performance, write performance, and storage cost.

Interview answer

Database replication involves maintaining copies of data on multiple database instances or nodes.

A common architecture has a primary database that accepts writes and one or more replicas that receive changes.

Primary Database

Accepts writes

Replicate changes

Replica 1

Replica 2

Replication can provide:

• Redundancy and improved availability.
• Read scaling, when replicas are used for reads.
• Disaster recovery options.
• Additional copies for certain reporting workloads.

Important distinction

Replication does not automatically guarantee zero data loss or automatic failover. Those depend on the replication mode, failure-detection mechanism, configuration, and recovery strategy.

Follow-up: Does replication improve write performance?

Not necessarily. In a primary-replica setup, writes generally still go through the primary, which must also handle replication work. Replication primarily helps with redundancy and, when appropriately configured, read scaling.

Interview answer

A read replica is a copy of a database that can serve read queries while the primary database typically handles writes.

It's particularly useful when an application has significantly more reads than writes.

For example:

                 Application
                 /          \
             Writes         Reads
                │              │
                ▼              ▼
             Primary       Read Replicas
                           /           \
                          ▼             ▼
                     Replica 1      Replica 2

Suppose the workload is:

• 20,000 read requests per second.
• 2,000 write requests per second.

After verifying the primary's actual capacity, we could distribute suitable read traffic across replicas to reduce the primary's read workload.

Which reads are suitable?

Good candidates may include:

• Product catalogue queries.
• Reports.
• Historical records.
• Read-heavy pages where a short delay in data propagation is acceptable.

Which reads need caution?

Suppose a user updates their address and immediately requests it again. If that read goes to a lagging replica, it might return the old address.

For read-after-write consistency, the application may need to read from the primary or use a database-specific consistency mechanism.

Key interview sentence

"I'd introduce read replicas when read traffic is a bottleneck, but I'd route reads according to their consistency requirements rather than sending every read to a replica."

Interview answer

Replication lag is the delay between a change being committed on the primary database and that change becoming visible on a replica.

Consider this sequence:

T1: User updates profile
          │
          ▼
Primary: Name = "Ramesh"
          │
          │ Replication delay
          ▼
Replica: Name = old value

If the next request reads from the replica before the change arrives, the user may see stale data.

Real-world consequences

• A newly created order may not immediately appear in order history.
• A profile update may appear to have failed.
• Inventory displayed from a replica may be outdated.
• Reporting results may lag behind recent transactions.

How would you handle it?

1. Route consistency-sensitive reads to the primary.
2. Use database-specific mechanisms for read-after-write consistency when available.
3. Monitor replica lag.
4. Avoid using potentially stale replica data for critical decisions such as inventory allocation unless the design explicitly handles that risk.
5. Use bounded staleness or eventual consistency where the business requirements allow it.

Important: Replication lag is not necessarily a failure. It becomes a problem when the application's consistency expectations are stricter than the replica's guarantees.

Interview answer

Replication creates additional copies of data, whereas sharding divides data across multiple database partitions or nodes.

Replication

All data

Copy A

Copy B

Purpose: redundancy, availability and potentially read scaling.

Sharding

All data

Shard A

Part 1

Shard B

Part 2

Purpose: distribute data, storage and workload.

Example

Suppose we have 100 million customers.

With replication, multiple database nodes may hold the same 100 million customers.

With sharding:

Shard 1 → Customers A–F
Shard 2 → Customers G–M
Shard 3 → Customers N–Z

Each shard stores a different subset of customers.

Can we use both together?

Yes. A common architecture distributes data across shards and replicates each shard for availability or read scaling.

Key interview sentence

"Replication addresses the need for additional copies of data, while sharding distributes different portions of the data across nodes. They're complementary techniques, not alternatives."

Interview answer

A shard key is the field, or combination of fields, used to determine which shard stores a particular record.

For example, in a customer database, we might use CustomerId to route records to shards.

Conceptually:

CustomerId
     │
     ▼
Shard Routing Logic
     │
     ├── Shard 1
     ├── Shard 2
     └── Shard 3

The application or database routing layer uses the shard key to locate the relevant shard.

Characteristics of a good shard key

1\. Even data distribution

Each shard should receive a reasonable share of the data.

2\. Even traffic distribution

One shard shouldn't receive a disproportionate share of requests.

3\. Query compatibility

The shard key should support common query patterns and avoid unnecessary cross-shard operations.

4\. Stability

The key should not change frequently, because moving records between shards can be expensive.

5\. Sufficient cardinality

There should be enough distinct key values to distribute the workload effectively.

Bad example: Sharding by country

Suppose the user population is:

India   → 80%
US      → 10%
UK      → 5%
Others  → 5%

Sharding only by country may overload the India shard while leaving other shards underutilized.

Another possible approach: Hash-based distribution

A hash function can distribute customer IDs across shards:

shard = hash(CustomerId) % numberOfShards

This is a simplified illustration. In real systems, changing the number of shards can cause many records to be remapped; consistent hashing or other routing strategies can help, depending on the database.

Strong interview conclusion

"I'd select a shard key based on data distribution, traffic distribution, and the application's most common queries. I'd also evaluate hotspot risk and the operational complexity of resharding before committing to the design."

🎯 Day 3 — Bonus Interview Scenario

Interviewer: Your ASP.NET Core e-commerce API is experiencing high SQL Server CPU utilization. How would you improve the architecture?

A good answer should be progressive:

1\. Measure the bottleneck

CPU, I/O, waits, connections, query plans

2\. Optimize SQL

Queries, indexes, execution plans, N+1 calls

3\. Reduce database workload

Caching, fewer round trips, pagination

4\. Scale the right workload

Read replicas, partitioning, suitable database scaling

5\. Consider sharding if necessary

When one database can no longer meet capacity requirements

Sample interview response

"I'd first examine query execution plans, database waits, CPU utilization, I/O, and connection usage to identify the actual cause. I'd optimize expensive queries and indexes, remove unnecessary database calls, and introduce caching for suitable frequently accessed data. If reads are the main bottleneck, I'd evaluate read replicas while accounting for replication lag. If storage or write throughput exceeds what a single database can support, I'd consider partitioning or sharding. I'd choose the solution based on measured bottlenecks and consistency requirements rather than jumping straight to sharding."

🧠 Day 3 Revision Sheet

Quick self-test

5-question quiz

0/5 answered

1\. Which technique creates additional copies of database data?

Sharding

Replication

Partition pruning

Indexing

2\. Which issue can cause a read replica to return an old value?

Horizontal scaling

Index fragmentation only

Replication lag

Load balancing

3\. What should you do first when SQL Server CPU reaches 100%?

Immediately shard the database

Measure and identify the bottleneck

Add API servers only

Replace SQL with NoSQL

4\. What is the main role of a shard key?

Encrypt database records

Determine which shard stores a record

Replicate every write

Create an index automatically

5\. Why can excessive indexes hurt performance?

They prevent all SELECT queries

They eliminate transactions

They add storage and write-maintenance costs

They always cause stale reads