Trace Parser & Optimization

Why trace before you optimise?

Simple explanation

Think of a doctor.

A good doctor doesn’t prescribe medicine based on a guess — they run tests first. Trace Parser is your diagnostic tool. It records everything F&O does during an operation (which methods run, which SQL queries fire, how long each takes) and shows you exactly where the time is spent.

Without tracing, you’re guessing. With tracing, you can see: “80% of this batch job’s time is spent in one SQL query that scans 2 million rows because there’s no index.” Now you know exactly what to fix.

Trace Parser: capture and analyse

Capturing a trace

Step	Action
1	Navigate to the operation you want to trace (e.g. a slow form or batch job)
2	Open System Administration → Setup → Trace → enable tracing
3	Set trace options: SQL traces (essential), X++ method traces (for code analysis)
4	Perform the slow operation
5	Stop tracing
6	Download the trace file (.etl)
7	Open in Trace Parser desktop tool for analysis

What the trace shows you

┌─────────────────────────────────────────────────────┐
│  Trace Parser                                        │
│                                                      │
│  ┌─ Call Tree ─────────────────────────────────────┐ │
│  │ processReport()              [Total: 45.2s]     │ │
│  │ ├─ loadData()                [38.1s] ← HOTSPOT  │ │
│  │ │  ├─ SELECT FROM InventTrans [32.4s] ← BAD SQL │ │
│  │ │  └─ calculateCost()        [5.7s]             │ │
│  │ └─ formatOutput()            [7.1s]             │ │
│  └─────────────────────────────────────────────────┘ │
│                                                      │
│  ┌─ SQL Statements ────────────────────────────────┐ │
│  │ #1: SELECT ... FROM InventTrans WHERE ...       │ │
│  │     Duration: 32.4s | Rows: 2,100,000           │ │
│  │     Table Scan (no index used)  ← ROOT CAUSE    │ │
│  └─────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────┘

Key metrics to examine

Metric	What it tells you	Action threshold
Method duration	How long each X++ method takes	> 1s for interactive, > 10s for batch steps
SQL duration	Time spent in individual SQL statements	> 500ms for single queries
Row count	How many rows a query returns or scans	Scan count >> return count = missing index
Call count	How many times a method/query executes	Repeated identical queries = caching opportunity
RPC calls	Client-server round trips	High count = chatty design, consolidate calls

Scenario: Elena traces the slow production report

PacificForge’s production report takes 12 minutes to run. Elena captures a trace and finds:

Finding	Duration	Root cause	Fix
SELECT FROM ProdTable scans entire table	6.2s	No index on SchedDate column	Add non-clustered index on SchedDate
processReport() calls fetchCost() 45,000 times	4.1s	Row-by-row lookup in a loop	Replace with set-based join using insert_recordset
Same CustTable.AccountNum lookup repeated 3,000 times	1.8s	No caching on CustTable	Already has Found caching — query uses non-indexed field. Fix the WHERE clause to use primary key

After fixes: 12 minutes → 45 seconds.

“The trace told us exactly where to look,” Elena tells Sophie. “Without it, we’d have been guessing.”

Query plan analysis

Reading SQL Server execution plans

When Trace Parser reveals a slow query, the next step is examining the execution plan to understand why:

Plan element	Good sign	Bad sign
Index Seek	Query uses an index efficiently	—
Index Scan	—	Reading entire index — usually means the WHERE clause doesn’t match the index
Table Scan	—	Reading every row — no usable index at all
Nested Loop Join	Small outer table, indexed inner table	Large tables on both sides = slow
Hash Join	Large unsorted datasets	Small datasets = overhead wasted on hashing
Sort	—	Sorting in-flight = missing index with the right sort order
Key Lookup	—	Index doesn’t cover all needed columns (consider INCLUDE columns)

Exam tip: Index Seek vs Index Scan vs Table Scan

The exam frequently tests understanding of these three:

Index Seek ✅ — jumps directly to matching rows using the index B-tree. Fast: O(log n)
Index Scan ⚠️ — reads the entire index sequentially. Better than table scan but still reads all index pages
Table Scan ❌ — reads every row in the table. No index used at all. Always investigate

Pattern: “A query on InventTrans WHERE ItemId = ‘WIDGET-01’ does a table scan. What’s missing?” → A non-clustered index on ItemId.

Index design

Index types in F&O

Index types — each serves a different query pattern
Index Type	Structure	When to use	Limitation
Clustered	Determines the physical sort order of data on disk. One per table.	The most frequently used range query or the primary key	Only ONE per table — choose carefully
Non-clustered	Separate B-tree structure with pointers to data rows. Many per table.	Secondary lookup patterns (WHERE, JOIN conditions)	Too many indexes slow down INSERT/UPDATE operations
Unique	Enforces uniqueness + serves as an index	Primary keys and alternate keys that must be unique	Insert fails if duplicate value exists
Include columns	Non-key columns added to a non-clustered index leaf level	Cover queries to avoid key lookups (SELECT columns not in WHERE)	Increases index size on disk

Index design guidelines

Guideline	Why
Index the WHERE clause columns	The optimiser uses indexes to filter rows — unindexed columns force scans
Put the most selective column first	The column that eliminates the most rows should be the leading column
Include columns to cover the query	If the SELECT list includes columns not in the index, SQL does a key lookup. Add them as INCLUDE columns to avoid this
Don’t over-index	Each index is updated on every INSERT/UPDATE/DELETE. More indexes = slower writes
Match the clustered index to the most common query	The clustered index determines physical data order — range queries on the clustered key are very fast

Vik designs indexes for a custom table

Vik creates indexes for a new PFQualityResults table at Axion Dynamics:

Index	Type	Columns	Reasoning
`PFQualityResults_PK`	Clustered unique	`QualityOrderId, TestId`	Primary key — most queries filter by quality order
`PFQualityResults_Item`	Non-clustered	`ItemId, TestDate` INCLUDE `Result, Status`	Reports query by item and date, need result and status in output
`PFQualityResults_Status`	Non-clustered	`Status, TestDate`	Dashboard queries filter by status within date range

“Three indexes is usually the sweet spot for a table,” Vik says. “One clustered for the PK, one or two non-clustered for the most common query patterns. More than five and your writes start suffering.”

Optimising data entity imports

Data Management Framework (DMF) imports can be slow for large datasets. Key optimisation levers:

Technique	How it helps	Setting
Set-based processing	Uses insert_recordset instead of row-by-row	Entity property: `EnableSetBasedProcessing = Yes`
Skip validation	Skips business rule validation for pre-validated data	Import settings: skip validation checkbox
Skip staging	Bypasses the staging table (direct insert)	Composite entity or skip staging option
Parallel processing	Multiple threads process different chunks simultaneously	Data Management → Configure entity execution parameters
Batch size tuning	Optimal number of rows per commit	Default 100 — increase to 500-1000 for clean data
Disable change tracking	Avoids change-tracking overhead during bulk load	Temporarily disable, re-enable after import

Scenario: Elena optimises a product data import

PacificForge needs to import 200,000 product records from their legacy system. Initial import takes 18 hours.

Elena’s optimisations:

Change	Before	After
Enable set-based processing	Row-by-row inserts	Bulk insert_recordset
Skip staging table	Staging → Target (2 writes)	Direct to target (1 write)
Increase batch size to 500	100 rows per commit	500 rows per commit
Enable 4 parallel threads	Single-threaded	4 parallel workers
Disable change tracking	Tracking every insert	Disabled during import

Result: 18 hours → 1.5 hours.

“Set-based processing alone cut it by 60%,” Elena tells DBA Harpreet. “The parallel threads gave us another 3x improvement.”

Batch process optimization

Batch framework basics

Concept	Detail
Batch job	A scheduled task that runs on the batch server (AOS)
Batch task	Individual units of work within a batch job
Batch group	Assigns tasks to specific AOS instances for load balancing
Recurrence	Schedule: once, daily, hourly, custom cron-like patterns
Parallelism	Multiple tasks within a job can run simultaneously on different threads

Async framework

The Async framework allows batch tasks to run asynchronously — the calling code doesn’t wait for completion:

// Create an async batch task
SysOperationServiceController controller =
    new SysOperationServiceController(
        classStr(PFInventValuationService),
        methodStr(PFInventValuationService, processWarehouse));

controller.parmExecutionMode(SysOperationExecutionMode::Async);
controller.startOperation();
// Continues immediately — task runs in background

Benefit	Detail
Non-blocking	Main thread continues while async work runs in background
Scalable	Multiple async tasks can run in parallel across batch servers
Resilient	Each task can fail independently without crashing the parent job

Sandbox framework

The Sandbox framework provides isolated execution for untrusted or resource-intensive code:

Feature	Detail
Isolation	Code runs in a separate AppDomain with restricted permissions
Resource limits	Memory and CPU caps prevent a single task from consuming all resources
Timeout	Tasks are killed if they exceed the configured time limit
Use cases	External data transformations, complex calculations, third-party library calls

Batch optimisation patterns

Pattern	Description	When to use
Task splitting	Break one large task into many small parallel tasks	Large dataset processing (e.g. inventory valuation per warehouse)
Batch bundling	Group related small tasks into one execution	Many tiny tasks with per-task overhead
Dependency chains	Task B runs only after Task A completes	Sequential processing requirements
Priority assignment	Critical tasks get higher priority in the batch queue	Mix of urgent and background tasks
Server affinity	Pin tasks to specific AOS instances via batch groups	Resource-intensive tasks on dedicated servers

Scenario: Elena redesigns the nightly batch

PacificForge’s nightly processing runs 12 batch tasks sequentially — total time: 6 hours. Elena redesigns:

BEFORE (Sequential — 6 hours):
Task 1 → Task 2 → Task 3 → ... → Task 12

AFTER (Parallel with dependencies — 2 hours):
┌─ Task 1 (Inventory sync)     ──┐
├─ Task 2 (Sales aggregation)    ├──▶ Task 7 (Financial posting)
├─ Task 3 (Purchase reconcile)   │       │
└─ Task 4 (Quality processing)  ──┘      ▼
                                   Task 12 (Reports)
┌─ Task 5 (Production costing)  ──┐
└─ Task 6 (Warehouse movements) ──┴──▶ Task 8 (Cost roll-up)

┌─ Task 9  (Email notifications)
├─ Task 10 (Data archival)        ← independent, run anytime
└─ Task 11 (Cache warm-up)

Key changes:

Tasks 1-4 run in parallel (no dependencies between them)
Task 7 waits for Tasks 1-4 (dependency chain)
Tasks 9-11 are independent — run in parallel with everything
Each task assigned to a batch group for server affinity

“Parallelism gave us 3x improvement. Identifying which tasks could run independently was the real insight,” Elena says.

Performance checklist summary

Elena's performance checklist for PacificForge

Elena creates a standard performance checklist for the development team:

Before writing code:

Use set-based operations (insert_recordset, update_recordset, delete_from) wherever possible
Choose TempDB temp tables for datasets > 500 rows
Design indexes for your query patterns before building forms

After writing code:

Run Trace Parser on key scenarios (form load, batch jobs, reports)
Check for table scans in SQL analysis — add indexes
Look for repeated identical queries — add caching
Look for row-by-row loops that could be set-based

For batch jobs:

Identify independent tasks and run them in parallel
Set up dependency chains only where truly required
Assign batch groups for server affinity
Monitor execution times and set alerts for degradation

For data imports:

Enable set-based processing on entities
Tune batch size (500-1000 for clean data)
Enable parallel threads
Skip staging if data is pre-validated

Question

What is the Trace Parser workflow for diagnosing F&O performance issues?

Click or press Enter to reveal answer

Answer

1) Enable tracing in System Administration. 2) Reproduce the slow operation. 3) Stop tracing and download the .etl file. 4) Open in Trace Parser desktop tool. 5) Examine call tree for hotspots, SQL statements for slow queries, and call counts for repeated lookups. 6) Fix the top bottleneck. 7) Re-trace to verify improvement.

Click to flip back

Question

What is the difference between Index Seek, Index Scan, and Table Scan?

Click or press Enter to reveal answer

Answer

Index Seek (✅) — jumps directly to matching rows via the B-tree. O(log n), fastest. Index Scan (⚠️) — reads the entire index sequentially. Better than table scan but still reads all pages. Table Scan (❌) — reads every row in the table. No index used. Always investigate and add appropriate indexes.

Click to flip back

Question

What are INCLUDE columns on an index and why use them?

Click or press Enter to reveal answer

Answer

INCLUDE columns are non-key columns added to the leaf level of a non-clustered index. They 'cover' the query — if all SELECT columns are in the index (key + include), SQL Server doesn't need a key lookup to the clustered index. This avoids expensive bookmark lookups and makes queries faster.

Click to flip back

Question

Name three techniques to optimise data entity imports in the Data Management Framework.

Click or press Enter to reveal answer

Answer

1) Enable set-based processing (insert_recordset instead of row-by-row). 2) Enable parallel processing (multiple threads processing different chunks). 3) Increase batch size (500-1000 rows per commit instead of default 100). Also: skip staging for pre-validated data, and temporarily disable change tracking during bulk loads.

Click to flip back

Knowledge Check

Elena's Trace Parser analysis shows a query on InventTrans that takes 32 seconds and scans 2 million rows but only returns 150. What is the most likely root cause?

Knowledge Check

Vik needs to optimise a data import of 500,000 vendor records. The import currently takes 24 hours. Which combination of settings will have the biggest impact?

Knowledge Check

PacificForge runs 10 nightly batch tasks sequentially (total: 5 hours). Elena identifies that tasks 1-4 have no dependencies on each other. What should she do?

Knowledge Check

A non-clustered index on PFQualityResults has columns (ItemId, TestDate). A report queries SELECT ItemId, TestDate, Result, Status WHERE ItemId = 'WIDGET' AND TestDate > '2025-01-01'. The query does an Index Seek but is still slow due to key lookups. What should Elena add?

Next up: You’ve completed Domain 7! Review security, performance, and optimisation concepts, then test your knowledge across all MB-500 domains.