Performance Analysis: Trace Parser & Activity Monitor in Dynamics 365 Finance & Operations
Question 46 — Finding and fixing slow code and queries with the right diagnostic tools.
Interview Question
Model Answer (Short)
To diagnose performance issues you use two complementary tools. Trace Parser analyses a captured
trace to reveal the longest-running code, longest-running SQL query, highest call counts and other
metrics — so you can pinpoint the slow method or statement to improve. SQL Activity Monitor is
used to identify slow-running queries at the database level, and it's especially handy for checking
the loading of a form and its data on screen. Beyond tools, the developer must follow key
performance rules: avoid nested loops (fetching data inside a loop-in-loop degrades performance
exponentially — use computed columns instead), prefer set-based operations with the right
skip methods, use field lists and firstOnly, keep
TTS blocks balanced, and add missing indexes found in the SQL execution plan.
The Two Diagnostic Tools
Trace Parser
- Analyses a captured trace of an execution.
- Reveals the longest-running code and the longest-running SQL query.
- Shows the highest call count and other useful metrics.
- Used to identify slow methods/code that can be enhanced or improved.
SQL Activity Monitor
- Identifies slow-running queries at the database level.
- Useful for checking the loading of a form and its data on the screen.
- Helps spot blocking, expensive queries and missing-index situations.
Key Performance Points
Rules every developer must follow
- Do not use nested loops — fetching data in a loop-in-loop degrades performance at an exponential rate; try computed columns instead.
- No direct SQL in forms — use views and queries; direct SQL/SPs are acceptable only for high-volume reports.
- Use RecordInsertList when
insert()is overridden and looped. - Use a field list for all selections to minimise memory use.
- Add indexes where the SQL execution plan shows a missing index.
- Keep
ttsbegin/ttscommitclear and balanced; avoid multiple TTS blocks. - Use set-based operations with the necessary skip methods; avoid container/memo fields in set-based ops.
- Consider multithreading (Individual Task / Batch Bundling / Top Picking) for large workloads.
Prerequisites (Rule 5)
- Visual Studio with the Dynamics 365 developer tools.
- Trace Parser installed/configured for capturing and reading traces.
- Access to SQL Server Management Studio (SSMS) / Activity Monitor.
- Permission to view execution plans for the queries under test.
Code Example — Nested loop (avoid) vs. join (prefer)
// BAD: nested loop -> exponential performance hit
SalesTable salesTable;
SalesLine salesLine;
while select salesTable
{
while select salesLine // loop inside a loop
where salesLine.SalesId == salesTable.SalesId
{
// ...
}
}
// GOOD: single join retrieves everything at once
while select SalesId from salesTable
join SalesId, LineAmount from salesLine
where salesLine.SalesId == salesTable.SalesId
{
// ...
}Field list + firstOnly to reduce memory
SampleTable sampleTable;
// Only the needed fields, stop at first match
select firstonly SampleId, SampleValue, SampleStatus from sampleTable
where sampleTable.SampleId == _sampleId;Detecting a missing index (SQL)
-- Use the execution plan / DMVs to find missing indexes,
-- then add the corresponding index in the D365 table (source-controlled).
SELECT *
FROM sys.dm_db_missing_index_details;Trace Parser vs. Activity Monitor
| Aspect | Trace Parser | SQL Activity Monitor |
|---|---|---|
| Layer | X++ / application (trace) | SQL Server / database |
| Shows | Longest code, longest SQL, call counts | Slow / blocking queries |
| Best for | Finding slow methods to refactor | Form/data load & query tuning |
| Typical output | Metrics from a captured trace | Live query activity |
Points the interviewer wants to hear
- Trace Parser = longest-running code/SQL, call counts, other metrics.
- Activity Monitor = slow queries and form/data load checks.
- Avoid nested loops; use joins or computed columns.
- Use field lists,
firstOnly, set-based ops and balanced TTS. - Add missing indexes found in the execution plan.
Likely Follow-up Questions
- Which tool would you use to find the longest-running SQL statement?
- Why are nested loops so damaging to performance?
- How do you decide to add a new index?
- How would you troubleshoot a form that loads slowly?
Key Takeaway
Use Trace Parser to find the slowest code and SQL and Activity Monitor to spot slow queries and form loads. Then apply the fundamentals — no nested loops, set-based operations, field lists, balanced TTS and the right indexes — to fix them.