FOR RELEASE NAV 4.0 SP3 , NAP 5.0SP1, NAV 2009 & NAV 2013
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. REPEATABLE READ 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/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;978100
- Apply the latest hotfix for the specific version of NAV following all the guidelines/installation instructions for updating to a newer platform hotfix.
- "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.
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/Knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmyymyqksymxosooumlqkpqloymzuzyyqxm
Source http://blogs.msdn.com/b/nav/archive/2011/05/12/microsoft-dynamics-nav-changes-by-version.aspx