emeadaxsupport: AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration]
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:
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:
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.
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):
Database Instant File Initialization:
Enable or Disable a Server Network Protocol:
Disabling of Hyper-threading must be performed in the BIOS settings of the server. For instructions, see the hardware documentation for your server.
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):
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:"
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:
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]:
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:
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:
How to accurately predict database growth in Dynamics AX:
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
|4199, полезное, производительность|
|emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 8||Blog bot||DAX Blogs||0||01.09.2014 14:11|
|emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 7||Blog bot||DAX Blogs||0||01.09.2014 14:11|
|emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 6||Blog bot||DAX Blogs||0||01.09.2014 14:11|
|emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 5||Blog bot||DAX Blogs||0||01.09.2014 14:11|
|atinkerersnotebook: Walkthrough & Tutorial Summary||Blog bot||DAX Blogs||1||09.09.2013 09:11|
|Опции темы||Поиск в этой теме|