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