Notifications
Announcements
No record found.
Has anyone tried turning on Allow Snapshot Isolation on a Dynamics AX 2012 R3 business data database? Any reason not to enable it for specific reporting purposes?
*This post is locked for comments
Revisiting this question. I see the LCS system diagnostics recommends that Snapshot Isolation be disabled (best practice), but what does it harm to enable it?
Hi Brandon Wiese,
Maybe this msdn article will give you an answer https://msdn.microsoft.com/en-us/library/ms188277.aspx
also another msdn article https://technet.microsoft.com/en-us/library/dd309734.aspx says:
Set READ_COMMITTED_SNAPSHOT to on. Performance testing has shown that Microsoft Dynamics AX performs better when the READ_COMMITTED_SNAPSHOT isolation option is set to on. You must use an ALTER DATABASE statement to set this option. This option cannot be set by using SQL Server Management Studio.
Thanks for the reply. I already understand the difference between Read Committed Snapshot Isolation as an optimistic implementation of the Read Committed isolation level, and Snapshot Isolation as a new isolation level. My interest in Snapshot Isolation is not to use within AX itself, but to use for the connection from SSAS (cubes).
LCS apparently checks if Allow Snapshot Isolation is enabled for the AX databases (as something to warn about), according to the following LCS release notes.
https://blogs.msdn.microsoft.com/lcs/2015/01/16/january-release-notes/
Circumstances that are checked for include --
But, no guidance is given for why enabling Allow Snapshot Isolation would be considered a problem when used from read-only connections purely to take advantage of a different row versioning strategy.
Of course Read Committed Snapshot Isolation should be enabled for the AX databases, and it has been that way for many versions now.
Maybe if I change the original post to Reporting and BI, and use a few keywords like OLAP, cubes, BI, SSAS, and Analysis Services, I can get Lance's attention. He seems to be one of the few at Microsoft who regularly respond to posts in this forum.
Lance, any reason not to use Allow Snapshot Isolation (along with MARS connection = true) on the SSAS datasource back to the AX business database?
I don't have data on what the impact would be of changing that setting for Dynamics AX.
There is a bit of info on the general impact of the setting here. msdn.microsoft.com/.../ms175492.aspx. Not sure its all that helpful though. It mostly just says writes are a tiny bit slower and more temp db space is required.
If you have a test AOS set up, maybe you could try running some operations with this setting on versus off to see if there's a meaningful impact. Maybe try adding and modifying records that are included in views used in a data cube, while processing the data cubes? If you've already done this it would interesting to know what you found.
What are you hoping will change in analysis services cube processing if you enable this setting?
While I'm still exploring whether true Snapshot Isolation is effective or not, my hope is to attain increased data consistency during processing. For example, processing many related dimensions or partitions within the same transaction using Snapshot Isolation should, in theory, allow them to all share the same row versioned view of the data acquired when the transaction was started. I've only seen a few minor cases thus far of processing issues due to changing data. I'm considering what strategies, other than simply processing again, can be used to acquire a fully consistenct view of the data where it is critical.
This post (if I read it correctly) seems to describe the issue pretty well, though a more extreme case.
gvkamath.wordpress.com/.../processing-using-snapshot-isolation-in-analysis-services
We have enabled ALLOW_SNAPSHOT_ISOLATION on our AX 2012 R3 business database (and also necessarily our model store, for reasons I will not explain herein).
According to our understanding of snapshot isolation, this setting will have no impact on AX itself, since it never uses an isolation level of Snapshot for any of its connections. Since AX was already using Read Committed Snapshot isolation, the TempDB database was already being used as a version store, though the capacity of the version store may need to increase, and thus we've increased (and are monitoring) TempDB accordingly to accommodate.
Using Snapshot isolation for our SSAS data source has accomplished the objective, i.e. improved data consistency and thus reduced processing errors by guaranteeing a consistent database view across all of the queries associated with a given SSAS processing batch. Attempts to process SSAS dimensions and then cubes under the standard Read Committed isolation level produced regular errors in production as data changed between the processing of dimensions and the cube itself. This was, of course, most common in the case of fact dimensions, as often times fact data (i.e. customer transactions) changes far more rapidly than supporting attribute data (i.e. customers).
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 4 Most Valuable Professional
Priya_K 4
MyDynamicsNAV 2
