ax2012-sql-performance
Diagnose performance problems in Microsoft Dynamics AX 2012 (incl. R3) and its SQL Server databases — the AX transaction database, the model store, and the Retail channel database. The bundled script
SKILL.mdskills/ax-retail/ax2012-sql-performance/SKILL.md
GitHubAX 2012 SQL Performance Triage
Targets SQL Server instances hosting Dynamics AX 2012 / R3 databases — the main AX transaction DB, and the Retail channel database behind POS. The bundled script
scripts/Invoke-SqlPerfTriage.ps1collects a read-only diagnostic snapshot and emits JSON; the agent (you) writes the analysis using the AX interpretation guide in REFERENCE.md — read it before writing your analysis.
SCRIPT = this skill's scripts/Invoke-SqlPerfTriage.ps1 (vendored identically in
sqlserver-perf-triage and nav2009-sql-performance).
Permissions & auth
- Default is Windows integrated auth;
-SqlCredential (Get-Credential)for SQL auth. - Needs VIEW SERVER STATE + VIEW DATABASE STATE (or
db_owner) in the AX/channel DB. - Add
-Encryptif the instance has a valid certificate. - From a non-domain-joined operator (or an agent-driven, non-interactive shell): integrated
auth from a workgroup box to a domain SQL instance fails, and a
-SqlCredential/Get-Credentialprompt can't render non-interactively. Either run the collector on the SQL host over a visible remoting window —Start-Process pwsh -ArgumentList '-NoExit','-Command',"Invoke-Command -ComputerName SQL01.contoso.local -Credential (Get-Credential) -Authentication Negotiate -FilePath '<SCRIPT>' -ArgumentList ..."— connecting tolocalhostinside the session, or pass-SqlCredentialfor SQL auth. Use the FQDN so it matches a*.domainWinRM TrustedHosts entry.
How to run
Always run with pwsh. Parse the JSON it prints on stdout.
# Full triage of the AX transaction database
pwsh -File SCRIPT -ServerInstance SQLSRV01 -Database 'MicrosoftDynamicsAX' -OutFile C:\ops\ax-triage.json
# The Retail channel database
pwsh -File SCRIPT -ServerInstance SQLSRV02 -Database 'RetailChannelDB' -OutFile C:\ops\channel-triage.json
# "AX is frozen right now" — live locking picture only
pwsh -File SCRIPT -ServerInstance SQLSRV01 -Database 'MicrosoftDynamicsAX' -Sections blocking,deadlocks,waits
Sections: server, database, waits, top_queries, missing_indexes, unused_indexes,
blocking, deadlocks, sift (NAV-only; reports 0 on AX — ignore), fragmentation,
stats, largest_tables (default all). Same parameters and output contract as the
sibling skills: -OutFile for big snapshots, -QueryTimeout 300 for the fragmentation scan
on a large AX DB, -TopN, -Sections.
What you (the agent) do with the result
- Run the script against the relevant database (AX transaction DB and/or channel DB), parse the JSON.
- Interpret through the AX lens — the mapping table in REFERENCE.md.
Headlines:
largest_tablesdominated by InventSumLogTTS, SysDatabaseLog, BatchJobHistory, AifMessageLog, EventInbox = missing AX cleanup routines, not a SQL problem. Each has a standard in-AX cleanup (REFERENCE lists them).- RCSI ON is EXPECTED for an AX 2012 database (opposite of NAV) — flag it if OFF.
- AX owns its indexes via the AOT — indexes created directly in SQL are dropped on database synchronization. Route index changes through the AOT; raw SQL indexes only as documented stopgaps.
- Trace flag 4136 (disable parameter sniffing) is commonly recommended for AX 2012 workloads — note its presence/absence.
- Channel DB findings usually trace to CDX sync backlog or unpurged retail transactions — check sync job health on the AX (HQ) side, not just SQL.
- Lead with the 2–4 findings that matter: evidence → likely AX-level cause → concrete next action (which AX cleanup/configuration, which AOT change, which batch reschedule).
- Fail loud on coverage: name
error/skippedsections; state the counter window (server.system.sqlserver_start_time) before trusting cumulative stats.
Errors
Same as the sibling skills: Cannot connect → instance name/SQL Browser/firewall/auth;
permission-denied sections → request VIEW SERVER STATE; deadlocks needs SQL 2008+.