AX 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.ps1 collects 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 -Encrypt if 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-Credential prompt 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 to localhost inside the session, or pass -SqlCredential for SQL auth. Use the FQDN so it matches a *.domain WinRM 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

1. Run the script against the relevant database (AX transaction DB and/or channel DB),

parse the JSON.

1. Interpret through the AX lens — the mapping table in REFERENCE.md.

Headlines:

• largest_tables dominated 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.

1. 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).

1. Fail loud on coverage: name error/skipped sections; 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+.