Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tony Mei]

We are using NAV 2013 R2 and working to improve NAV and database performance. In SQL server we can see the most “expensive” SQL queries that were generated by NAV. But so far we couldn’t figured out how to trace back to NAV to find which NAV sessions and CAL codes generated the queries. Does anyone have any idea how to do that? Is there a way to relate SQL server session ID to NAV session ID?

——————————
Tony Mei

——————————

[Joe Shields]

Hi Tony,

You can use the SQL Tracing feature in the debugger to determine the SQL queries being created by a session. I usually will turn this on for my session the a test environment and go through the steps of the process I want to tune to capture the SQL that is being generated by NAV and see if it can be improved.

Here is a link to a blog post that explains how to use the SQL Tracing feature and the SQL Server Profiler. I particularly like the queries they have made for analyzing the results:

https://blogs.msdn.microsoft.com/nav/2012/09/18/example-of-how-to-use-sql-tracing-feature-to-profile-al-code/
They are referencing the 2013 version, but the concept is the same for all versions above 2013.

——————————
Joe Shields
Advanced Media Technologies
——————————
——————————————-

[Tony Mei]

Thank you Joe for the reply

——————————
Tony Mei

——————————
——————————————-

[Suresh Kulla]

Tony,

There is a tool on Codeplex called Application Profiler which you can use to check what is the corresponding SQL query for your NAV C/AL Code and how much time it is taking to execute that query. You can review those results and make the necessary changes to your code.

I have not developed that tool but used it in various scenarios like the one you mentioned and to solve some of locking issues.

Using the Application Profiler to Run Code Coverage in NAV 2013 R2

——————————
Thanks
Suresh Kulla
Navision Developer
ArcherPoint Inc.
Atlanta GA
(866) 343-4517
——————————
——————————————-

[Joe Shields]

Hi Suresh,

Thanks for the suggestion. I will also check out that tool

——————————
Joe Shields
Advanced Media Technologies
——————————
——————————————-