I recently ran into a problem where a customer upgraded froman earlier version of Dynamics AX and was experiencing severe SQL locking andblocking after going live. They worked with the SQL server team to identifythe lead blocker, but as it turned out the underlying problem was related to aSQL configuration setting. When a AX database isfirst created through the installer program it creates the database withREAD_COMMITTED_SNAPSHOT ON.
If this setting is turned off at any time the results willbe severe SQL locking and blocking. There may be times where you wouldchange the setting, like in the course of the Dynamics AX upgrade process whenthe TempDB can grow very large. The key is to make sure it is turned backon for normal AOS operation.
To check the setting you can use the following script:
In the results that are returned, check theis_read_commited_snapshot_on setting for your Dynamcis AX database. A 1will indicate it is turned on a 0 indicating it is off.
If that setting is off or 0 you can use the followingcommand to turn it on.
ALTERDATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOTON
If you need to perform this you should schedule a down timewhen there are no active connections to the Dynamics AX database, while the AOSservice is stopped.
Other support team members have encountered issues with this SQL setting if a customer's database was not created through the AX setup program but rather restored from a "template" database in which RCSI was turned off.
For additonal information on installations check out the white paper - Planning DatabaseConfiguration.
Thanks Becky. Has the team updated the white paper for AX 2012?