About Navision Performance

Dear all,

My Navision version is 3.6. I have the question for the Navision performance.

The user report Navision application too slow, so I set the counter to monitor server performance...(check object is recommendation from doc. "Tuning Navision for better performance")

I found the object doesn't met the best values, the details...

1) Physical disk / Avg. disk read queue length : server value is 47 but the best value is <2

2) System / Context Switches/sec : server value is keep 17000~23000 but the best value is <8000

Is these two object problem for this case? and how to tuning? Anybody help me?

Thank you so much!

Server details:

- Server IBM X3650 Dual Xeon CPU

- 4GB RAM

- Array 0 | RAID mirror for SQL Log, OS : WinSrv 2003, and MS SQL Server 2000 Std.

- Array 1 | RAID 5 for Data 70GB  

  • This is a broad subject and there may be a lot you can do. One thing is the hardware, but equally there is Navision indexes, coding, what modules you are using, size of database, users etc. I would suggest first searching the Forum as there are a lot of posts around this subject. Ideally you need a Nav performance consultant (someone with good knowledge of Navision and SQL performance) to help you. Depending on your needs/wants?
  • RAID5 IS BAD!!!!!!

    Better use 2 disks in RAID1 for DB and 2 different disks in RAID1 for log. This is really the MINIMUM configuration.

    I don't know how big your DB is and how many users you have.

    Also your Navision DB should be prepared for SQL (Maintaining or not maintaining SQLIndex and SQLSIFTIndex). But this is something for Navision-on-SQL specialists.

    Search the forum and also www.mibuso.com for more info on it. 

  • sleeky:

    1) Physical disk / Avg. disk read queue length : server value is 47 but the best value is <2

    2) System / Context Switches/sec : server value is keep 17000~23000 but the best value is <8000

    - Server IBM X3650 Dual Xeon CPU

    - 4GB RAM

    - Array 0 | RAID mirror for SQL Log, OS : WinSrv 2003, and MS SQL Server 2000 Std.

    - Array 1 | RAID 5 for Data 70GB  

    Hi!

    As already mentioned, one crucial issue is the RAID 5 for the database - this should be changed in first priority. According to that - as far as I could read it from your posting - you have some "issues" with distributing the various database files on the disks. There are several thing which must be regarded:

    - OS, SQL Server Programs, Swap on dedicated drive RAID 1

    - System databases master, model and msdb on dedicated drive  (RAID 1) if possible , else leave it on the OS/SQL drive

    - System database tempdb MUST be stored on a dedicated drive (RAID 0 or 1)

    - Dedicated drive for NAV Database data (mdf/ndf) RAID 1

    - Dedicated drive for NAV Database transaction log (ldf) RAID 10 - on this drive NOTHING ELSE MUST BE STORED!!!

    Implementing this or similar disk-subsystem will decrease the number of request in the queues!

    The huge number of Context Switches indicates that Hyperthreading is enabled - Hyperthreading does not work with SQL Server! Disable it!

    4GB Ram for a 70GB database is somewhat small. Pity, with using SQL Server 2000 the system could not allocate more than 2 GB of RAM ...

    ... to optimize the usage of this pretty smal cache it would be necessary to do a complete re-work of the index-structure.

     

    So, there are thing you could do to improve your performance, I hope these advice could help you a little!

     Best regards,

    Jörg

  • In reply to Jörg A. Stryk:

    Hi Jörg,

     Thank you very much for your help and comment.

    I will set the disk to :  (1) Array 1 - RAID 1 for WinSrv 2003 OS, MS SQL 2000 and NAV Database transaction log (ldf)

                                    (2) Array 2 - RAID 10 for NAV Database data (mdf/ndf)

    This setup is budget issues.....

    And (1) Would you teach me how to disable the hyperthreading function for SQL 2000?

           (2) Would you tell me how to re-build the NAV database index?

    Thank you so much!

     Best regards,

    Sleeky

  • In reply to sleeky:

    Hi!

    Changing the RAID is a good measure! But have in mind, that the most crucial part is the NAV Transaction Log file: this has to be stored on a dedicated and exclusive drive - nothing else here - to gain performance; with the current setup you'll still waste performance. Especially when your system starts "swapping" due to the small amount of RAM, the TLog would be hardly reachable, and the perfromance will vanish ...

    If it is an option, then you should at least consider to store the TLog of NAV database also on the RAID 10 volume to benefit from the speed of "striping". This array needs to have at least the double size of he database size, as during some maintenance jobs the TLog could be expanded to the same size as the dabtabase.

    (And besides: when I am talking about disks or volumes I always mean "physical disks" - logical drives are no option, of course)

    Is Hyperthreading really enabled - I was just guessing? If you have a real dual CPU - two physical CPU - server, then with HT 4 CPU would be shown (actually faked). HT is a feature which is usually enabled/disabled in BIOS.

    Re-building indexes:

    Well, with standard features you could do a minimum maintenance - e.g. using Maintenance Plans or SQL Server Agent jobs - to reorganize indexes with restoring the original fillfactor. According to this, you should also take care about your "index statistics" (if you search this forum you'll find lots about this).

    An advanced index optimization only could be done with using special utilities; just to mention my own "NAV/SQL Performance Toolbox" (and there are other utilities around, too).

    Best regards,

    Jörg

  • In reply to Jörg A. Stryk:

    Hi Jörg,

    Thank you so much, more helpful information....

    Hum...My luck's in.

     Best regards,

     Sleeky

Related
Recommended