Hello, we have 150 Users with Nav3.7 and SQL-Server 2000 Running. At least 10 Times a week we have blockings in the SQL-Server so that Navision is hanging at several places. Mostly the reservation entry table causes the locking and we must manually kill the user because the blocking doesnt end automatically. Who can help ?
any help??? same problem here as well especially when posting gl,sales.
Hello there: I had the same problem, after a lot time checking the database and trying to improve the performance, we found (between another problmes) that Audit Trail table had not autoincremental id, we did this change ( convert id field to auto incremental field ) and Navision is working better.
"I hope i’m going to much off-topic on sql ": Does someone have the same problems with a native database?
Hi, I have been working in the area of performance of Navision on SQL Server for a while now and unfortunately there is no silver bullet solution. However the good news is that there is a solution, you need to do several things; - Redesign indexes so that they fit SQL Server rather than Navision ‘native’ Server - Minimize number of long composite indexes - Go through SIFT definitions and minimize the number of buckets - Put indexes on underindexed tables - Use IDENTITY (AutoIncrement) columns on some ledger/log tables - Review your code to eliminate SELECT statements caused by NEXT statements - Make sure your server is maintained properly (defrag/reindex/stats) - Implement server side caching on hot small tables - Go through application setup and disable long transactions such as Automatic Cost Posting - Minimize number of dimensions - Etc., etc. After you have done it properly your application will be flying. I cannot give you more details since performance troubleshooting is a major source of income of the company I work for (www.hmbusinesssolutions.co.uk), but I hope that the above helps. Hynek
Hi The problem of users locking each other out of tables happens regularly on Navision SQL. Although not that often with Navision standard table anymore so this suggest you are using an old version of Navision. If you use a table in Navision that will be written to by a number of users at the same time. Or even more of a problem a table that is parsed and then written to by a number of user you should call a locktable before the parsing starts. What you find is that users accessing the table will lock each other out of the table and the system eventually grinds to a halt. By using the locktable one user will wait until the other has finished with the table before processing continues. The wait is only milliseconds so the user will not notice. What Hynek says is true but if you are haveing those sort of problems with a large number of users then you need to replace the server or look at your network infrastucture more than Navision. Paul Baxter
Hi, just a reminder, LOCKTABLE doesn’t do anything on SQL until you read the data, it merely changes the isolation level and keeps the locks on the rows you read or accessed. SQL will isolate perfectly concurrent updates on same table if you don’t upset it by wrong design - SIFT, indexes, etc. Have you seen that the same SPID is blocking as well blocker when you run sp_who2 or dig into sp_lock? You can throw more hardware at it but it will come back to you once the database/table grow too much. Best regards, Hynek.