Improving Navision SQL performance in terms of disk I/O

knight
knight Member Posts: 45
edited 2007-04-11 in SQL Performance
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!

Comments

  • kine
    kine Member Posts: 12,562
    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)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • sc00by
    sc00by Member Posts: 43
    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
    Timo_Lässer Member Posts: 481
    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
    Timo Lässer
    Microsoft Dynamics NAV Developer since 1997
    MSDynamics.de - German Microsoft Dynamics Community - member of [clip]
  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from Navision Tips & Tricks forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories