I am quite new to NAV as our current company is running NAV ver 3.7. We have quite a number of retail shops accessing to our server which is in our city areas. Quite often, the retailers will call our company up and complain that the NAV hangs and they cannot do any posting as there was a large volume of posting of Goods Receipt.
What really wonders was whether was it due to large SQL database? currently our database is around 60GB and it is still GROWING!!! so i wonder whether is it because of the huge database that slows down the accessing time. Is it a problem with the SQL or NAV?
Will time slice or achival of the data be of help to the processing time? Or would an upgrade of NAV to ver 5.0 be better?
Please help to enlighten me as i am really new to NAV?
Can you explain better “hangs”. It’s realted to table locking?
Your SQL Server is responding correctly (CPU, etc)? Are you using Terminal Services from other shops?
NAV 5.0 has a huge performance increase in SQL Server, but some times the problem is in another places.
for this, i will need to find out more whether r they using terminal services or they have direct access to our NAV server.
The word hang comes from the retailer side, but from what we know, the transaction is still on except that it takes a much more processing time to post the items which can take around 10 to 20 mins. Whether is it table lock or record lock, i am not to sure about it as i am quite new to the system.
Our consultant propose to us to upgrade from Nav 3.7 to Nav 4.0 SP3 instead of NAV 5.0 as the version (5.0) is quite new and they may have some bugs that have yet to be solved.
I think the consultant did something to the NAV also as previously the number of problem cases is almost everyday (at least 3 to 5 per day) but now it had been reduced to around 1 per day. I will go and find out what did the consultant did also…
But does anyone have this huge of database? and how do they maintain it?
firstly 60 GB is big, but not huge. If the system is designed correctly, then it should not lock like this. In fact this locking could be caused by many things.
I need to start this reply, by saying that really at this point you should consider getting a Navision/SQL expert to look at this system, you can easily make it much worse by playing if you are not certain what you are doing. But to answer your quesiton.
Firstly I am assuming by hanging, I assume you mean where the server just seems to not respond, and things like list forms, are just white space. If this is the case, then basically the reads/writes you are doing are overloading the hardware. Optimizing the hardware will make it better, but probably wont fix it. Take a look here Move to SQL server for an idea of the sort of hardware configuration that you need.
But to get these hang ups gone you are going to need to do a lot of work on the core application and especially look at any enhancements made by your partner.
Take a look on the site here Search for SQL Performance There is a wealth of information here about exactly your issue. And yes IT CAN be fixed. Please do me a favor, if you find any threads on the site that help you, please reply to it with either more questions, or just a thank you. We are trying to categorize and Tag threads, but I need the good ones bumped, so I know which ones are the most useful. Also please give a start rating if it helps you. Thanks.
SQL 2005: SQL Server General Statistics; Processes blocked; rises above 0; delay 10 seconds
Using this you could verify if you have basically blocking trouble; if not this indicates an index-problem. You mentioned this happens when “processing item posts”. Here there is a big potential for problems caused by SIFT tables! Reduce the number of buckets - this would also solve lots of blocking issues - and clean up the SIFT tables (e.g. using http://www.stryk.info/english/downloads.html ).
To analyze index problems you need to run SQL Profiler traces and look for “bad queries” with long Duration and high number of Reads …
And besides this: how’s your hardware setup, SQL Server configuration, etc.?
As David mentioned: search the forum and you’ll get plenty of advice about how to improve performance!
From what i know, our SQL and Windows had been upgraded to Enterprise version. Just to let you know that before that the system hangs very frequently but after when they did the upgrade of the server and also some fine tuning of the application, this had been reduced quite a lot.
Currently our server configuration are basically 8GB memory and 4 CPUs which i think are sufficient to meet the requirements for SQL and Navision. So i think the problems actually lies with the Navision itself. What i found out today was that one of the remedy for the hang processes, was to go to Navision, under database - information - session, you can see all the sessions that are connected to the system in the database session. If the user experience slowness or hang, you can actually see the user who is causing the problem by looking at the Blocking User ID. Once the user had been identified in the Blocking User ID, you can ask the user to stop what he/she is doing and the other user will be able to process. So what is wrong with that? Is it bug?
As for the above recommended steps to create an alert for SQL server agent, i am not sure whether i can do it or allow to do it as i am new to the system. Maybe i can put up for suggestions. Any more recommendation is welcome.
I really grateful that i found this forum, it really helps a lot. Thanks.
Well, the hardware seems sufficient.Regarding the RAM, I hope you have assured that the switches /3GB and /PAE are set in the boot.ini file and that AWE is enabled and configured in SQL Server; else you system is just using 2 GB out of the available 8 GB. (Are you using SQL Server 2000 or 2005?)
So as far as I could read it from your posting, it’s actually blocking issues you encounter. Most of the blocks are caused by SIFT tables, so reducing the number of SIFT-buckets will help. Other blocks are primarily caused by the C/AL code - bad programming, actually.
What you actually need to know is which resources - tables if you like - are actually blocked; here you need to look into the SQL Server’s “Activity Monitor” to get some clue. Once you have picked out the candidates you could start changing the NAV code …
Unfortunately, there are other reasons for sub-optimal performance caused by the NAV standard …
thanks Jorg. I will check it out tomorrow and report to you for any findings. Thanks. By the way, how do i reduce the number of SIFT buckets? In which areas do i reduce that and is there any recommended numbers for the SIFT buckets? Actually we no longer renew the maintenance contract for the developers therefore we have to do everything by ourself, and i am trying to learn as much as i can… so hopefully can gain more and more knowledge from the forum. Cheers!
That is just one aspect though. Just having ‘the right number’ of CPU’s or ‘enough’ RAM does not guarantee good performance. You need to consider your storage system, how you set up logical drives, what you use those drives for, your RAID level (NOT 5!!!), your network connections, etc.
There are many factors involved in performance issues. Hardware is typically not the biggest factor, unless the hardware is spec’ed out wrong. If you are on RAID 5 with 150 users and all of the data and logs on one logical drive, then changing to properly setup storage system might solve all of your problems. Chances are that it is a combination of all factors.
You did stumble into the right place (mibuso.com is the other one you will want to check out), lots of very good people in here, you’ll find many answers.
I noticed that you didn’t answer whether they were connecting via the Navision Client or through Terminal Services. We have been live on our system for a few months and my project team for part of that had to connect using Terminal Services over a wireless LAN - so seven users through one access point. We are now on the wrired lan using the Navision client and it is slower - especially when doing transactional activities - because we have 65 users going through a 1gig back-bone, go through TS and it flies. I am sure as everyone else here has said there are lots of ways to improve the SQL performance, but if they are using the client then it might be worth getting them on TS, quicker and cheaper [;)], which as an accountant I always prefer.
Hi all, sorry for the late reply, the users are connecting via Terminal Services to the Navision server. I am sure that the hardware had already maxed out to cater for the requirements for Navision usage. Therefore i think is some fine tuning that need to be done particularly on the MS SQL or Navision side. Understand from the last reply from Jorg that we need to set the SIFT tables… But currently now the “hang” case had dropped alot from daily problems to once a week. And i have give the scenario for the Blocking Users in the database information, dun know whether does it helps to diagnose the problems. Last but not least thanks all of ur help! appreciate it!
I think you really need to answer Daniel’s question about hardware. Yes its true that software tuning will generally get you more performance than Hardware, BUT if your drives are configured wrongly, then no amount of tuning will ever help. The two key questions are:
1/ How many physical drive are there in the dedicated RAID 10 array that you have your Database on, and
2/ Is the Log File on a dedicated RAID 1 or on a dedicated RAID 10 array?
You also want to go into SQL, and make sure its using all that RAM.
(From your post, it seems you know SQL quite well, but it still wont hurt to let us know how your server is configured.)
Dedicated drive arrays are often confused with partitions on the same drive array. If you have your data on G: and your log on H:, and those drives are both logical drives on the same array, you still don’t get any performance gains. Especially the transaction log needs to be on a dedicated physical drive array. If you can only afford one fast drive array, you will want to use that for your transaction log.
Except the original version of 4.00 - where there was a bug - every NAV table has a clustered index, created from the Primary Key! The change with 4.00 was, that with the new property “Clustered” one could define a different key than the PK as the clustered one. Another “bug” which persists in NAV 4.00 until SP3 is, that this propoerty “Clustered” is not set for any table - even though there is allways a CI! This flag is firstly set in 5.00 …
But: when fiddling with CI you should be absolutely aware of what you are doing, any mistake here will definitely screw up the system!
Re-Indexing should be considered a “must” for all tables - not just the SIFT tables -, same goes for “Statistics”. If you don’t use any optimization utility - as the “Performance Toolbox” or other stuff - you should at least use the standard Maintenance Plan task for re-indexing, restoring the current index-fillfactors!
The best way is to create a Maintenance Plan with a Schedule. This way it is done automatically at given intervals (like rebuild once a week and optimize each night) when no one is working.