I recently ran into a problem where a customer upgraded from
an earlier version of Dynamics AX and was experiencing severe SQL locking and
blocking after going live. They worked with the SQL server team to identify
the lead blocker, but as it turned out the underlying problem was related to a
SQL configuration setting. When a AX database is
first created through the installer program it creates the database with
READ_COMMITTED_SNAPSHOT ON.
If this setting is turned off at any time the results will
be severe SQL locking and blocking. There may be times where you would
change the setting, like in the course of the Dynamics AX upgrade process when
the TempDB can grow very large. The key is to make sure it is turned back
on for normal AOS operation.
To check the setting you can use the following script:
select
name,is_read_committed_snapshot_on,snapshot_isolation_state from
sys.databases
In the results that are returned, check the
is_read_commited_snapshot_on setting for your Dynamcis AX database. A 1
will indicate it is turned on a 0 indicating it is off.
If that setting is off or 0 you can use the following
command to turn it on.
ALTER
DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT
ON
If you need to perform this you should schedule a down time
when there are no active connections to the Dynamics AX database, while the AOS
service 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 Database
Configuration.