sql-server-query-tuning

name	sql-server-query-tuning
description	Interpret SQL Server 2016 performance evidence for Dynamics AX 2012 workloads, including expensive queries, waits, indexes, statistics, plan cache, Query Store, and parameter sniffing.

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.