MS Dynamics NAV 2017 slowness at database level different for different users

ChrisK

We recently upgraded a client to MS Dynamics NAV 2017 and one user was reporting slowness entering sales orders. The slowness was determined to be a delay at the quantity field validation of approx. 6 seconds. After more testing, this issue appears to affect other users but in different databases. For example, one user experiences the delay in Production database but not in the TEST database, another has the delay in TEST but not in Production database, and yet another has no delay in either database. We have checked object versions, role centers, user setup, config file, and are not able to explain the behavior. Has anybody seen an issue like this or know what could cause a delay at the user/database level?

ChrisK

It looks like we found the resolution. We set up a SQL Profiler trace (to see if some other activity was running) and ran it while entering a sales order in the database logged in as user who experienced a delay (just at the point where there was a delay) and then ran it again while entering a sales order in the database logged in as user who did not experience a delay. When comparing the trace files, we saw that there were lines under Event Class SQL:Batchcompleted with high duration numbers (>1000) for the user who had the delay.
After more investigation, we found that notifications were set up on the role center for the affected user/database. When the notification options were removed, the performance improved and there was no more delay in entering the sales order. When SQL Profiler trace was run again, the SQL:Batchcompleted lines were gone. It was tested on two different users who experienced the issue and the results were the same.
Thanks again for the suggestions!

Slawek_Guzek

Perhaps the delay is related to some other random or periodic activities going on in the database or on the server or in the network environment.

If your server is virtualized then random activity peaks at different unrelated guests may have an impact.

If server disks are SAN based it could be some periodic activities/maintenance on the SAN

For example in our virtualized environment we have a SAN snapshot running every so often, and it has a visible impact on our NAV - but the impact is only visible when the other guests have a load/activity peak at the same time. So overall it looks like a random issue - from time to time NAV slows down, for one user or another.

Try to setup the some monitring on the SQL and NST server and try to correlate NAV issues with unusual system activity peaks, perhaps this will help you to pinpoint the problem

Slawek

David_Singleton

It can also be data related, do the users have identical permissions and setup and are they working on the same test orders with same customer item location etc. Also are there any security filters set up?

ChrisK

Thank you for your input. We will try these suggestions and report back.

ChrisK

It looks like we found the resolution. We set up a SQL Profiler trace (to see if some other activity was running) and ran it while entering a sales order in the database logged in as user who experienced a delay (just at the point where there was a delay) and then ran it again while entering a sales order in the database logged in as user who did not experience a delay. When comparing the trace files, we saw that there were lines under Event Class SQL:Batchcompleted with high duration numbers (>1000) for the user who had the delay.
After more investigation, we found that notifications were set up on the role center for the affected user/database. When the notification options were removed, the performance improved and there was no more delay in entering the sales order. When SQL Profiler trace was run again, the SQL:Batchcompleted lines were gone. It was tested on two different users who experienced the issue and the results were the same.
Thanks again for the suggestions!

RemkoD

We've experienced a related performance issue caused by the 'My Notifications' functionality. To be more specific. We experienced slow performance on multiple pages in NAV, for example opening a service order card. The notification options 'Customer exceeds credit limit' and 'Customer has overdue balance' caused this slow performance. Currently we recommend users to disable these two options on the My Notifications page.

I expect Microsoft has fixed this issue in NAV 2017 Cumulative Update 09 application hotfix. Change on TAB 1518.

I have not tested this fix by Microsoft yet. Lets hope this does the trick.