Acumatica Performance — Cache Tuning

Acumatica Performance — Cache Tuning is the Acumatica performance topic that nobody asks about until they have to. By the time a tenant is "slow", the user base has been frustrated for weeks, the IT team is in firefighting mode, and the budget for a fix is whatever the CFO approves in a panic. This guide is what to do before that point. It is the diagnostic flow, the fixes, and the monitoring that keeps a tenant fast.

Establish a baseline first

Before you tune anything, you need a number to compare against. The baseline:

1. Pick the top 20 screens by user count.
2. For each, time the render from navigation to interactive.
3. For each, capture the SQL the screen generates.
4. For each, record the SQL Server wait stats during the render.

protected void _(Events.RowSelected<INItemSite> e)
{
    var sw = Stopwatch.StartNew();
    // ... existing logic
    sw.Stop();
    if (sw.ElapsedMilliseconds > 100)
        PXTrace.WriteInformation($"Slow RowSelected on {e.Row}: {sw.ElapsedMilliseconds}ms");
}

The diagnostic flow

When a screen is slow, the diagnostic order is:

1. Is the SQL Server busy? Check sys.dm_exec_query_stats and sys.dm_os_wait_stats. If the server is saturated, no amount of code tuning helps.
2. Is the slow screen a single query or many? Look at the SQL Profiler trace. A single slow query is a query-tuning problem; many slow queries is a data-volume problem.
3. Is the slowness on the server or the client? Server: SQL, BQL, event handlers. Client: rendering, JS, network.
4. What is the execution plan of the slow query? Table scan, missing index, parameter sniffing — each has a different fix.

SELECT TOP 20
    total_worker_time / execution_count AS avg_cpu,
    total_elapsed_time / execution_count AS avg_duration,
    total_logical_reads / execution_count AS avg_reads,
    SUBSTRING(text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_cpu DESC;

For the broader SQL Server performance playbook, see the SQL Server indexing guide and the Query Store guide.

The fixes that work 90% of the time

The five fixes I try first, in order:

1. Add the missing index. Most slow queries are missing-index queries. Use the missing-index DMVs to find the candidate.
2. Move heavy logic out of RowSelected. Push validation to RowPersisting, side effects to a graph action.
3. Filter at the GI, not the UI. The most common performance bug in a GI is a row-level visibility expression that returns every row.
4. Reduce the number of joins. Each join is a multiplication factor. Drop the ones you do not need.
5. Batch the work. Loop with 1,000 calls is slow; loop with 10 calls of 100 records is fast.

SELECT TOP 20
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) AS improvement_measure,
    OBJECT_NAME(d.object_id, d.database_id) AS table_name,
    d.equality_columns, d.inequality_columns, d.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details d ON mig.index_handle = d.index_handle
ORDER BY improvement_measure DESC;

For the broader customisation performance patterns, see the performance tuning definitive guide.

IIS application pool tuning

The IIS application pool is the second most common performance bottleneck. The right configuration:

For the full IIS tuning playbook, see the IIS application pool tuning guide.

Continuous monitoring

Tuning is not a one-time event. The monitoring that keeps a tenant fast:

• SQL Server wait stats. Alert on a sudden increase in any wait type (PAGEIOLATCH, LCK_M_X, ASYNC_NETWORK_IO).
• Slow query log. Capture any query over 5 seconds; review weekly.
• Application pool recycling. Alert on every recycle; verify the cause is planned.
• User-reported slowness. Trend ticket volume by screen; alert on a spike.
• Customisation event handler timing. Log timing in every event handler; review weekly.

public class PerformanceMonitor
{
    public static void Record(string operation, long milliseconds)
    {
        if (milliseconds > 1000)
            PXTrace.WriteWarning($"SLOW: {operation} took {milliseconds}ms");
    }
}

For the broader monitoring patterns, see the monitoring and alerting guide.

Load testing

Before you declare the tenant tuned, load test it. The tools:

• Visual Studio Load Test. Built in, easy to use, good for Windows shops.
• JMeter. Open source, platform-independent, steeper learning curve.
• k6. Modern, JavaScript, scriptable, good for CI integration.

import http from 'k6/http';
import { check, sleep } from 'k6';
export const options = {
  stages: [
    { duration: '1m', target: 50 },
    { duration: '5m', target: 50 },
    { duration: '1m', target: 100 },
    { duration: '5m', target: 100 },
  ],
  thresholds: { http_req_duration: ['p(95)<2000'] },
};
export default function () {
  const res = http.get('https://acumatica.example.com/app');
  check(res, { 'status is 200': (r) => r.status === 200 });
  sleep(1);
}

For the broader scaling patterns, see the scaling for 1000 users guide and the Azure VM sizing guide.

Wrapping up

The diagnostic flow, the five fixes, the IIS tuning, the monitoring, the load testing. Get all five right and the tenant is fast. The discipline is not glamorous, but it is the difference between a tenant the user trusts and a tenant they blame. Numbers, not feelings.