How We Turned a Sluggish ERP Database into a High‑Performance System

Background

When learning databases I always liked case studies because they illustrate concrete optimization techniques. In this post I share a typical ERP performance case that many enterprises face: long document workflows, complex business logic, large data volumes, and insufficient DBA oversight leading to severe slowness.

User Symptoms

The system was extremely slow: saving a document took minutes, many operations timed out, reports took an hour, and users complained especially around 4 PM, causing overtime and pressure on IT.

System Environment

The server had 8 sockets, 24 cores with hyper‑threading (384 logical CPUs), 1 TB RAM, all‑flash storage, and SQL Server 2012 with the latest patches. The database size was about 1.2 TB.

Key Metrics

Various performance charts showed high request rates, many user connections, long statement execution times, extensive wait times, CPU saturation, memory pressure, and disk queue buildup.

Analysis

Performance issues usually stem from six factors: business pressure, hardware, environment, code, internal DB engine behavior, and architecture. In this case, hardware resources were under pressure, but the main bottleneck was massive statement wait times caused by lock contention ("LCK_M_" locks) lasting hundreds of seconds.

Optimization Phase 1 – General Tuning

Initial steps included creating high‑cost indexes, adjusting server parameters, and optimizing tempdb. After applying these changes, memory and I/O pressure stabilized and the number of slow statements decreased, though many statements still exceeded two minutes.

Before:

After:

Optimization Phase 2 – Statement‑Level Tuning

Further investigation revealed three remaining issues: occasional memory spikes, long‑running sleeping processes, and a few high‑cost statements. The most resource‑intensive component was reporting, which used physical temporary tables to insert and later delete millions of rows, blocking business operations. Interfaces also caused frequent updates, and some application code leaked connections.

Optimization Phase 3 – Report Separation

Since reporting remained a bottleneck, we introduced a multi‑active cluster (Moebius) to separate OLAP reporting from OLTP processing. By routing report‑related stored procedures to a secondary node that could write to physical tables, we achieved read/write separation without compromising data safety.

Resulting architecture diagram:

After this phase the system became responsive again; slow statements dropped dramatically despite the TB‑scale data volume.

Conclusion

Effective performance tuning requires a holistic view covering business load, hardware, environment, code, internal DB behavior, and architecture. Simple tweaks rarely solve deep issues; comprehensive analysis and targeted architectural changes, such as read/write separation, deliver lasting improvements. Even with TB‑scale data, careful tuning can yield significant gains before resorting to costly sharding or partitioning.