SQL Query Tracing in Axapta 4.0 - Part 1

The key thing for the optimization and performance of the application is a good code.

Optimization can be done in 2 prime ways:
a. Code review and cleanup.
b. Right indexes to the table.

In general, all the possible bottlenecks for the optimization of the application cannot be identified at the initial stages of the implementation, instead only over a period of time. To identify the long running queries, the built in trace utilities exist in the application.

Let’s learn on enabling the trace and capture the long running SQL queries as a first part.

Later about tuning the application by implementing the necessary indexes as secondary.

As a first part, to enable the trace for long running SQL queries:
1. Select ‘SQL Trace’ checkbox from SQL tab. Accessible from Tools >>Options
a. Set the Threshold limit in milliseconds for considering the long running SQL queries.
b. Select ‘Database’ option and click Apply.


Note:
1. This Database option is helpful to parse the queries using SQL SERVER than Ax based SQL Parser utility.
2. This trace checkbox should not be checked always, instead to be used only whenever the tracing is needed. Otherwise, the application’s performance will be affected.
3. This tracing will not capture the queries unless the ‘Client tracing’ option is enabled as mentioned.


Identifying and implementing the indexes for the application tuning will be posted next.