All Microsoft Dynamics AX databases must use the same SQL collation.
- Set COMPATIBILITY_LEVEL to 120 for SQL Server 2014
- Set COMPATIBILITY_LEVEL to 110 for SQL Server 2012.
- Set COMPATIBILITY_LEVEL to 100 for SQL Server 2008 / 2008 R2.
- Set READ_COMMITTED_SNAPSHOT to on.
- Set ALLOW_SNAPSHOT_ISOLATION to on
- Set AUTO_CREATE_STATISTICS to on.
- Set AUTO_UPDATE_STATISTICS to on.
- Set AUTO_UPDATE_STATISTICS_ASYNC to off.
- Set AUTO_SHRINK to off.
- Set AUTO CLOSE to off
- Max Degree of Parallelism set to 1
http://technet.microsoft.com/en-ca/library/dd309734.aspx
https://blogs.msdn.microsoft.com/axsupport/2011/09/22/severe-sql-locking-and-blocking-on-dynamics-ax/
Sur un serveur SQL avec beaucoup de base de données AX, il est long de vérifier toutes les options sur chaque base de données. Voici une requête afin de vous faciliter la vie.
SELECT
d.name AS [Database Name],
compatibility_level,
collation_name,
[Auto Shrink] =
CASE d.is_auto_shrink_on
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END,
[Auto Close] =
CASE d.is_auto_close_on
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END,
[Auto Create Statistics] =
CASE d.is_auto_create_stats_on
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END,
[Auto Update Statistics] =
CASE d.is_auto_update_stats_on
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END,
[Auto Update Statistics Async] =
CASE d.is_auto_update_stats_async_on
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END,
snapshot_isolation_state_desc AS [Allow Snapshot Isolation],
[Read-Committed Snapshot Isolation] =
CASE d. is_read_committed_snapshot_on
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END,
[Page Verify] =
CASE d. page_verify_option
WHEN 0 THEN 'NONE'
WHEN 1 THEN 'TORN_PAGE_DETECTION'
WHEN 2 THEN 'CHECKSUM'
END,
[Recovery Model] =
CASE d.recovery_model
WHEN 1 THEN 'FULL'
WHEN 2 THEN 'BULK_LOGGED'
WHEN 3 THEN 'SIMPLE'
END,
[Database State] =
CASE d.state
WHEN 0 THEN 'ONLINE'
WHEN 1 THEN 'RESTORING'
WHEN 2 THEN 'RECOVERING'
WHEN 3 THEN 'RECOVERY_PENDING'
WHEN 4 THEN 'SUSPECT'
WHEN 5 THEN 'EMERGENCY'
WHEN 6 THEN 'OFFLINE'
WHEN 7 THEN 'COPYING'
END
FROM sys.databases d
SELECT name, value
FROM sys.configurations
WHERE configuration_id IN (109,1539,1544)
WHERE configuration_id IN (109,1539,1544)