Nav2009 SQL Performance

NAV 2009 SQL Performance Triage

Targets SQL Server instances hosting Dynamics NAV 2009 databases. The bundled script scripts/Invoke-SqlPerfTriage.ps1 collects a read-only diagnostic snapshot and emits JSON; the agent (you) writes the analysis. The script never modifies server or database state and never calls an LLM.

SCRIPT = this skill's scripts/Invoke-SqlPerfTriage.ps1. Requires PowerShell 7+ (pwsh) and network access to the SQL Server. The full SQL-finding → NAV-action interpretation guide is in REFERENCE.md — read it before writing your analysis.

Permissions & auth

• Default is Windows integrated auth (the user running pwsh). Pass -SqlCredential for SQL auth — it is a PSCredential, so let PowerShell prompt (-SqlCredential (Get-Credential)); never put a password on the command line.
• Needs VIEW SERVER STATE on the instance and VIEW DATABASE STATE (or db_owner) in the NAV database. The trace-flag sub-check may need sysadmin and degrades gracefully if denied.
• Connections are unencrypted by default (NAV 2009-era instances rarely have TLS certs); add -Encrypt if the instance supports it.

How to run

Always run with pwsh. Parse the JSON it prints on stdout.

Sections: server, database, waits, top_queries, missing_indexes, unused_indexes, blocking, deadlocks, sift, fragmentation, stats, largest_tables (default all).

Examples:

# Full triage of a NAV 2009 database
pwsh -File SCRIPT -ServerInstance SQLSRV01 -Database 'Navision_PROD' -OutFile C:\ops\nav-triage.json

# "Users are stuck right now" — live locking picture only
pwsh -File SCRIPT -ServerInstance SQLSRV01 -Database 'Navision_PROD' -Sections blocking,deadlocks,waits

Output contract

• Without -OutFile → full JSON on stdout.
• With -OutFile → full JSON to the file; a compact summary (per-section status + row counts) on stdout. Prefer -OutFile for full snapshots so your context stays small; then read only the sections you need from the file.

Top level: status (ok / partial / error), generated_at (UTC), and sections, where each section is { status: ok|error|skipped, data|error|reason }. Timestamps inside data are SQL Server local time; deadlock timestamps are UTC. Full schema and per-section notes in REFERENCE.md.

What you (the agent) do with the result

1. Run the script, parse the JSON.
2. Interpret through the NAV lens, not just generic SQL tuning — use the mapping table in REFERENCE.md. The key NAV 2009 specifics:
   • Top queries full of FETCH API_CURSOR / sp_cursorfetch are normal for the Classic client driver — judge them by reads-per-execution, not by their cursor shape.
   • High reads-per-execution usually traces back to C/AL: missing SETCURRENTKEY matching the filters, FIND('-') loops over large tables, or CALCSUMS/FlowFields without a supporting SIFT key. The fix belongs in C/AL or NAV key design more often than in raw SQL indexes.
   • Never recommend creating/dropping indexes directly in SQL on a NAV 2009 DB as the first option — NAV owns its indexes via table keys (MaintainSQLIndex) and SIFT views (MaintainSIFTIndex); out-of-band SQL indexes are lost when keys/companies change. Say so.
   • Blocking/deadlocks during posting: look at the objects involved (G/L Entry, Item Ledger Entry, dimension tables, No. Series Line) — typical fixes are C/AL-side (lock ordering, shorter transactions, MaintainSIFTIndex off on hot SIFT views), not SQL-side.
3. Lead with the 2–4 findings that matter, each as: evidence (numbers from the JSON) → likely NAV-level cause → concrete next action. Don't recite every section.
4. Fail loud on coverage: name any section with status: error/skipped and what that means for the analysis (e.g. "deadlocks section unavailable — SQL 2005 has no Extended Events"). Wait stats and index usage are cumulative since instance restart — state the window (server.system.sqlserver_start_time) before drawing conclusions from them.
5. For the C/AL side of a finding (which key to add, how to restructure a loop), the nav2009-development skill holds the coding patterns.

Errors

• Cannot connect → wrong instance name, SQL Browser off for named instances, firewall, or auth. Suggest checking with Test-NetConnection <server> -Port 1433.
• Section error: VIEW SERVER STATE permission was denied → ask for the permission grant; the remaining sections still ran.
• deadlocks empty/unsupported on SQL Server 2005 → only SQL Profiler / trace flag 1222 can capture deadlocks there; offer that as the manual alternative.