Tracing with the Tools Menu
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
Tracing and logging are useful ways to monitor the system. They can help diagnose both errors and performance problems.
To set the tracing options, click the Tools menu, click Options, and then click the SQL link. You can configure the following SQL tracing areas:
Multiple SQL statements
Long queries
Warnings
Deadlocks
For information about setting up tracing in these areas, see Setting Up the Tracing Tools.
Multiple SQL Statements
The tracing of SQL statements enables you to do the following:
Examine all the SQL statements issued, including the degree of statement reuse.
Compare complex X++ queries with the actual output.
Examine the vendor-specific, SQL-generated statements against the SQL backend database. A generated SQL statement might include Select or Insert.
Copy Microsoft Dynamics AX SQL statements and re-execute them in the SQL vendor tools.
Long Queries
Long queries tracing enables you to define the maximum time (in milliseconds) that a SQL query can execute before the system terminates it.
Warnings
The warning system alerts you when code fragments or constructions force the kernel to modify its interpretation of your SQL statements. Performance-related warnings are also issued.
Warning text |
ID |
Possible actions |
|---|---|---|
XXX records in table 'myTable' retrieved from the database, but never used. Consider using FIRSTONLY, or a more selective WHERE clause. |
W-101 |
A change might not be needed because the client doesn't have to iterate through every record available in the buffer of retrieved records. However, sometimes a more selective WHERE clause can reduce this inefficiency. |
No fields will be selected from table 'myTable'. |
W-102 |
Check the source code. You might be selecting only virtual fields. You might be selecting feature key disabled fields. If so, consider disabling the table or module. |
No fields will be selected from table 'myTable' in join-statement containing GROUP BY. |
W-103 |
Consider whether an EXISTS join statement might be more efficient. |
The field 'myTable.myField', which appears in the GROUP BY list, is not stored in the database, and is thus ignored. |
W-104 |
Replace the display or virtual field that is listed in the GROUP BY clause with a field that is stored in a database table. Note Fields that are calculated during query execution are not stored in the database. |
Field 'myTable.myField' appears more than once in select-list, only the first is actually used. |
W-105 |
Remove the extra occurrences of the field from the select list. |
Field 'myTable.myField' is invalid in ORDER BY clause because an aggregate function is used. |
W-106 |
Remove the field from the ORDER BY clause, or replace the field with another field whose values haven't been aggregated. |
About to delete a record in table 'myTable', which was not selected for update (RecId is XXX). |
W-107 |
Add the ForUpdate keyword to the query that populated the table variable. |
About to update a record in table 'myTable', which was not selected for update (RecId is XXX). |
W-108 |
Add the ForUpdate keyword to the query that populated the table variable. |
Index hint to index 'myIdx' on table 'myTable' eliminated in For Update query, as index is NON unique. |
W-109 |
Remove the index hint or consider whether the index could be changed to unique. |
Index hint to index 'myIndex' on table 'myTable' is eliminated, since the index is disabled. |
W-110 |
Remove the index hint or consider whether the index could be enabled. |
Downloading the EntireTable-cached table 'myTable' in company 'DAT' exceeds the time-threshold (1000 ms): 1234 milliseconds used. |
W-111 |
Modify the query to process a limited portion of the table in any one iteration or consider increasing the time threshold. Note Increasing the time threshold might risk creating long database locks, or blocking or deadlocking other queries. |
Downloading the EntireTable-cached table 'myTable' in company 'DAT' exceeds the record-threshold (2000): 12345 record(s) retrieved. |
W-112 |
Modify the query to process a limited portion of the table in any one iteration, or consider increasing the record-threshold. Note Increasing the record threshold might risk creating long database locks, or blocking or deadlocking other queries. |
Calls to NEXT, update(), or delete() must be performed on the buffer on the selection transaction level, or within the same transaction (TTS) scope (RecId is XXX). |
W-113 |
Consider using the Optimistic Concurrency Control, which compares and increments record version identification values each time a record is updated. This can provide the flexibility of programmer-controlled transactions. This helps guarantee that the code is notified if another thread updates the record between the read and update of the current thread. |
The field 'myField' in table 'myTable' is used in a WHERE clause, but not stored in the SQL database (i.e. has no effect). The field may have been disabled by a configuration key. |
W-114 |
Remove the derived field from the WHERE clause. |
The field 'myTable.myField' appears in ORDER BY, but not in the GROUP BY list, and is ignored. |
W-115 |
Add the field to the GROUP BY list or remove the field from the ORDER BY list. |
The field 'myTable.myField ' in the SELECT list, or ORDER BY is invalid and ignored because it is not contained in the GROUP BY clause. |
W-116 |
Add the field to the GROUP BY clause or remove the field from both the SELECT list and the ORDER BY list. |
Since no unique index exists for table 'myTable', the kernel will internally make index 'myIndex' unique and use that. At least one unique index is required to update or delete records in the database. |
W-117 |
Create a unique index on 'myTable'. |
Since no indexes exist for table 'myTable', the kernel will internally enable the 'CreateRecidIndex' table-property and use the 'myIndex' index. At least one unique index is required to update or delete records in the database. |
W-118 |
Create a unique index on 'myTable'. |
SELECT on table 'myTable' has a WHERE part, but no index match. Query might table scan. |
W-119 |
Create an index on 'myTable' to improve performance. |
Call to WWW on XXX was converted into a simpler and less optimal record-by-record operation (YYY records affected by the operation). Reason: ZZZ. |
W-120 |
Create an index to avoid the record-by-record access plan. |
FORUPDATE cannot be used on view 'myView', since views are read-only. The directive is discarded. |
W-121 |
Rewrite FORUPDATE to target a table rather than a view. |
Update must be performed inside a transaction. |
W-122 |
Place a transaction scope around your Update. |
Delete must be performed inside a transaction. |
W-123 |
Place a transaction scope around your Delete. |
Deadlocks
Deadlock detection is an integrated part of Microsoft Dynamics AX. Deadlock detection remains in effect even if you disable the Deadlocks trace feature.
Most deadlocks are caught within try blocks in X++, meaning that the operation is re-executed. Potential problems might be hidden.
Tracing deadlocks can be used to identify tables or other database resources that cause conflicts between concurrent queries.
See also
Optimistic Concurrency Control
Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.