NAV Team: Microsoft Dynamics NAV Changes by Version

[Blog bot]

Microsoft Dynamics NAV has had multiple changes to the underlying code in order to optimize performance of NAV on SQL Server. The chart below shows the changes by version.

REPEATABLEREAD

In order to use the REPEATABLEREAD, changes are needed after installing the latest hotfix to enable the option. The following information outlines this and should be used ONLY after testing and discussion with your SQL DBA. REPEATABLEREAD support was adding on the following hotfix version and later for NAV, it would be recommended to use the latest hotfix builds, the information below is to give a reference of when the support was added, to better understand if you are currently on a version that supports this change:

5.0 SP1 – (Build 30482 / KB 979135)

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;979135

6.0 SP1 – (Build 30609 / KB 978100)

https://mbs2.microsoft.com/Knowledge...b;en-us;978100

1. Apply the latest hotfix for the specific version of NAV following all the guidelines/installation instructions for updating to a newer platform hotfix.
2. Configure NAV to use the REPEATABLEREAD isolation level in Micrsoft SQL Server. You can do this by enabling the 4194304 flag in the Diagnostic field of the $ndo$dbproperty table in the NAV database. To enable the 4194304 flag, run the following TSQL statement against the NAV SQL database:

update [$ndo$dbproperty] set diagnostics = diagnostics | 4194304

for more information about Microsoft SQL Server transaction isolation levels, visit the topic 'Isolation Levels in the Database Engine' on Microsoft MSDN.

Enabling the REPEATABLE READ isolation level in NAV will improve general performance in situations where multiple users are experiencing blocking when they are entering journal entries, sales order entries, purchase order entries and similar tasks.

The difference between the SERIALIZABLE transaction isolation level and the REPEATABLE READ transaction isolation level is that SERIALIZABLE transaction isolation level protects against phantom reads. Therefore, enabling REPEATABLE READ transaction isolation level introduces a theoretical risk of phantom reads. The following example shows what this means from a C/AL perspective.

Note: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

MYTABLE.LOCKTABLE;
MYTABLE.SETCURRENTKEY("Document No.", "Line No.");
MYTABLE.SETRANGE(MYTABLE."Document No.", '1');
MYTABLE.SETRANGE(MYTABLE."Line No.", 1, 10);
IF MYTABLE.FIND('-') THEN
REPEAT
UNTIL (MYTABLE.NEXT()=0);
IF MYTABLE.FIND('-') THEN
REPEAT
UNTIL (MYTABLE.NEXT()=0);
COMMIT;



With SERIALIZABLE transaction isolation level, all existing records will be locked during the first "REPEAT UNTIL" loop and other users will also be blocked from inserting new records within the specified range. The record that has Document No=2, Line No=1 will also be blocked. Therefore, the second loop will always read exactly the same result as the first loop. With REPEATABLE READ transaction isolation level, someone can theoretically insert a new record within the mentioned range which will then appear as an additional record in the second loop.

To disable the 4194304 flag, run the following TSQL statement:

update [$ndo$dbproperty] set diagnostics = diagnostics ^ 4194304

Microsoft recommends thorough testing before making any changes to a live environment to ensure that there are no unexpected results.



Fast Forward-only (FFO) for Browse cursor with OPTION (FAST x)

Microsoft Dynamics NAV replaced the Cursor type from DYNAMIC to Fast Forward-only (FFO) for Browse when in a Browse transaction in hotfix releases. A Dynamic cursor is still used when inside a write transaction. The OPTION (FAST x) support was added after the change to FFO for Browse to improve performance. It would be recommended to be on the latest hotfix builds to include the OPTION (FAST x) support. No additional setup is needed once the hotfix has been implemented. Again, the following information is for a reference to know if you are on a build that has the OPTION (FAST x) support, it would be recommended if you are not that you obtain the most current hotfix release for the version you are on:

5.0 SP1 – (Build 29729 / KB 974798)

6.0 – (Build 29894 / KB 974798)

6.0 SP1 – (Build 29958 / KB 974798)

https://mbs2.microsoft.com/Knowledge...pqloymzuzyyqxm





Nick Haman

North America Senior Escalation Engineer



Источник: http://feedproxy.google.com/~r/Micro...y-version.aspx