Welcome — Ax Database Configuration Checklist part 1


Welcome to the Dynamics Ax Performance Team’s blog.  We’re putting together a team introduction and hope to have it posted within the next week or so.  The first entries will discuss SQL Server 2005 configuration and Best Practices for Dynamics Ax, but we’ll be covering a much wider range of topics over the coming months, such as Dynamics AOS configuration, X++ profiling, and leveraging new features of SQL Server 2008.


My name is David Reinhold and I specialize in SQL Server database and BI performance.  I spent nine years in Microsoft Consulting Services (MCS) practice before joining the Ax Performance Team in December, 2007.


Here is the Part 1 of the Database Configuration Checklist, covering configuration of: Operating System performance settings, SQL Server 2005/2008 Service configuration, and SQL Server 2005/2008 Instance configuration (updated to include special considerations for Windows Server 2008).


The format of the Checklist includes instructions on How to check and perform the configuration steps, Why we’re making the recommendation, and References.


We’ve highlighted configurations that are specifically tuned for Ax databases, but there are also some general guidelines.  Rather than re-invent the wheel, we’ve inlcuded links to authoritative SQL Server Best Practices documentation available on the web, primarily on Microsoft sites.


Database Configuration Checklist Part 1


 Assumptions:



  • Dedicated SQL Server 2005 Server (does not run any other major applications besides SQL Server)

  • Single instance of SQL Server dedicated to running Dynamics Ax database.

OS


q  Set Advanced Performance Option->Processor scheduling: Best Performance of Background Services.


 


q  Set Advanced Performance Option->Memory usage: Best Performance of Programs (does not apply to Windows Server 2008).


  



Windows Server 2003:


My Computer->Properties->Advanced tab->Performance->Settings button->Advanced tab->Choose Processor Scheduling-Adjust for best performance of Background Services, Memory Usage-Adjust for best performance of Programs.


Windows Server 2008:


My Computer->Properties->Advanced System Settings->Advanced tab->Performance Settings->Advanced tab->Choose How to Allocate Processor Resources->Adjust for Best performance of background services.


 


q  Ensure boot.ini parameter /USEPMTIMER is present on AMD64 systems.


 



 


SQL Instance – Service Config


q  Run SQL Server service under a Domain Account with minimum necessary privileges.


 



References:


See http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLSecurityOverviewforAdmins.doc for detailed prescriptive guidance on configuring SQL Server service accounts.


 


q  Grant SQL Server service account “lock pages in memory” privilege (Local Security Policy).


 



 


q  Enable TCP/IP network protocol.


 



NOTE: This protocol may be automatically enabled when SQL Server is installed, depending on the edition.


How:


http://msdn2.microsoft.com/en-us/library/ms191294.aspx


 


q  Disable Hyperthreading


 



How:


This must be performed in the bios settings of the server.


Why:


Not all SQL Server workloads benefit from hyperthreading.  Dynamics AX ERP has not been extensively tested with hyperthreading, so the recommendation is to disable it.


References:


http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx


 


SQL Instance – Configuration


 


Max Degree of Parallelism (MAXDOP instance-wide)


q  OLTP (normal operations0: Set Max Degree of Parallelism to 1


 


q  UPGRADE (when upgrading to a new release of Ax): Set Max Degree of Parallelism to lesser of {8, # of physical processor cores, # of physical processor cores per NUMA node}.


 



How:


Use sp_configure system stored procedure where n is the desired value:


EXEC sp_configure ‘max degree of parallelism’, 1;


RECONFIGURE;


GO


EXEC sp_configure;


Examine the output from the second sp_configure execution and confirm that the new value is in the run_value column for the max degree of parallelism setting.


Why:


Dynamics AX OLTP workloads generally perform better when intraquery parallelism is disabled, but the upgrade process benefits from parallelism.


References:


http://msdn2.microsoft.com/en-us/library/ms181007.aspx


http://support.microsoft.com/kb/329204


http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx


 


q  Memory: Adjust MAX Server Memory (MB) according to available RAM (32/64 bit server).


 


NOTE: The following recommendations for configuring SQL Server:max server memory do not apply to Windows Server 2008, only to earlier versions of the OS (Windows Server 2003).  Windows 2008 includes improvements to memory management that make overriding the default setting for maximum SQL Server memory unnecessary (assuming the host is a dedicated to running a single SQL Server instance); see http://support.microsoft.com/kb/918483.







































































Server RAM (MB)


SQLServer:max server memory (MB)


/3GB *


/PAE*


AWE*


2,000


1,600


No


No


No


4,000


3,000


Yes


No


No


6,000


4,500


Yes


Yes


Yes


8,000


6,500


Yes


Yes


Yes


12,000


10,000


No


Yes


Yes


16,000


14,000


No


Yes


Yes


24,000


22,000


No


Yes


Yes


32,000


30,000


No


Yes


Yes


>32,000 <= 128,000


(available RAM) – 2,000 to 4,000


No


Yes


Yes


>128,000


4,000


 No


 Yes


 Yes

             *Denotes 32-bit only setting


 


Set Maximum Memory



How:


Use sp_configure to set the value of ‘max server memory (MB)’ as follows:


EXEC sp_configure ‘max server memory (MB)’, 10240;


RECONFIGURE WITH OVERRIDE;


GO


EXEC sp_configure;


This example sets the max server memory to 10,240MB (10GB).  Replace the value 10240 with the desired value.  Examine the output from the second sp_configure execution and confirm that the new value is in the run_value column.


Why:


Under the default setting, which is to use all available memory, SQL Server may leave no more than 128MB physical memory free, and may not release memory fast enough if needed by the Operating System.  These settings generally assure that ample memory will be available. See note above – max server memory default setting can be used if SQL Server is running on Windows Server 2008 OS.


References:


http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx


http://support.microsoft.com/kb/918483


 


Set AWE



NOTE: Only configure on 32-bit systems as specified.


How:


Use sp_configure to set the value of ‘awe enabled’ as follows:


Examine the output from the second sp_configure execution and confirm that the new value is in the run_value column.  If the ‘lock pages in memory privilege’ has not been set for the service account, an error is raised if you attempt to enable AWE (see section above).


Why:


If AWE is not enabled, SQL Server will be unable to address more than 2GB or 3GB of memory (depending on /3GB switch), irrespective of how much physical RAM is available.  When AWE is enabled, the additional memory over 2GB or 3GB is available to SQL Server for data cache buffers.  32-bit only, not applicable to native 64-bit SQL Server 2005 instances.


References:


http://msdn2.microsoft.com/en-us/library/ms190673.aspx


http://technet.microsoft.com/en-us/library/ms191481.aspx


http://technet.microsoft.com/en-us/library/ms187499.aspx


 


Set /PAE and /3GB switches in boot.ini



NOTE: Only configure on 32-bit systems as specified


How:


These options are set in the boot.ini file for the appropriate OS selection.  If hot-swappable memory is supported by the hardware, the functionality enabled by the /PAE switch is automatically enabled.


Why:


/3GB is required to address memory >2GB on 32-bit systems, and /PAE is required to address memory >4GB on 32-bit systems.  Enabling /3GB limits memory available to the OS to a maximum of 16GB, even if more physical RAM is installed..


References (see AWE section above)


 


q  Monitor available memory to ensure that SQL Memory setting is correct.


 



How:


Use the sysmon performance counter Memory:Available Mbytes to determine if available memory drops below 500MB, especially for sustained periods.  You may need to reduce SQL Server Max Memory if this happens regularly or for extended durations.


Why:


On a server with at least 4GB running a dedicated SQL Server workload, when available memory goes below 500MB, especially for sustained periods, the server may experience performance problems.  It is out of scope of this document to provide guidance applicable in every such situation, but if available memory exhibits this pattern, further investigation and remediation may be necessary.


 


_____________
David Reinhold


Microsoft Dynamics Ax Performance & Scalability Engineer – Database

Comments (14)

  1. stefan.lundquist@systeam.se says:

    Hi, good article !

    What is the newest supported “Cumulative update package for SQL Server 2005 Service Pack 2” for a 64 bit SQL server for Dynamics Ax 4.0 Sp2 ?

  2. davidre says:

    All post-SP2 Cumulative Update (CU) packages for SQL Server 2005 are fully supported for Dynamics AX 4.0 (all SPs).  The minimum patch level for SQL Server runing AX 4.0 is SP2 (9.00.3042).

    We do not re-certify the AX application for each SQL Server CU, we only do this for Service Packs.  However, customers can install a post-SP2 CU if they are affected by any of the bug fixes it includes, and they will be fully supported.  

    The “Incremental Servicing Model” for SQL Server, under which the CUs are released, is defined here: http://support.microsoft.com/kb/935897/LN/

  3. leh0311 says:

    Hi David,

    This is a very good article and really helps me a lot.

    WIth regards to SQL Server collation, is it safe to assume that the default SQL Collation (Latin1_General_CI_AS) when installing SQL Server 2005 is supported by AX 4.0?

    Cheers!

    Leh

  4. davidre says:

    Yes, Leh, the default SQL collation is fully supported.

    David

  5. fkap67 says:

    Hi David,

    good article, but I have some more questions:

    Is Read-Committed Snapshot Isolation = true also recommended for AX 3.0 ?

    Is it useful/allowed to split the AX database files (Files + Filegroups)and put them on different disks?

    Thanks in advance

    Fritz

  6. davidre says:

    Fritz –

    Yes, RCSI can be enabled in AX3.

    We don’t recommend splitting the AX database into multiple filegroups, and then trying to balance filegroups over different sets of disks.  By default, all AX database tables & indexes are created in the Primary filegroup; you will generally get the most benefit from available storage by striping the .mdf file over as many physical disks (“spindles”) as you can, which spreads all the IO.  We don’t recommend isolating particular tables into their own filegroups, or splitting non-clustered indexes from clustered indexes into separate filegroups, either.

    There may be exceptions in some limited cases, but this would require performance analysis to identify potential tables which MIGHT benefit from filegroup isolation, then testing to confirm that there is a performance benefit.  In the performance team, we don’t do that type of testing, there are more effective ways to improve performance at lower cost.

    – David

  7. Sodina says:

    Hi David,

    This is a very good article.

    I have a question about Max DOP (Degree of Pararelism).

    My company will upgrade Ax 3.0 – 4.0 and our database server have 2 physical processor (as logically we have 8)

    The question is should i set the MAX DOP into 2 or 8 or may be 0 (i read article about Max DOP, 0 value is for all available server) in order to optimize the process.

    Cheers,

    David Santoso

    Technical Consultant

  8. minime says:

    Hi David,

    Before changing the Max degree of Parallelism in our production server, the average CPU utilization is about 20-30%. However, after changing the value to 1, the average CPU utilization increased to 40-50%! Any idea?

    We are using AX4.0 sp2 and sql2005 enterprise X64 with 16GB and dual Quad core Xeon 2.5Ghz CPU.

    Thanks !

    MiniMe

  9. davidre says:

    Hi, MiniMe.

    When Max Degree of Parallelism (MAXDOP) is changed, SQL Server evicts all of the procedure cache for the instance.  This means that all queries will need to be re-compiled into the procedure cache.  This can result in a spike of higher CPU activity, but it should subside after a relatively short time (depending on how active the system was when it was reconfigured).  If you continue to see a sustained period of higher-than-normal CPU activity while the system is under load, say 2-3 hours at the most, and it doesn’t go back to normal levels, you may want to change back to the original configuration to make sure this was the cause.  If this is a live production system, you should make this change during a period of light load if possible.

    This change does take effect "hot" and does not require the instance to be recycled.

    David Reinhold

  10. Tom Frisk says:

    Hi

    I have read this Blog and thinks that I found the right people to communicate with about a Speed issue that we have at a costumerside. How can I post infos so that I can be helped to solve the problems ?

    Kindly regards

    Tom Frisk

    MPS Graphics GmbH

  11. GuillaumeD says:

    Hi David,

    What do you think about SQL Server table partionning winth Dynamics AX 4 or 5 ? Do you think it is possible to use this functionality ? Is there any problem with the AX synchronization process ?

    Greets,

    Guillaume

  12. GuillaumeD says:

    Hi David,

    What do you think about SQL Server table partionning winth Dynamics AX 4 or 5 ? Do you think it is possible to use this functionality ? Is there any problem with the AX synchronization process ?

    Greets,

    Guillaume

  13. Microsoft Dynamics CRM training in Hyderabad says:

    Microsoft Dynamics CRM training will help you manage and prioritize your business goals, customize.we teach MS Dynamics CRM training and class available at Hyderabad.

    http://www.arrowsoltraining.com/microsoft-dynamics-crm

  14. Rob says:

    David, do all these changes still apply for SQL Server 2012 ?