How to use MS SQL Database Engine Tuning advisor for WMS related performance investigation

The focus of this article is Produmex WMS MS SQL version and it will give you an overview how to use, and utilize the MS SQL Database Engine Tuning advisor for performance related issue investigation.

Please note this topic and tasks are usually assigned to database administrators who has in depth knowledge on SQL server administration. BOYUM support is not to be tasked with any database administration related issues such as performance tuning of the SQL server itself, backups, housekeeping activities etc.

Produmex WMS uses many tools and complex SQL commands to operate. You have Mobile clients, Robot tool, Add-on functions. The MS SQL database engine provides you the functionality to profile the activities happening against your database.

In order to use the Database Tuning Advisor you can run a trance on your database to capture the sql commands executed. Then you can load the trace file and start the analysis. The steps are shown below, with the example of tracing the WMS Mobile Packing Flow form the Mobile client.

1. Configure your SQL profiler, and RUN
   
   
   
   
   
   
   
2. Execute the Mobile Packing Flow on your mobile client
   
   
   
   You will see each query run by the application in the SQL Server Profiler window.
3. Stop the SQL profiler and save the trace file.
   
   
   
   File -> Save as
   
   
   
4. Start the MS SQL Tuning advisor and load the trace file from the previous step.
   
   
   
   
5. Setup the Tuning advisor
   
   Choose the saved trace file
   Set the database for analysis
   set the database for tuning
   
   
   
   
   
6. Run the process
   
   
   
   
   
   
7. Review and apply recommendations
   
   Recommendations are shown:
   
   You can check the index recommendations first, and click in the link. It will show you the command you can Copy and Paste into MS SQL Management Studio and run it for your database.
   
   You can run these on by one to see if performance is improved.
   
   
   
   

Please note the steps above are not ultimate solution for performance improvement. Always consider the recommended specification for your installation.
Also doing regular housekeeping activities, archiving and database tasks suggested by the SAP Business One RSP tool can help you to have better performance and stable operation.

Useful links:
https://learn.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-ver16