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

Extending the Tracing Tools

Optimistic Concurrency Control

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.