Navision performance issue

mihai.valcea

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

Marije_Brummel

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.valcea

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_Brummel

Can you give a list of the performance indicators? Which one are good/bad?

kriki

mihai.valcea wrote:

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

mihai.valcea

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_Brummel

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?

DenSter

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.valcea

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_Brummel

Hmm. Maybe it is interesting to find ount who told them it was optimised for Navision, and on what information that was based?

mihai.valcea

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.

DenSter

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.

thaug

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.

DenSter

Yes upgrading the executables is a quick win, which alone should give you a good performance increase because of better SQL Server communication.

bbrown

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.