Improving Navision SQL performance in terms of disk I/O

knight

Hi there everyone,

After upgrading our client from 260 SQL to 370 SQL, and changing the server to RAID 5 the performance dropped dramatically, especially in terms of disk reads. We weren't sure what the problem was and tried in vain to fix it.

After a bit of screwing around we discovered that going to File, Database, Alter, Options Tab and turning off "Allow Find as you Type", the Disk Queue length dropped astronomically! Since then, the performance of the database has been much much better.

So if you have a client with Disk I/O performance problems with Navision SQL, please turn off "Allow Find as you Type" and see if that helps!

kine

And NEVER USE RAID 5 FOR MS SQL or another DB...

(and there is planty issues for ptimizing application and HW for MS SQL - see SQL Server Resource Kit.pdf and w1w1PerfTGuide.pdf from MBS TOOLS CD)

sc00by

This looks really interesting - especially as the help function also says that this can be a performance problem.

This doesn't appear in the Options on version 3.60 - is it only available from 3.70?

Any way of forcing 3.60 to stop allowing the Find as you Type behind the scenes by altering a SQL table :?:

Regards,

Rob.

Timo_Lässer

kine wrote:

And NEVER USE RAID 5 FOR MS SQL or another DB...
[...]

I agree fully with you!
If you want to crash your database, you only need to use RAID5. #-o

Never, really NEVER use RAID5 for DBMS! [-X [-X [-X

kriki

RAID5 is only usefull for static data (data that is read often but changed little). Reading will be faster than on a RAID1+0 because the reads are divided over several disks (DB's write all the time on the disks!!!!).
On the other hand, it slows down on writing, because for every write on 1 of the disks, first the other disks (minus 2 disks) must be read and than the data can be written on this disk and another disk to complete the operation and to be sure that if 1 disk fails, you just have to put another without loosing any data.

kriki

[Topic moved from Navision Tips & Tricks forum to SQL Performance forum]