Dynamics AX Performance Management May 12 2016 Introduction

Introduction Aaron Porter – Associate Partner – 10 years of Dynamics AX consulting experience

Goal for this Session ► Share knowledge on how to improve the performance of

Introduction ► How do you know if you are having performance issues? – Users

High Level Approach ► ► ► Configuration Best Practices Index Overview Index Optimization Index

Configuration ► SQL Max Degree of Parallelism describes how many CPU Cores can be

Configuration ► SQL Max Server Memory Controls how much memory the SQL Server is

Configuration ► SQL Auto Growth Configuration This configuration controls how the database will behave

Index Overview ► An index is a copy of select columns of data from

Index Overview ► Indexes generally fall into two categories: – Clustered § Index that

Index Optimization ► Determine what Indexes need to be created to optimize performance. –

Index Optimization ► Missing clustered indexes – Missing clustered indexes can cause undue stress

Index Maintenance ► Index Fragmentation – 10 -30% fragmentation the index should be reorganized

Data Maintenance ► Regularly Evaluate Table Sizes – Identifies missing clean up jobs. –

Recap ► ► ► Configuration Best Practices Index Overview Index Optimization Index Maintenance Data

Conclusion ► Questions? 15 2016 Quantum Dynamix, LLC. All rights reserved. .

Slides: 16

Download presentation

Dynamics AX Performance Management May 12, 2016

Introduction Aaron Porter – Associate Partner – 10 years of Dynamics AX consulting experience Eric Neyer – Dynamics AX Technical Architect – 14 years of professional software development – Certifications – – – Dynamics AX 2009 Development Dynamics AX 2012 Installation and Configuration SQL Server 2012/2014 Administration Developing Share. Point 2013 Core Solutions 1 2016 Quantum Dynamix, LLC. All rights reserved.

Goal for this Session ► Share knowledge on how to improve the performance of Dynamics AX. – Share common performance pitfalls that we have encountered with AX users. – Advanced performance management solutions exist, but we will focus on using standard AX and SQL tools that all of you already have at your disposal. 2 2016 Quantum Dynamix, LLC. All rights reserved.

Introduction ► How do you know if you are having performance issues? – Users complain about slowness – You experience slowness first hand – System runs well generally but slows at specific times of the day or during specific business processes ► In our experience almost all AX environments can achieve better performance by doing performance tuning. 3 2016 Quantum Dynamix, LLC. All rights reserved.

Configuration ► SQL Max Server Memory Controls how much memory the SQL Server is allowed to use. – Quantum Recommended Setting – Allow for at least 4 GB for the Windows Server and system services. 6 2016 Quantum Dynamix, LLC. All rights reserved. .

Configuration ► SQL Auto Growth Configuration This configuration controls how the database will behave once it reaches its maximum size. – Microsoft Recommended Setting – 100 MB to 500 MB 7 2016 Quantum Dynamix, LLC. All rights reserved. .

Index Overview ► Indexes generally fall into two categories: – Clustered § Index that defines how the table is physically stored. § Only one per table § Contains all columns – Non-Clustered § All other indexes § Not limited to one per table § Contains only indexed fields. AX 2012 allows for the addition of included (unindexed) columns. 9 2016 Quantum Dynamix, LLC. All rights reserved. .

Index Optimization ► Determine what Indexes need to be created to optimize performance. – – Create an index based on a commonly used query. Use Dynamic Management Views for suggested indexes. Index optimization can be easily overlooked in custom code. Identify long running queries using the client SQL trace. – Using with development – All indexes affect performance, but as a general rule, the benefit almost always outweighs the cost. 10 2016 Quantum Dynamix, LLC. All rights reserved. .

Index Optimization ► Missing clustered indexes – Missing clustered indexes can cause undue stress on the system. – As a general rule, all tables should have clustered indexes. 11 2016 Quantum Dynamix, LLC. All rights reserved. .

Index Maintenance ► Index Fragmentation – 10 -30% fragmentation the index should be reorganized (Defragmented). – Indexes with greater than 30% fragmentation should be rebuilt. – Fill factor of 80% 12 2016 Quantum Dynamix, LLC. All rights reserved. .