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:
http://blogs.technet.com/perfguru/default.aspx
http://support.microsoft.com/kb/895980
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).
How:
See http://technet.microsoft.com/en-us/library/ms190730.aspx for detailed instructions. This privilege is not automatically granted to local administrators.
References
http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx
http://support.microsoft.com/kb/918483
http://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:
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.
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:
http://blogs.technet.com/perfguru/default.aspx
http://support.microsoft.com/kb/895980
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).
How:
See http://technet.microsoft.com/en-us/library/ms190730.aspx for detailed instructions. This privilege is not automatically granted to local administrators.
References
http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx
http://support.microsoft.com/kb/918483
http://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:
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.