When customer is starting a new Dynamics AX 2012 instance, Microsoft Premier Field Engineer are often asked to support them on-site in case something goes wrong.
Because of wide nature of incidents, it is always necessary to setup the right tools in production to collect the relevant data and keep the system healthy. We also want to collect run time data to monitor the health of the system during the few hours of Go Live. This is critical to ensure right communication can be sent to the business users and top management.
This is equally true when Go Live is a limited one (pilot Go Live for small subsidiaries) or major deployment (Big Bang approach with transactional data migrated over the week end). This blog post aims to describe the few required tools and settings that should be enabled to secure the Go Live based on my personal experience as Dedicated Support Engineer.
1. Enable context info to find user ID
This is one critical setting to be activated on every transactional Dynamics AX AOS servers in order to find which Dynamics AX user ID is behind long running SQL queries. Therefore it is required to have this piece of information to eventually find the user responsible for SQL locking and to engage resolution with termination of SQL statement.
In Dynamics AX 2012, information about the user sessions can be directly included in the SQL connection context. To include user session information in the connection context perform the following steps:
- Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\DynamicsServer\6.01\Original (installed configuration). The last key, Original (installed configuration), is the key name for the current server configuration. If your system uses a different configuration than the original installed configuration, navigate to the currently active configuration.
- Create a string registry value called ‘connectioncontext’ and set the value to 1.
- Restart the AOS.
Next open SQL Server Management Studio and run the following query to look at all the sessions related to Microsoft Dynamics AX:
select cast(context_info as varchar(128)) as ci,* from sys.dm_exec_sessions where program_name like '%Dynamics%'
There is limited, if not zero, performance overhead so we only recommend to turn it off for all transactional and batch AOS. If Dynamics AX AOS running the Batches are dedicated, it may not be required because the user running the batch is usually already identified.
Tip: you can also use the [Quick - Activity Monitor with Cursor Support] query from [DynamicsPerf] to retrieve the following data: AX_Session_ID, AX_User_ID, Context_Info.
2. Enable Dynamics AX Kernel Hot Fix for Parameter Sniffing
Parameter sniffing can have a negative effect on the performance of your Dynamics AX implementation. With the default behaviour of the Dynamics AX Kernel, all similar queries will be using the same execution plan. In some case, when data distribution is uneven between companies for example, this can result in unexpected long running queries.
Microsoft Product Group released some Dynamics AX Kernel Hot Fix to overcome this issue. When fix is enabled, each execution plan is optimized per company instead of using a “one size fits all” approach. Because cost is minimal, more execution plans are compiled and stored, we strongly recommend you to enable the Fix before facing the issue in Production.
The fix exists on version AX 2009 SP1, AX 2012 R1, R2 and R3 at certain Kernel Build. For Dynamics AX 2012 R2, from kernel build 6.2.1000.7877, you can enable the fix by running the SQL statement:
UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ('DATAAREAIDLITERAL','PARTITIONLITERAL')
More information on parameter sniffing: http: //blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx
More information on parameter sniffing in combination with Dynamics AX:
3. Over size all database files to avoid frequent auto-growth
Because it is quite hard to estimate the database size of your Dynamics AX solution, you need to rely on the Auto-growth option from SQL Server Database setting to let the database files automatically grow when needed, that means when there is no more free space within the file.
Each database files auto growth events can last several seconds and have negative impact on the business. Therefore we recommend you to oversize the Database files to avoid any unexpected database files growth during the first few days of Go Live as shown in below picture.
The following recommendations are good practice to avoid such issues:
- Enable SQL Trace Flag 1117 so that all Data files are growing equally.
- Oversize each data file, Dynamics AX and TempDB, to 10 GB for example.
- Avoid percentage value in AutoGrowth (10% by default) and define fixed value like 200 MB
- Setup regular database file review and increase the file size as needed during a planned maintenance windows
Tip: some customer prefer to have very large TempDB files (8 files of 10 GB) without any Auto growth capacity (0 MB) to avoid unexpected zero space on the logical disk. Of course that is a valid option, but only if monitoring of % Free Space within each data file is operational.
You can now access the next blog post on this topic here.
Principal Premier Field Engineer