emeadaxsupport: AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration]

[Blog bot]

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

Introduction

This check list is primarily aimed at troubleshooting general performance issues in Microsoft Dynamics AX 2009 and 2012. 'General' here typically means a set of unidentified issues across one or more modules, or indeed the entire application. However it is good practice to have at least a quick check of set up and settings when working with any performance issue. It is important to always have a good foundation to diagnose issues from.

It is based on issues encountered on support and best practice documentation. As always, you should be sure that you fully understand the impact of any changes you make and implement them in your test environment first.

Part 1 covers SQL and AX configuration and is intended to give you the foundation. This in turn is split into 2 parts:

• SQL Server and Storage Settings (this page).
• AX application and AOS configuration (page 2).

Part 2 (following on later) will cover hardware, indexing, queries, blocking and code (at a high level). This naturally involves deeper analysis, which will be iterative and is probably where the vast majority of the time will be spent - but it requires a solid foundation (i.e. part 1).

It is suggested that you analyse/implement changes in three phases - Part 1, Part 2 (except code), code review, however each phase may overlap. You should also plan to spread out your deployments (of any remediation activities for this) as much as possible, for easier diagnosis and reversibility in case of any problems.

Within each section below, the links under 'Recommendation' relate to further details, while the links under 'How to...' relate to implementing the changes. You should ensure you have the information relevant to your software versions.

With each part, there will be a link to an analysis script which you can use to help gather the information from your system. These scripts require the Performance Analyser to be installed:

http://blogs.msdn.com/b/axinthefield...ent-guide.aspx

References:

• SQL Optimization for Microsoft Dynamics AX 2012: Course Number 80428 (training materials), e.g.: https://mbs.microsoft.com/customersource/northamerica/AX/learning/student-training-materials/course80428
• Microsoft Dynamics AX 2012 Installation Guide: http://www.microsoft.com/en-us/downl....aspx?id=12687
• Microsoft Dynamics AX 2009 White Paper: Planning Database Configuration: http://www.microsoft.com/en-us/downl....aspx?id=13647
• Dynamics AX Performance team Blog: http://blogs.msdn.com/b/axperf/
• Dynamics AX 2012 System Requirements: http://www.microsoft.com/en-gb/downl....aspx?id=11094
• Dynamics AX 2009 System Requirements: http://www.microsoft.com/en-us/downl....aspx?id=26568
• Inside Microsoft Dynamics AX (books/e-books, various AX versions): Microsoft Press
• Technet & MSDN (links below).

Part 1A: SQL Server and storage settings

Analysis script: http://blogs.msdn.com/b/axsupport/archive/2014/09/01/microsoft-dynamics-ax-general-performance-analysis-scripts.aspx

Infrastructure / assumptions:

-You are following the documented best practices for Windows Server and SQL Server.-You are using a dedicated server that is appropriately sized according to the workload and meets the relevant system requirements (above).-You are using a single instance of SQL Server that is dedicated to running the Microsoft Dynamics AX production databases.-We recommend that you store your test and development databases on a separate server from the production databases.-SQL Server settings: Affinity Masks and Priority boost are left as default (automatic and off respectively).Virtualisation

Recommendation:-While there are clear benefits from using virtualisation, it has been known to degrade performance under high load scenarios; if the virtualisation is not optimally configured then obviously the severity can increase. See "Virtual server support" in the system requirements. See also: Hyper-V Benchmark for Microsoft Dynamics AX 2012 How to...-Refer to your hardware/infrastructure vendor for advice.

OS

Recommendation:-Verify that SQL Server is configured to run as a background service in Windows.-Set the power plan to 'high performance' (all AX servers): http://blogs.msdn.com/b/axsupport/ar...r-options.aspx-[AX 2009 only] In Windows Server 2003, set the Specify memory usage option to Programs.-[AX 2009 only] If you are using Windows Server 2003 with AMD processors, ensure that boot.ini contains the parameter /USEPMTIMER How to...Configure Application Performance on Windows Server 2008 R2:
http://technet.microsoft.com/en-us/m.../ff458358.aspxSQL Server Instance

Recommendation:-Set max degree of parallelism to 1 (for normal Production operations)-Max Server Memory: make sure that sufficient memory is available for the operation of Windows Server. Use the Memory: Available Mbytes performance counter for the Windows Server operating system to determine whether the available memory drops below 500 MB for extended periods-AWE enabled on large memory 32bit systems [AX 2009]-Check for large TokenAndPermUserStore (> a few hundred MB)http://community.dynamics.com/ax/b/a...userstore.aspx How to...Max Degree of Parallelism Option:
http://msdn.microsoft.com/en-us/library/ms181007.aspx Windows Performance Monitor:
http://technet.microsoft.com/en-us/l.../cc749249.aspx Memory Architecture [AWE]:
http://msdn.microsoft.com/en-us/library/ms187499.aspxSQL Server Service

Recommendation:-Confirm that the account for the SQL Server service has been granted the Lock pages in memory privilege.-Configure the account for the SQL Server service for instant file initialization.-Enable only the required network protocols - AX only requires TCP/IP.-Disable hyper-threading. How to...Enable the Lock Pages in Memory Option (Windows):
http://technet.microsoft.com/en-us/l.../ms190730.aspx

Database Instant File Initialization:
http://technet.microsoft.com/en-us/library/ms175935.aspx

Enable or Disable a Server Network Protocol:
http://msdn.microsoft.com/en-us/library/ms191294.aspx

Disabling of Hyper-threading must be performed in the BIOS settings of the server. For instructions, see the hardware documentation for your server.

TempDB storage

Recommendation:-Set a specific value (MB, not %) for autogrowth (safety mechanism, i.e. "emergency release valve" only)-1 tempdb file per processor.-Isolate tempdb on dedicated storage (highest speed possible)-Determine the size of the tempdb data files and log files How to... Optimizing tempdb Performance:
http://msdn.microsoft.com/en-us/library/ms175527.aspx ALTER DATABASE File and Filegroup Options (Transact-SQL):
http://technet.microsoft.com/en-us/library/bb522469(v=sql.120).aspx Move System Databases:
http://msdn.microsoft.com/en-us/library/ms345408.aspxAX Database Configuration:

Recommendation:-Set COMPATIBILITY_LEVEL to 110 for SQL Server 2012, or to 100 for SQL Server 2008 or SQL Server 2008 R2-Set READ_COMMITTED_SNAPSHOT to on-Set AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to on. Set AUTO_UPDATE_STATISTICS_ASYNC to off.-Make sure that the AUTO_SHRINK option is set to off-All Microsoft Dynamics AX databases must use the same SQL collation. How to... ALTER DATABASE Compatibility Level (Transact-SQL):
http://msdn.microsoft.com/en-us/library/bb510680.aspx ALTER DATABASE SET Options (Transact-SQL):
http://technet.microsoft.com/en-us/library/bb522682(v=sql.120).aspx Turn AUTO_SHRINK off:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx Server Configuration - Collation:
http://msdn.microsoft.com/en-us/library/cc281995.aspxConfiguring physical storage

Recommendation:-Disk sector alignment: partition offset value must be a multiple of the stripe size (i.e. partition offset / stripe size resolves to an integer). File allocation unit size: bytes per cluster should usually be 64KB.-Create the tempdb database files, data files for the Microsoft Dynamics AX database, and Microsoft Dynamics AX log files on disk arrays of type RAID 1, RAID 0 + 1, or RAID 10 (RAID 10 recommended).-Store the data files for the Microsoft Dynamics AX database on separate physical stores from transaction log files.-Store the tempdb data files on a separate physical store from the data files and log files for the Microsoft Dynamics AX database.-Store other database files on separate physical stores from the data files and log files for tempdb and the Microsoft Dynamics AX database.-Virtual log files for each database log file. VLF_Count > 10k requires attention. How to... Seek guidance from your SAN vendor or if they do not provide specific recommendations, refer to:
Disk Partition Alignment Best Practices for SQL Server
http://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspxSeek guidance from your SAN vendor regarding RAID configuration. Move User Databases:
http://msdn.microsoft.com/en-us/library/ms345483.aspx Move System Databases:
http://msdn.microsoft.com/en-us/library/ms345408.aspx SQL Server Transaction Log Architecture and Management (VLF_Count):
http://technet.microsoft.com/en-us/l...=sql.110).aspxTrace flags

Recommendation:-Generally for SQL Server the recommendation is only to implement trace flags to address the specific issues they are designed to address, however for AX the following are known to have a beneficial impact on performance and should therefore be considered:4199; 1117; 1118; 1224; 2371;7646*-Establish the reasons behind any other trace flags being enabled.*7646 does not apply to SQL Server 2012 and above.See: http://blogs.msdn.com/b/axinthefield/archive/2014/05/08/dynamics-ax-and-sql-server-trace-flags-quick-and-dirty.aspxHow to... Verify which trace flags are turned on:
DBCC TRACESTATUS (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187809.aspx If any of the mentioned trace flags are not turned on (see left), consider adding them as start-up parameters:
Configure Server Startup Options (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms345416.aspxParameter sniffing fix (dataareaid & partition literals)

Recommendation:-Ensure you have implemented this important fix for general performance in AX. How to...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.aspxSQL Server build

Recommendation:-Most recent supported Service Pack together with a recent cumulative update. How to... First check the system requirements (above link) for the latest supported service pack. To obtain the above service pack, search for "How to obtain the latest service pack for [your SQL Server version]", e.g. "How to obtain the latest service pack for SQL Server 2012":
http://support.microsoft.com/kb/2755533 followed by:
"The [SQL Server version] builds that were released after [SQL Server version] [above Service Pack] was released", e.g.: "The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released:"
http://support.microsoft.com/kb/2772858Triggers

Recommendation:-In part 1 the action is just to list non standard triggers - investigate these in more detail in part 2. How to...SQL script:
USE DYNAMICSPERF
SELECT * FROM TRIGGER_TABLESQL Server Agent jobs

Recommendation:-Is there a database backup job (among other reasons, transaction logs need regular backups to keep the sizes small)-Ensure that you have an appropriate database maintenance strategy.-Are there jobs that could stress the server? In part 1 the suggested action is just to list them, then investigate these in more detail in part 2. How to...Plan backup and recovery [AX 2012]:
http://technet.microsoft.com/en-us/library/dd361990.aspx

Planning backup and disaster recovery[AX 2009]:
http://technet.microsoft.com/en-us/library/dd309580(v=ax.50).aspx Database Maintenance Strategies for Dynamics AX:
http://blogs.msdn.com/b/axinthefield/archive/2012/08/01/database-maintenance-strategies-for-dynamics-ax.aspxData archiving/purging

Recommendation:-Keeping the amount of data in your database to a minimum can help with performance.-Other than those mentioned in the blog (see right), also consider the following key tables:

• Logs and batch tables under Administration/Inquiries [AX 2009] or System Administration/Inquiries [AX 2012]. For SQL statement trace log (SysTraceTableSQL) there is also a SQL Server Agent purge job in the Performance Analyser tool.
• InventSumLogTTS table: run MRP with full regeneration periodically (or turn off Master Planning configuration key)].

How to...Database Maintenance Strategies for Dynamics AX:
http://blogs.msdn.com/b/axinthefield...namics-ax.aspx

How to accurately predict database growth in Dynamics AX:
http://blogs.msdn.com/b/axinthefield/archive/2011/06/12/how-to-accurately-predict-database-growth-in-dynamics-ax.aspx










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