SQL Server Query Tuning

SQL Server Query Tuning For AX

Use this skill when reviewing SQL Server 2016 data from AX 2012 R3 systems.

Evidence To Review

• sys.dm_exec_query_stats, execution plans, and query text.
• Wait stats deltas, especially PAGEIOLATCH, CXPACKET/CXCONSUMER, LCK, WRITELOG, RESOURCE_SEMAPHORE, and TEMPDB-related waits.
• Missing index DMVs, index usage, index operational stats, fragmentation, and statistics age.
• Blocking chains, deadlock graphs, Query Store data if enabled, and file I/O latency.
• Table row counts, reserved/used space, index size, partition/company distribution, and historical growth where available.
• Production vs. test/pre-production differences in database options, indexes, statistics, compatibility level, maintenance jobs, and data volume.

AX-Specific Guardrails

• Do not recommend broad or speculative index changes on large AX transaction tables.
• Consider DataAreaId, Partition, RecId, and common AX join/filter patterns before suggesting index keys.
• Prefer evidence-backed statistics updates, targeted index review, query pattern validation, and batch scheduling adjustments before invasive changes.
• Flag changes requiring AX model/store deployment, downtime, regression testing, or formal change approval.
• Treat missing-index DMV output as a signal, not a recommendation. Validate against existing AX indexes, write overhead, table size, update frequency, and query recurrence.
• Do not frame NOLOCK, forced plans, MAXDOP changes, or trace flags as casual fixes. They require explicit risk analysis and validation.

Parameter-Sniffing Signals

Look for:

• Same query hash with strongly different duration, reads, CPU, or memory grant.
• Multiple plans for related text or query hash.
• Runtime variance linked to company, item, customer, date range, or status distribution.
• Intermittent regressions after statistics updates, deployments, or plan cache eviction.

Recommended response:

• First confirm recurrence and business impact.
• Compare plans and parameter values where available.
• Suggest targeted plan/query review, statistics strategy, or code-level query shape review before server-wide changes.

Index And Statistics Advice

When proposing index or statistics work, include:

• Existing index coverage and usage.
• Candidate key order and included columns.
• AX fields involved, especially Partition, DataAreaId, status/date fields, dimensions, and RecId.
• Write overhead and maintenance cost.
• Deployment path, test plan, and rollback.
• Before/after metrics: logical reads, CPU, duration, wait profile, blocking, and write cost.

Data Growth And Archive Signals

Flag growth-driven findings when:

• Runtime grows with table row count or index size.
• Maintenance, backup, or statistics windows are expanding.
• Old closed records dominate a high-cost table.
• Staging, log, workflow, retail, or integration tables grow without retention.
• Query tuning would only defer the underlying data-lifecycle problem.

Archive recommendations must include business approval, retention/audit risk, reporting impact, validation, and rollback or restore approach.

Environment Drift Checks

When a problem is production-only, compare:

• Database options and compatibility level.
• Max degree of parallelism and cost threshold settings where available.
• Index definitions and disabled/hypothetical indexes.
• Statistics age, sample rate, and auto-update behavior.
• Data volume and distribution by company/module/date.
• Maintenance schedule and Query Store availability.

Output

For each SQL finding, provide the query signature, affected database object, AX table mapping if known, observed metric, suspected cause, recommendation, change-readiness score, validation query, rollback note, data-growth signal, and environment-drift signal.