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.
References:
https://blogs.technet.com/perfguru/default.aspx
https://support.microsoft.com/kb/895980
SQL Instance – Service Config
q Run SQL Server service under a Domain Account with minimum necessary privileges.
References:
See https://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).
How:
See https://technet.microsoft.com/en-us/library/ms190730.aspx for detailed instructions. This privilege is not automatically granted to local administrators.
References
https://support.microsoft.com/kb/918483
https://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx
q Enable TCP/IP network protocol.
NOTE: This protocol may be automatically enabled when SQL Server is installed, depending on the edition.
How:
https://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:
https://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:
https://msdn2.microsoft.com/en-us/library/ms181007.aspx
https://support.microsoft.com/kb/329204
https://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 https://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:
https://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:
https://msdn2.microsoft.com/en-us/library/ms190673.aspx
https://technet.microsoft.com/en-us/library/ms191481.aspx
https://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
Anonymous
May 06, 2008
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 ?Anonymous
May 07, 2008
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/Anonymous
May 28, 2008
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! LehAnonymous
May 29, 2008
Yes, Leh, the default SQL collation is fully supported.DavidAnonymous
June 23, 2008
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 advanceFritzAnonymous
June 25, 2008
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
- Anonymous
July 30, 2008
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 SantosoTechnical Consultant - Anonymous
March 04, 2009
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 - Anonymous
March 05, 2009
The comment has been removed - Anonymous
June 07, 2010
HiI 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 regardsTom FriskMPS Graphics GmbH - Anonymous
August 24, 2010
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 - Anonymous
August 24, 2010
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 - Anonymous
June 28, 2012
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.www.arrowsoltraining.com/microsoft-dynamics-crm - Anonymous
September 11, 2014
David, do all these changes still apply for SQL Server 2012 ?