Navision performance issue

mihai.valceamihai.valcea Member Posts: 95
edited 2006-10-01 in Navision Attain
Hi all,

I have a performance issue for a Navision 3.6 SQL option database.
The details are as follows.

1. server Nec 5800 120 lh, 4 GB Ram, raid 5 (5 hdd on 10000 rpm) - recently full updated will drivers and others
2. database on raid 5 and t-log on different sata hdd.
3. 30 concurent users on 13 gb database (9 months of activity).
4. index defrag and statistics updated
5. performance indicators checked and half of them are bad.

The application performance is not acceptable for the customer and I need to do something. I did almost all I found on documentation.

What else can I do, but not to change the server?

thanks

Comments

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    First of all make a tech-upgrade to 4.0 SP2.

    This allows you to play more with the SQL stuff.

    Then, if server, network and clients are OK you need to analyse which transactions perform bad. You need to make a plan per transaction what to do about it.

    If I read correctly the Logfiles are not on the Raid 5?
  • mihai.valceamihai.valcea Member Posts: 95
    Thanks for reply.
    Is not possible to make update, at least 6 month from now on.
    The database is on raid 5 together with so and the t-log is on single sata driver.
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Can you give a list of the performance indicators? Which one are good/bad?
  • krikikriki Member, Moderator Posts: 9,086
    1. server Nec 5800 120 lh, 4 GB Ram, raid 5 (5 hdd on 10000 rpm) - recently full updated will drivers and others
    2. database on raid 5 and t-log on different sata hdd.
    3. 30 concurent users on 13 gb database (9 months of activity).
    4. index defrag and statistics updated
    5. performance indicators checked and half of them are bad.
    1. Kill RAID5 and put a RAID1 or 10
    4. statistics must be done every night ; index rebuild once a week

    Like Mark Brummel said : tech upgrade to 4.0SP2 and then you need a finetuning of the SQL-indexes and SIFT-fields.

    Search also Mibuso for a lot of other tips on SQL-option-performance
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • mihai.valceamihai.valcea Member Posts: 95
    Performance Indicators (bad one) for 5 hours of database utilization are as follows:

    Physical disk READ
    Avg. disk read queue len. (database partition): Average = 0,9 Maxim=13

    Physical disk WRITE
    Avg. disk write queue len. (so partition): Average = 5,8 Maxim=11
    Avg. disk write queue len. (database partition): Average = 15,8 Maxim=249

    MSSQL
    Pages split/sec Average = 0,6 Maxim = 10,6
    Full scan/sec Average = 0,15 Maxim = 2,13
    Lock Request/sec Average = 5200 Maxim = 160000
    Lock waits/sec Average = 0.013 Maxim = 0.66

    Should be nice to be raid the only problem but I don't think so.

    Thanks
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    If changing the RAID system is not an option and you have everything set up properly like maintenance plands, indexing etc. I would recomend investigating what transactions cause the most problems. This is quite a challenge and can be time consuming.

    Why is changing the RAID system and upgrading ro 4SP2 such a problem? Don't you have a weekend that you can use for that?
  • DenSterDenSter Member Posts: 8,304
    I just love it when people ask 'what is the problem' and we give them an answer, and they go 'no that is not possible'

    With small numbers of users (up to 10 maybe 15 users) and a really good machine, RAID 5 will be your friend. You go over that, all of a sudden RAID 5 becomes your problem. It has something to do with the parity calculations slowing down database write operations. Check any documentation, and you will find that RAID 5 is not recommended for wite intensive database applications, such as ERP systems.
  • mihai.valceamihai.valcea Member Posts: 95
    This Navision implementation is made on a chemical factory and it's sales. In this case is very difficult to stop the system. The problem is to convince the customer that server raid need to be changed because he received the server as optimal configuration for Navision and now are problems with this configuration.
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Hmm. Maybe it is interesting to find ount who told them it was optimised for Navision, and on what information that was based?
  • mihai.valceamihai.valcea Member Posts: 95
    The server was recommended by NEC commercial as departamental database server.
    And unfortunatelly this server is said that have some automatic tools to configure as raid 5 (maybe for commercial purposes). This is the reason the customer received the server with raid 5.
    I suppose I'll convince to convice to change the raid and all the problems will go away.
  • DenSterDenSter Member Posts: 8,304
    No you should definately not tell them that changing RAID will solve all their performance problems. It will likely make it quicker, but it might still not perform as quickly as they are expecting.

    Given their number of users and their system usage RAID 5 might not be the cause of the performance problems. Up to a certain system load it doesn't matter much which RAID level you have. It's when you start really working on the machine that it will start affecting performance.
  • thaugthaug Member Posts: 106
    I would still point to the disk system here.

    Are you using simple or full recovery on the logs? Full on the SATA drives doesn't sound like such a good idea.

    Funny thing with RAID 5, is that I see it often suggested in a lot of SAN installations. They look at me like I have two heads when I say I would want RAID 1+0.

    Check out some of the recommendations in the "Tuning Navision for Better Performance" document. I think it is in the downloads section. I think that some of the other performance tuning stuff from MS is also in downloads.

    I believe that 3.6 also had some SQL issues. I would suggest a technical upgrade to at least 3.7. For 30 GB, it shouldn't be too difficult or take too much time, especially on some decent hardware.
    There is no data, only bool!
  • DenSterDenSter Member Posts: 8,304
    Yes upgrading the executables is a quick win, which alone should give you a good performance increase because of better SQL Server communication.
  • bbrownbbrown Member Posts: 3,268
    Performance issues rarely have a single solution. They are usually a combination of issues that together result in unacceptable performance.

    RAID 5 vs. RAID 10

    The two RAID versions have about the same read performance, but RAID 5 has about 1/2 the write performance. RAID 5 does have its uses. It can be useful (and cost-effective) in systems that see a lot of reads and little writes. An example would be a database used for reporting only.

    Considering the small datase size (13 GB) and the number of drives (5), the RAID is probably not a major factor in your performance issue but I expect it is contributing.

    Suggestion: Reconfigure as RAID 10. Either use 4 drives and keep the 5th as a hot spare, or add another drive and do 6 drives.

    Transaction Log

    An often over-looked issue is the write performance of the transaction log.

    When a user makes a change to the database (insert, modify, delete, etc.), the change is immediately written to the transaction log and also held in memory. Once the change is written to the transaction log, the users connection is released and may continue with the next task.

    I have no direct experience with using SATA drives in these systems so I cannot comment on them. However the following quote fro the HP website raises questions in my mind.
    As the next step in the evolution of ATA hard drives, SATA is directed towards customers requiring the best price advantage for entry level servers and external storage deployments in non-mission critical, low workload environments.

    Auto-expansion of data/Log files

    This can create performance issues if it is occurring during high system activity.

    Navision code issues (key selection, etc.)

    I would expect this to be most of your issues. Upgrade to the new executables to gain access to the new commands (FindFirst, FIndLast, and FindSet).

    Also use the Performance Toolkit to track issues with specific functions and look into locking and blocking issues.
    There are no bugs - only undocumented features.
Sign In or Register to comment.