"It was fast last week" is one of the most useless and most common statements I hear when a client reports Acumatica has gotten slow. Useless because without historical data there's nothing to compare against, and by the time someone escalates the complaint, the bad query plan has usually been running for days and nobody captured what changed. SQL Server's Query Store solves exactly this problem, and turning it on against an Acumatica database is one of the highest-value, lowest-effort things I do on any client engagement that involves performance work.
Query Store persists execution plans and runtime stats, across plan changes
Query Store, once enabled on a database, automatically captures every query's execution plan and runtime statistics (duration, CPU, logical reads, execution count) over time, and critically, it keeps a history of every plan a query has used, not just the current one. This is the feature that matters for Acumatica specifically: BQL compiles to parameterized SQL, and the SQL Server optimizer can and does choose different plans for the same query shape depending on statistics, parameter sniffing, and index changes. Without Query Store, "the plan changed and got worse" is invisible until someone notices the symptom; with it, you can see exactly when the regression happened and what the plan looked like before.
ALTER DATABASE [AcumaticaDB] SET QUERY_STORE = ON;
ALTER DATABASE [AcumaticaDB] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO, -- ignore trivial/ad-hoc noise
MAX_STORAGE_SIZE_MB = 2000,
INTERVAL_LENGTH_MINUTES = 15,
STALE_QUERY_THRESHOLD_DAYS = 30
);
QUERY_CAPTURE_MODE = AUTO matters on an Acumatica database specifically because the platform generates a huge volume of ad-hoc, rarely-repeated queries (dynamic BQL from Generic Inquiries, ad-hoc filters) alongside a smaller set of genuinely hot, repeated queries from screens users hit constantly. Capturing everything (ALL mode) fills the store with noise that makes the actually-important regressions harder to find; AUTO filters out queries that aren't executed or expensive enough to matter.
Finding a regression: the query that changed plans, not just the slow one
The most valuable Query Store report for Acumatica troubleshooting isn't "top queries by duration," it's the plan comparison view, because a query that's always been moderately expensive is a design decision to live with, while a query that was fast for months and suddenly picked a bad plan after a statistics update or an index change is an actual regression worth fixing immediately.
SELECT
q.query_id,
qt.query_sql_text,
COUNT(DISTINCT p.plan_id) AS plan_count,
AVG(rs.avg_duration) AS avg_duration_us,
MAX(rs.avg_duration) AS worst_duration_us
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(DAY, -14, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY MAX(rs.avg_duration) DESC;
A query showing up here with two or more plans and a big spread between average and worst duration is almost always a parameter sniffing case, common on Acumatica screens where a BQL filter's selectivity varies wildly by customer (a small customer's open orders vs a huge distributor's), and the cached plan optimized for one shape performs badly for the other.
Once you've identified a regressed plan, sp_query_store_force_plan pins a specific known-good plan_id for a query_id, immediately stabilizing performance while you investigate the root cause (usually stale statistics or an index change) at leisure. This is the single fastest mitigation I've deployed for a client production incident: identify the regression in Query Store, force the prior good plan, buy time, then fix the underlying cause without an emergency deployment of any Acumatica customization.
Filtering Acumatica's own query noise out of the picture
A raw Query Store report on an Acumatica database is dominated by framework-generated housekeeping queries, session and cache management, license checks, audit logging, that aren't worth optimizing and just add noise. Filtering the query text for the actual DAC-backed table names your customization or client's heavy screens touch (ARInvoice, SOOrder, INTran, whatever the client's specific pain point is) narrows the report to signal fast.
WHERE qt.query_sql_text LIKE '%SOOrder%'
AND qt.query_sql_text NOT LIKE '%sysdiagrams%'
AND rs.last_execution_time > DATEADD(DAY, -7, GETUTCDATE())
Retention settings and the overhead conversation clients always ask about
Query Store's overhead is genuinely small (typically low single-digit percent CPU) for the visibility it buys, but on a busy production Acumatica instance it's worth capping MAX_STORAGE_SIZE_MB deliberately and monitoring it rather than leaving the default, because a store that fills up switches to read-only and silently stops capturing new data, which defeats the entire point right when you need it most, during an active incident. I set an alert on sys.database_query_store_options.actual_state flipping away from READ_WRITE on every client instance where I've enabled this, specifically so the store never goes quiet without someone noticing.
Wrapping up
Query Store turns "it was fast last week" from an unfalsifiable complaint into an actual investigation with historical plans and runtime data to compare against. Enable it with AUTO capture mode to avoid drowning in Acumatica's own housekeeping query noise, use the plan-comparison view to catch parameter sniffing regressions specifically, and keep an eye on storage so the store never silently stops capturing during the exact incident you'll want the data for. It is one of the cheapest performance investments available on any Acumatica SQL Server instance, and most clients I work with have never turned it on.
Independent software engineer in Nairobi specialising in Acumatica customisations, Laravel backends, and tax fiscalisation integrations across East and Southern Africa.