SQL Query Tracing in Axapta 4.0 - Part 2

SQL Queries can be captured from the Axapta application, once the tracing is enabled as mentioned in the earlier post.

The traced queries can be captured from Axapta’s menu:
Administration -->Inquiries -->Database -->SQL statement trace log

The captured queries can also be filtered on the columns like:
Datewise, Userwise, Trace type wise,etc.

Steps to identify the necessary indexes to implement as per the captured queries:
          1. Save the captured queries into a ‘.sql’ file by replacing the substation variables.
This replacement is needed to get parsed by SQL server during the index tuning.

For eg: The captured query is:

SELECT TOP 1
A.LINEDEVICEPERMANENTID,A.LINEDEVICEID,A.COMPUTERNAME,A.ACTIVE,
A.LINEDEVICEPROVIDER,A.LINEDEVICENAME,A.RECVERSION,A.RECID
FROM SMMPHONEPARAMETERS A WHERE ((DATAAREAID=?) AND ((COMPUTERNAME=?) AND (ACTIVE=?)))

The same can be replaced (‘?’ is replaced with ‘’) as:

SELECT TOP 1
A.LINEDEVICEPERMANENTID,A.LINEDEVICEID,A.COMPUTERNAME,A.ACTIVE,
A.LINEDEVICEPROVIDER,A.LINEDEVICENAME,A.RECVERSION,A.RECID
FROM SMMPHONEPARAMETERS A WHERE ((DATAAREAID='') AND ((COMPUTERNAME='') AND (ACTIVE='')))


2. DETA (Database Engine Tuning Advisor) is the tool available in SQL server, which can be used to assess the workload and identify the necessary indexes for any performance optimization. A very handy tool to use and accessible from SQL Server Management Studio under:
Tools -->Database Engine Tuning Advisor

a. Create a new session in DETA.
b. Load the captured and stored .sql file to analyze.
c. Select the respective Database from the list to assess the load of the identified queries (.sql file will contain the identified queries)
d. Start the analysis
e. After the analysis, the necessary table INDEXES and sql STATISTICS will be identified and displayed.

3. Create the necessary indexes as identified in step #2 in Axapta.
The indexes have to be created only using Axapta, because of one way synchronization from Axapta to SQL Server.

Note:
1. The DETA will not only suggest the indexes to be created, but also provides the syntax to create. But this will be relevant only to create them directly using SQL server. The suggested INDEXES and STATISTICS can be created using SQL SERVER also, but leads to synchronization issues from Axapta.
2. This process helps to identify the necessary indexes, but also have to be cautious while creating the indexes. As we all knew, creating multiple and improper indexes will also affect the performance, hence be cautious too.

It is always ideal to handle the performance issues at the code level only as the first preference than going for these index creations as and when needed.

Hope, this helps for the next time optimizations ;-)