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