AX Performance – Checking key SQL Server configuration and database settings
It’s important to configure your SQL Server instance and your AX databases properly in order to achieve optimal performance on your Dynamics AX environment. This blog post aims to help you do that by providing some T-SQL scripts that can check key settings and highlight possible issues you need to address. The scripts don’t cover all the things that can impact performance, nor do I at this stage go into details as to what the settings should be, but the information collected will be of interest to technical resources (including Microsoft Support) and could establish some quick wins that you can implement.
If you’re interested in investigating these settings in more depth yourself, the following blog post by my colleague Glen Turnbull provides some additional reference materials:
Please see the next blog post in this series for my comments on analyzing the settings:
MSDN Blogs > Microsoft Dynamics AX Support > AX Performance – Analyzing key SQL Server configuration and database settings
http://blogs.msdn.com/b/axsupport/archive/2015/09/25/ax-performance-analyzing-key-sql-server-configuration-and-database-settings.aspx
You can use the scripts attached below to check your current key SQL Server configuration and database settings to see if you need to make some changes at the instance or database level. There is a script for AX 2009, AX 2012 RTM, and AX 2012 R2/R3, so please select the appropriate one and run it from SQL Server Management Studio (SSMS). You can either send the output to screen and take some screenshots, or save the output as a report. You may need to adapt the scripts slightly to get them to collect the information we are interested in analyzing if you use non-standard AX database names.
— Disclaimer:
—
— Microsoft provides programming examples for illustration only, without warranty
— either expressed or implied, including, but not limited to,
— the implied warranties of merchantability or fitness for a particular purpose.
— This mail message assumes that you are familiar with the programming language that
— is being demonstrated and the tools that are used to create and debug procedures.
—
— This source code is freeware and is provided on an “as is” basis without warranties of any kind,
— whether express or implied, including without limitation warranties that the code is free of defect,
— fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of
— the code is with the end user.
—
— Please note that you may need to adjust the scripts a little if your AX database names are non-standard
SQL_Config_Scripts_For_AX2009_AX2012RTM_AX2012R2_AX2012R3.zip
