emeadaxsupport: AX Performance - Analyzing key SQL Server configuration and database settings

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...-settings.aspx
==============

In the previous post in this series, I provided some T-SQL scripts that can check key settings and highlight possible issues you need to address. This post aims to provide some feedback and suggestions on the information the scripts collect. There are of course different views on almost all SQL Server configuration settings, but the comments I will make are intended to be as in line as possible with other public documentation and Microsoft policies and best practices. I will approach this from a Microsoft Dynamics AX perspective, first and foremost, though general SQL Server configuration guidance still applies to instances running an AX workload.

MSDN Blogs > Microsoft Dynamics AX Support > AX Performance - Checking key SQL Server configuration and database settings
http://blogs.msdn.com/b/axsupport/archive/2015/09/22/ax-performance-checking-key-sql-server-configuration-and-database-settings.aspx

The scripts I provided look at the SQL Server instance and the AX databases deployed there. Let's start by looking at the SQL Server instance you are using to run your AX databases on. For background information on this area, please see the official Microsoft Dynamics AX documentation, e.g.:

SQL Server topology recommendations for availability and performance [AX 2012]
https://technet.microsoft.com/en-us/library/dd362068.aspx

ConfigureSQL Server and storage settings [AX 2012]
https://technet.microsoft.com/en-us/library/dd309734.aspx

I also recommend that you take a look at the 'SQL Optimization for Microsoft Dynamics AX 2012' training courses (80428 and 80696) if you have access to the Microsoft Dynamics Learning Portal.

Microsoft Dynamics Learning Portal | AX | Course Catalog
https://mbspartner.microsoft.com/AX/CourseResources/

1. AX Instance Config

*** I ran the script on a machine hosting a recent AX 2012 R3 build on SQL Server 2014 ***



What does the script output tell us here? Well, it shows us what kind of SQL Server instance we are running, at what build and edition, and how it has been configured when it comes to TempDB data files per core. We can also see if Lock Pages in Memory (LPiM) has been enabled, if priority boost is on or off, and if 'max server memory (MB)' has been set to a static value or not. Finally, we can see if MAXDOP is set to 1 or some other value, and for good measure, we can see a snapshot of a few SQL Server performance counters like Page life expectancy (PLE) and total and target server memory in KB. We would also see any I/O warning messages and paging errors found in the SQL Server ERRORLOG (i.e. '...IO requests taking longer than 15 seconds to complete...' and 'A significant part of sql server process memory has been paged out...')

I would usually recommend the following, which again, should be in line with other public documentation and Microsoft policies and best practices:

SQL Server Builds

Generally speaking, I would recommend that you keep your SQL Server instance updated and in a supported state.

You'll need to refer to the Microsoft Dynamics AX System Requirements (which are updated regularly)

Microsoft Dynamics AX 2012 System Requirements
http://www.microsoft.com/en-gb/download/details.aspx?id=11094
You should also regularly check the Dynamics AX In-Market Engineering blog for compatibility statements to ensure that you are in a supported state.

TechNet Blogs » Dynamics AX In-Market Engineering » All Tags » compatibility
http://blogs.technet.com/b/dynamicsaxse/archive/tags/compatibility/

It's usually a good idea to run your PROD SQL Server instance on a recent (high) Service Pack (SP) and Cumulative Update (CU)

TempDB Data Files

As you can see, I have 4 TempDB data files on this 4 core SQL Server instance. I'd go with 1 per core up to 8 cores.

MSDN Blogs > Dynamics Ax Performance Team Blog > Ax Database Configuration Checklist Part 2 -- TempDB --http://blogs.msdn.com/b/axperf/archive/2008/03/13/ax-database-configuration-checklist-part-2.aspx"Determine total size of data and transaction log required for tempdb to avoid autogrow, and number of data files required based on # of processors (logical or physical)."

I'd set my TempDB data files to grow and the same rate in MB, not %

Lock Pages in Memory (LPiM) and ‘max server memory (MB)’

Generally speaking, I'd recommend enabling LPiM on x64 systems these days, but there are scenarios where LPiM is not appropriate. If you enable it, ensure that you also set 'max server memory (MB)' to a reasonable static value. If you are on an older version of SQL Server STANDARD EDITION, you may need to implement trace flag 845 to be able to enable LPiM.

As with any other major change to a critical production system, you should test this before you make the change in production and be ready to remove it if you run into problems

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
http://support.microsoft.com/kb/918483

How to enable the "locked pages" feature in SQL Server 2012
http://support.microsoft.com/kb/2659143

Once you have set the Lock Pages In Memory privilege on SQL Server 2012 you should see this sort of messages in the ERRORLOG on startup:

2015-09-20 12:13:14.250 Server Using locked pages in the memory manager

Do not enable LPiM without setting ‘max server memory (MB)’

If you are running a clustered instance, it is important to verify that LPiM works ok regardless of which node is active.

If you are running more than one instance on the same cluster nodes, ensure that your LPiM and ‘max server memory (MB)’ settings are suitable for your failover scenarios.

Trace flags

Based on experience, I would usually go with trace flags 1117, 1118, and 4199, but you may also need to look at 845, 1224 and 2371 depending on scenario.

Trace flag 1117
http://blogs.msdn.com/b/axperf/archive/2011/09/12/consider-enabling-trace-flag-1117-on-dynamics-ax-sql-server.aspx

Trace flag 1118
http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

Trace flag 4199
http://blogs.msdn.com/b/axinthefield/archive/2010/11/04/sql-server-trace-flags-for-dynamics-ax.aspx

Trace flag 2371
http://blogs.msdn.com/b/axinthefield/archive/2013/10/07/sql-server-trace-flag-2371-for-dynamics-ax.aspx

One of my colleagues in the AX PFE Team has also blogged about this area recently:

MSDN Blogs > Dynamics AX in the Field > Dynamics AX and SQL Server Trace Flags, Quick and Dirty
http://blogs.msdn.com/b/axinthefield/archive/2014/05/08/dynamics-ax-and-sql-server-trace-flags-quick-and-dirty.aspx

Priority Boost

We see this set to 1 on very rare occasions. It should be set to 0.

Configure the priority boost Server Configuration Option
https://msdn.microsoft.com/en-us/library/ms188709.aspx

"Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server."

Max degree of parallelism (MAXDOP)

As a SQL Server DBA you may have some views on MAXDOP, but from an AX perspective we recommend that you set MAXDOP to 1.

MSDN Blogs > Dynamics Ax Performance Team Blog > Welcome -- Ax Database Configuration Checklist part 1 -- MAXDOP --
http://blogs.msdn.com/b/axperf/archive/2008/03/10/welcome-database-configuration-checklist-part-1.aspx

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

Configure SQL Server and storage settings [AX 2012]
http://technet.microsoft.com/en-us/library/dd309734.aspx

“Microsoft Dynamics AX workloads generally perform better when intra-query parallelism is disabled. However, the upgrade process benefits from parallelism, as do activities that are used exclusively for batch jobs or maintenance.”

MSDN Blogs > Dynamics AX in the Field > Database Maintenance Strategies for Dynamics AX -- MAXDOP --
http://blogs.msdn.com/b/axinthefield/archive/2012/08/01/database-maintenance-strategies-for-dynamics-ax.aspx

“In Dynamics AX databases, it is recommended to run with a max_degree_of_parallelism (MAXDOP) option of 1. However, a REBUILD benefits highly from parallelism. Developer and Enterprise Editions support temporarily altering the MAXDOP setting as part of the ALTER command. You can change this by specifying the WITH(MAXDOP = n) option where n is the number of cores in your server up to a maximum of 8.”
Perfmon Counters

This is a snapshot so perhaps of limited value, but if it shows very low PLE, for example, you should look into memory consumption in more detail.

MSDN Blogs > MCS UK Data Platform and Business Intelligence Team > SQL Server Page Life Expectancy
http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx

Now that we've looked at the SQL instance level, let's focus on the AX database level.

2. AX Database Config

*** I ran the script on a machine hosting a recent AX 2012 R3 build on SQL Server 2014 ***



The output from the script shows us how the AX databases and the TempDB have been configured. We can see what compatibility level they are running at, and what their collations are set to. We can see the recovery model, and establish if RCSI has been enabled or not.

Ensure that your databases are using the same compatibility level and collation, and that you've chosen a suitable recovery model, e.g. FULL if in PROD. Also ensure that RCSI has been enabled OK.



Parameter Sniffing Mitigation Functionality

The last set of queries show you how your AX environment is configured with regards to passing in DATAAREAID and PARTITION (R2 and R3 only) as LITERALS, and in order to establish if that might be a good idea, we check to see if you have one or more PARTITION and if you have many AX companies (DATAAREAIDs).

I would usually recommend that you ENABLE this functionality (i.e. VALUE = 1), which has certain pre-requisites when it comes to AX kernel builds etc., as you can see here:

MSDN Blogs > Dynamics AX in the Field > SQL Server Parameter Sniffing with Dynamics AX, just plain evil
http://blogs.msdn.com/b/axinthefield/archive/2014/01/09/sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil.aspx

On an AX 2012 R2 or AX 2012 R3 environment you would run this sort of UPDATE command to enable the functionality:

UPDATE SYSGLOBALCONFIGURATION
SET [VALUE] = 1
WHERE NAME IN ('DATAAREAIDLITERAL','PARTITIONLITERAL')

Note that you'll need to restart your AOS for the new settings to take effect.

3. Other settings that can impact performance which the scripts don't cover

As mentioned in the original post, there are other factors that can impact performance that the scripts I published don't look at for a variety of reasons.

BIOS

Ensure that your BIOS settings provide the maximum performance your hardware is capable of delivering (this will vary by manufacturer)

MSDN Blogs > Microsoft Dynamics AX Support > Dynamics AX - Power options
http://blogs.msdn.com/b/axsupport/archive/2014/01/10/dynamics-ax-power-options.aspx

OS Settings

Ensure that you have set your servers to use a high performance power plan and they are optimized for background services.

Power Options = High performance
http://blogs.msdn.com/b/cindygross/archive/2011/03/09/power-saving-options-on-sql-server.aspx

Processor Scheduling = Background Serviceshttp://technet.microsoft.com/en-us/magazine/ff458358.aspx

Maintenance Strategy

Ensure that you have a valid database maintenance strategy.

MSDN Blogs > Dynamics AX in the Field > Database Maintenance Strategies for Dynamics AX
http://blogs.msdn.com/b/axinthefield/archive/2012/08/01/database-maintenance-strategies-for-dynamics-ax.aspx

Builds

Last but not least, ensure that you are patching your Dynamics AX environment and running recent builds of both the AX kernel and AX application to get the full benefits of the code fixes that are released (use LCS to find them!)

MSDN Blogs > Dynamics AX in the Field > Patching Strategy for Dynamics AX
http://blogs.msdn.com/b/axinthefield/archive/2014/08/22/patching-strategy-for-dynamics-ax.aspx

MSDN Blogs > Microsoft Dynamics AX Support > Overview of Microsoft Dynamics AX build numbers
http://blogs.msdn.com/b/axsupport/archive/2012/03/29/overview-of-ax-build-numbers.aspx

Issue search (Lifecycle Services, LCS) [AX 2012]
https://technet.microsoft.com/en-us/library/dn268610.aspx




Источник: http://blogs.msdn.com/b/axsupport/ar...-settings.aspx