We currently have Microsoft Dynamics NAV 5.0 SP1 87 Users NAS and 170GB database with Avg. Costing.
We have been on NAV since 2002. We have always suffered with deadlocks and have taken the following actions in the past which some of the actions helped but have not eliminated deadlocking.
Upgraded NAV to SQL Version in 2005 now running SQL 2008 R2 as DB with RAID 1 for OS RAID 1 for TransLog RAID for DB
48GB RAM on Server
Tweaked SQL memory and also table keys using proven methodology by SQL Preform. and SQL Sunrise.
Issue when posting Output Journals, Consumption Journals, Cash Receipt Journals, AP AR Item Journals we receive deadlocks quite a bit throughout the day. The Adjust Cost Routine is set to run at each posting. We have tried turning that off but takes all weekend to get caught back up.
I would like to hear from a user that has a larger database and user base that has made the move to NAV 2013 and eliminated deadlocks.
The problem may sit not in hardware or SQL optimization, although these issues are important, too.
Adjust Cost on Posting + Average Cost method eats resources heavily AND may cause deadlocks, but you didn’t mention in your post whether turning that off helped if not eliminate deadlock occurrences at all --which is theoretically impossible-- but at least minimize them…
Not all app areas you mentioned involve costing calculations. If deadlock problems arise, aside from purely technical issues first place I am looking for is Dimension usage in company, and particularly Analysis Views - how much of them are defined at all, and set up with Update on Posting= True. I had a client, who had one third of your concurrent user count, and system was put on it’s knees by ~30 of such AVs.
Returning to SQL tuning - here is a blog post from our SQL guru Jorg Stryk: Blocks & Deadlocks in NAV with SQL Server and subforum (now readonly, new posts go to Technical forum) for SQL server issues
Adjust Cost on Posting + Average Cost method eats resources heavily AND may cause deadlocks, but you didn’t mention in your post whether turning that off helped if not eliminate deadlock occurrences at all --which is theoretically impossible-- but at least minimize them…
I have turned off the automatic posting and most deadlocks were eliminated. The issue is we have over 8000 SKUs and when turning this function off I have to run the batch job. The batch job item adj. cost routine takes longer than a weekend to finish after turning the auto posting adjust cost off. Any other suggestions? We do use document dimensions but do not use Analysis Views just Account Schedules. I have additionally went through Jorg Stryk recommendations and implemented quite a few of those but still to no avail. Most of the locks state GL Entry, Item Ledger Entry, Warehouse Entry tables and during posting is when we are receiving the messages.