Acumatica SQL Server — Indexing for Acumatica DACs

Acumatica's schema is generated: every DAC maps to a physical table, and out of the box the platform indexes primary keys, a handful of unique constraints, and the columns it already knows will be filtered on. Everything else — the custom BQL view that joins three tables, the generic inquiry a controller built to slice AR by project, the report that groups by a Usr-prefixed field — runs on whatever the query optimizer can do with what's there. Indexing Acumatica well is less about learning new SQL and more about knowing where the platform stops helping you and your own judgment has to start.

How DACs map to tables

A DAC's fields correspond one-to-one with columns on its mapped table, and Acumatica ships primary key and identity indexes for the tables it controls directly — APInvoice, SOOrder, ARTran, and so on. What it does not do is anticipate every predicate you'll ever filter or sort on. A generic inquiry built against SOOrder filtering on CustomerID and a custom date range will use the clustered index scan happily at 5,000 rows and choke at 500,000, because nothing indexes that specific combination. The same is true for custom DACs backed by new tables created through a Usr-field or a full custom table — SQL Server gives you a clustered primary key and nothing more unless you add it yourself.

Finding missing indexes safely

SQL Server tracks what the optimizer wished it had via the missing-index DMVs. They're a starting point, not a prescription — they reflect compiled plans since the last service restart and say nothing about write cost, so blindly creating every suggestion is how systems end up with fifteen overlapping indexes on GLTran. Treat the output as a shortlist to validate against actual query patterns from the slow queries you already know about, not as a to-do list to clear.

-- Rank missing-index suggestions by estimated impact
SELECT TOP 20
    d.statement AS table_name,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    gs.avg_user_impact,
    gs.user_seeks + gs.user_scans AS usage_count
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
ORDER BY gs.avg_user_impact * (gs.user_seeks + gs.user_scans) DESC;

-- A realistic fix: SOOrder filtered by branch + status,
-- sorted by order date, with commonly-selected columns included
-- so the query is satisfied from the index without a key lookup
CREATE NONCLUSTERED INDEX IX_SOOrder_Branch_Status_OrderDate
ON SOOrder (BranchID, Status, OrderDate)
INCLUDE (CustomerID, OrderNbr, OrderType, CuryOrderTotal);

The INCLUDE columns matter as much as the key columns. Acumatica's generic inquiries and reports tend to select a wide set of display fields even when they filter narrowly, so an index that covers the filter but forces a key lookup for every display column barely helps. Include what the query actually selects, not just what it filters on.

Index maintenance jobs

Acumatica tables under active transaction load — GLTran, ARTran, APTran, INTran — fragment quickly because rows insert in transaction order but indexes are frequently keyed by business date or reference number, not insert order. A standard maintenance job checks sys.dm_db_index_physical_stats and reorganizes indexes between 5-30% fragmentation, rebuilds above 30%, and skips small tables where fragmentation is cosmetic. Run it in the maintenance window, not mid-day — a rebuild takes a schema modification lock unless you're licensed for ONLINE = ON, which stalls every screen touching that table.

The cost of over-indexing

Acumatica is a write-heavy OLTP system — every sales order, AP bill, and inventory move touches several tables in one transaction. Every additional nonclustered index on those tables is extra work on every insert and update, and on high-transaction tables like GLTran or INTran that overhead is felt directly in screen responsiveness, not just in batch jobs. It's tempting to index every column a generic inquiry ever filters on; the better discipline is to add indexes for the queries that actually run often and are actually slow, and to periodically check sys.dm_db_index_usage_stats for indexes with high writes and near-zero reads — those are pure cost with no benefit, and dropping them is often the fastest performance win available.

Wrapping up

Index the predicates your custom BQL views, generic inquiries, and reports actually use, not every column that could theoretically be filtered. Use the missing-index DMVs as a lead to investigate, not an instruction to execute. Keep a maintenance job rebuilding and reorganizing based on measured fragmentation, and periodically prune indexes nobody reads — on a system where every table write cascades through GL, AR, and inventory in the same transaction, an unused index isn't neutral, it's a tax you're paying on every posting.