I am reading the Dynamics AX Database Configuration checklist written by David Reinhold. A nice document btw. There he says that AX shold have the database set up with Read-Committed Snapshot Isolation. My question is if this applies to NAV also.
According to this article http://technet.microsoft.com/en-us/library/ms188277.aspx readd Commited snapshot isolation is more like the kind of isolation we are used to in Native db and are more efficient then the default row based read isolation level.
It would be interesting to hear from someone who has been deeper into this and can explain this.
It is an excellent question Since this query is very valid one and which affects both Ax and Nav, I will raise this with my contacts at Dynamics product team. I will post updates here as soon as I hear from them.
Finally I have had the following response from MS. I hope it helps. Please let me know your thoughts.
Dynamics AX and NAV are vastly different products.
If Read-Committd-Snapshot Isolation level is turned on at the database level then not only would I expect NAV to perform worse but certain errors would also occur that normally does not occur. The reason is that Dynamics NAV is not written to support (RCSI). If RCSI is set to on then two users cannot modify the same records while in different transactions, if they do one of them will receive an error when the transaction is committed.
Add to that the extra administration that SQL server has to deal with to maintain the extra copies of rows.
AX is largely written to support the optimistic concurrency model which means they can handle errors like this. Also they have probably optimized their code to avoid update-conflicts.
We should be able to use RCSI for Business Intelligence tasks, but we don’t have any concrete plans for this yet.
The big issue I see this that NAV does not have a TransactionType that corresponds to the isolation level required for this feature. If it did that might open some possibibilities.
So if I understand you correctly, we should not be altering the DB default for Nav, as it sets it differently depending on the type of the transaction (read vs write)?
Any generic application using SQL Server (such as NAV) needs to explicitly make use of the Snapshot isolation level to make use of it, by issuing the isolation level request for its transactions.
However, as an addebdum to this, with the READ COMMITTED form of Snapshot, any application that normaly utilizes READ COMMITTED (using locking) will then automatically use the READ COMMITTED SNAPSHOT using row versioning.
Since NAV does not make use of READ COMMITTED it would never use this style of snapshot isolation, even if it was set on the database.
And since it also does not explicitly using a Snapshot isolation level, it will never actually use either of the Snapshot isolation level flavours. As babrown says, it uses READ UNCOMMITTED and SERIALIZABLE.
There have been investigations into using these isolation levels, but there is a performance cost and transaction semmantics that the application (AL) relies on would be broken.
It is an area that could well be utilized in future version of NAV though. But for now, do not enable this option on a database for NAV since there will be an unnesessary peformance hit but with no concurrency benefit (and also a growth of tempdb).
Since this query is very valid one and which affects both Ax and Nav, I will raise this with my contacts at Dynamics product team. I will post updates here as soon as I hear from them.