# SQL Server Query Tuning

> Interpret SQL Server 2016 performance evidence for Dynamics AX 2012 workloads, including expensive queries, waits, indexes, statistics, plan cache, Query Store, and parameter sniffing.

- Skill: `rweisssieker-xp/sql-server-query-tuning` (Agent Skill)
- Install (CLI): `npx skillmds@latest add rweisssieker-xp/sql-server-query-tuning`
- Raw SKILL.md: https://api.skillmd.com/api/skills/rweisssieker-xp/sql-server-query-tuning/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: rweisssieker-xp (https://skillmd.com/u/rweisssieker-xp)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/rweisssieker-xp/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.