[WK Yee]

Good morning all,

Reaching out to see if anyone can give me some information/help on an issue we are facing. We are seeing some major latency issues on NAV2017 (table locks, etc) after we moved our data from SAS to SSD drives. Has anyone else run into this issue? If so how did you resolve it? Any help would be much appreciated!

——————————
WK Yee
Business Systems Analyst
Visual Pak Companies
Waukegan IL
——————————

[Anthony Darden]

Unfortunately I do not have any advice to give, but rather assurance that the move to flash storage is a great move once your issue is uncovered and addressed.  Our performance gains were noticeable once we made the switch.  Please share with this forum once you have resolved the issue – many will benefit!

Thanks,

Tony

——————————
Anthony Darden
Director of IT
Protective Industries, Inc.
Buffalo NY
——————————
——————————————-

[Robert Thomas]

Are you log files on the same disk?

What process did you use to perform the transfer? Backup and Restore, Offline Move, something else?

Have you contacted any SQL DBAs or experts? They are really the best option all things considered.

——————————
Robert Thomas
Stamptech Inc
Chester VA
——————————
——————————————-

[Gino Pack]

I would be curious to how you moved the database as well. Without more detail (How it was moved, how your Log and Database files are set up, Are you using a VM to run your SQL etc..) it is hard to diagnose. 

Ultimately SSD should be a major performance improvement because you are not sending commands to a movable disk to read and write data.

It could also be that users transactions are being processed so much quicker, that tasks are bouncing off one another. Maybe it is something in the NAV application code that is causing the table locks?

——————————
Gino Pack
Director of Information Systems
Modern Optical International
ROSELLE IL
——————————
——————————————-

[Ron Ketterling]

One our clients had a similar situation some years ago with their new storage device with SSDs and hard drives. It turned out to be a driver incompatibility issue between the OS and the device. We recommended a Microsoft instance. The VAR dragged their feet, but eventually did and the problem was resolved

——————————
Ron Ketterling
President
Business Automation Specialists of MN, Inc.
Minneapolis MN
——————————
——————————————-

[Robert Thomas]

Have you had any luck figuring this out? I’d verify all drivers as the other commenter said, but if that doesn’t resolve you issue check out this MSDN Article on optimizing SQL performance in NAV.

I’m sure NAV is just like any other SQL intensive program. See if this MSDN Article has any relevant information as well.

By default, you database should try to load as much into memory as possible, to avoid disk IO. Check the min and max server memory thresholds. Even SSD is significantly slower than RAM.

——————————
Robert Thomas
Stamptech Inc
Chester VA
——————————
——————————————-

[WK Yee]

Hi All,

Sorry I had not checked back to this topic till now. Thank you all for your input so far. A little update on the situation. It is still not resolved. I am not the system admin for our SQL server, so excuse me if my terminology is not correct or accurate.

Since I posted this initial question, we have ran a bunch of SQL analysis tools and found a bunch of expensive queries with various NAV tables. Item ledger Entry and Reservation Entry tables being the most popular ones. We also ran various utilities against the SQL server itself and there were no alarming concerns. CPU, memory and disk usages were coming back clean.  We did increase the SQL server memory to 128G. Our database size currently is at around 100GB. We have also ruled out network infrastructure as a bottleneck.

Fast forward to yesterday,  we noticed on one of our production order that has 31 components in the BOM, that it took an average of 3-5 mins for the consumption to post in NAV. We tested this with our regular RF scanners (ADCS/VT100) as well as posting the consumption journal manually. While this posting was taking place everyone else on the production floor were getting table locks trying to do different functions through their RF scanners until the posting was completed. This happened again this morning.

At this point, we are still trying to figure out what is causing this. We are looking to possibly bringing in SQL Perform to help us. If you guys have any other ideas, Im all ears!

Thanks!

——————————
WK Yee
Business Systems Analyst
Visual Pak Companies
Waukegan IL
——————————
——————————————-

[Lew Hope]

I have asked and posted a couple of times and gotten no response. I am looking to get more info from others users. What impact disabling SmartSQL has on performance. ?I have seen performance issues in NAV. I worked with both the ISV and Microsoft. I found some code that needed fixed and the ISV has issued a hot fix. While troubleshooting with Microsoft tech. He felt that the issue was related to the code written. He said let’s fast forward to one of the last things I would do. We went into the NAV Administration tool. Under Database settings, we turned “disable SmartSQL” on by checking it. I saw an instance change in performance. I am curious to see if this works for you.

Resources:

Microsoft Dynamics NAV: Faster than ever.

https://blogs.msdn.microsoft.com/nav/2014/11/12/microsoft-dynamics-nav-faster-than-ever/

 

Directions EMEA 2016 – SmartQuery Tuning

http://blog.stryk.info/2016/10/12/directions-emea-2016-smartquery-tuning/

 

Dynamics NAV Server Configuration
https://msdn.microsoft.com/en-us/dynamics-nav/configuring-microsoft-dynamics-nav-server

Optimizing SQL Server Performance with Microsoft Dynamics NAV

https://msdn.microsoft.com/en-us/dynamics-nav/optimizing-sql-server-performance-with-microsoft-dynamics-nav

 

Troubleshooting Long Running SQL Queries Involving FlowFields by Disabling SmartSQL

https://msdn.microsoft.com/en-us/dynamics-nav/troubleshooting-queries-involving-flowfields-by-disabling-smartsql

——————————
Lew Hope
knk Software
Morristown, NJ
——————————
——————————————-

[WK Yee]

Hi Lew and Cynthia,

Thanks for the input. I will forward these links to the appropriate channels within our IT team here and see if it helps. Will update when I get anything back from them.

——————————
WK Yee
Business Systems Analyst
Visual Pak Companies
Waukegan IL
——————————
——————————————-

[Cynthia Priebe]

Now that you have narrowed it down, have you done a code review? 

If you have customization in this area and just upgraded it without refactoring, you may find the cause there.

——————————
Cynthia Priebe, MCTS, DCP
Senior Business Analyst and Project Manager
Liberty Grove Software
Grafton OH
——————————
——————————————-