Which Build for SQL2005

ta5
ta5 Member Posts: 1,164
edited 2007-10-12 in SQL General
Hi experts

We have to migrate a rather small customer from Navision 4.02 from Native DB to SQL Server 2005.

I think we will also do a technical upgrade to 4.03 because of the security model switch.
What Navision build would you recommend, e.g. is the SP3 alone ok or are there important *technical* hotfixes concerning sql server (2005)?

Thanks in advance
Thomas

Answers

  • DenSter
    DenSter Member Posts: 8,307
    Update 6, and disable the automatic index hinting.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • nunomaia
    nunomaia Member Posts: 1,153
    Yes, I agree with DenSter

    Update 6 is one of the best builds for SQL Server. You should read information about index hinting in http://blogs.msdn.com/microsoft_dynamic ... 40718.aspx
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ta5
    ta5 Member Posts: 1,164
    Thanks for your help on this.

    Just to be sure:

    1) The table $ndo$dbconfig is created and used by update 6 (at least if the database is created by finsql.exe of update 6). Is this correct?

    2) To turn off the index hinting just set the field "IndexHint" to No.
    Is this correct?

    Thanks in advance.

    Thomas
  • nunomaia
    nunomaia Member Posts: 1,153
    1- You must create config. table

    CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)
    GRANT SELECT ON [$ndo$dbconfig] TO public

    2 - Yes

    INSERT INTO [$ndo$dbconfig]
    VALUES ('IndexHint=No')
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ta5
    ta5 Member Posts: 1,164
    Thanks Nuno.
    This means that if the table does not exist, indexhinting is on?

    A follow up: If we are migrating from a native installation to a sql server 2005 installation, the impact of the indexhinting will probably not be very hard since in the native db we have used setcurrentkey anyway. Is this correct.

    Thanks again.

    Thomas
  • DenSter
    DenSter Member Posts: 8,307
    No that is not a correct assumption. SQL Server does not use indexes for sorting, at least not the way that you are used to in the native database server.

    Indexes on SQL Server are used for data retrieval, and the SQL Server engine has logic to determine the most efficient index for each and every query. With update 6, the default behavior is that index hinting is on, which means that NAV is forcing SQL Server to use the sorting order as an index. In many cases this is NOT the most efficient way to retrieve the data, and as a result index hinting slows the system down.

    One of my customers is currently testing update 6 on a new SQL Server 2005, and found that it was terribly slow, with people being locked out all over the system. We turned index hinting off, and performance was much better.
    Daniel Rimmelzwaan
    RIS Plus, LLC

Categories