Dynamics AX Performance Optimization Guide

By Martin Zhen & Daniel Liao

PREFACE

This book is a practical guide for database administrators and Dynamics AX technical consultants who implement, maintain, or develop Dynamics AX. It outlines guidelines for improving the performance of Dynamics AX running on Microsoft SQL Server. Much of the information presented in this book is based on findings from real-world customer deployments. This book and its content are provided as is without warranty of any kind, and should not be interpreted as an offer or commitment. We cannot guarantee the accuracy of any information presented. We make no warranties, express or implied, in this book. Also, the descriptions are intended as brief highlights to aid understanding, rather than as thorough coverage.

WHICH VERSION OF DYNAMICS AX IS COVERED IN THIS BOOK?

All the examples in this book are based on Dynamics AX 2012. In fact, most of what is covered in this book also applies to Dynamics AX 2009. While you may notice some minor changes between the two versions, and discover that Dynamics AX 2012 includes a few new features, every example in this book will work with Dynamics AX 2009 and everything you learn here is applicable to both Dynamics AX 2012 and Dynamics AX 2009.

AVAILABLE SUPPORT

We make every effort to ensure that there are no errors in the text. However, no one is perfect, and mistakes do occur. If you find an error in our book, such as a spelling mistake or a faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save other readers’ hours of frustration, and at the same time, you will be helping us provide even higher quality information. To find the errata page for this book, go to http://www.outsourceax.com/AXPerfBook and click the Errata link. If you have any comments regarding the book, please contact us via emails to Martin.Zhen@outsourceax.com or Daniel@outsourceax.com .

ABOUT THE AUTHORS

Martin Zhen started as a Dynamics AX developer in 2004. He currently works as a principle technical consultant at OutsourceAX Development (outsourceax.com), specializing in problem resolution support, proactive consulting assistance for Dynamics AX environments. He has worked on Dynamics AX environments for leading corporations in various business domains, helping them to identify and rectify SQL Server-related issues for Dynamics AX applications. Daniel Liao runs OutsourceAX Development, a Chinese offshore development company in Shanghai, China. He started as a developer on Microsoft Dynamics AX in 2004 primarily working on a Chinese localization project with Dynamics AX 3.0 for Microsoft. He has worked on many projects based on AX versions 2.5, 3.0, 4.0, 2009, and 2012.

PREFACE ......................................................................................................................................... 2

AVAILABLE SUPPORT ...................................................................................................................... 3

ABOUT THE AUTHORS ..................................................................................................................... 4

1 UNDERSTANDING DYNAMICS AX ........................................................................................... 9

1.1 DYNAMICS AX ARCHITECTURE ................................................................................................. 9 1.1.1 Dynamics AX and its Components ........................................................................... 9

1.2 HOW DYNAMICS AX MANAGE SESSIONS.................................................................................. 12 1.2.1 Server/Client Session ............................................................................................. 12

1.3 CONCURRENCY CONTROL WITHIN DYNAMICS AX ....................................................................... 17 1.3.1 Pessimistic Concurrency Control ............................................................................ 17 1.3.2 Optimistic Concurrency Control ............................................................................. 17 1.3.3 How To Set Up Concurrency Model Globally .......................................................... 17 1.3.4 Implementing Optimistic Concurrency Control ...................................................... 18

2 MONITORING HARDWARE, DATABASE, AND DYNAMICS AX ................................................. 23

2.1 HARDWARE MONITORING .................................................................................................... 23 2.1.1 Using System Monitor ........................................................................................... 23 2.1.2 Microsoft Dynamics AX Performance Counters...................................................... 28 2.1.3 WCF Performance Counters with Dynamics AX 2012 AOS ...................................... 31

2.2 DATABASE MONITORING ...................................................................................................... 33 2.2.1 Using SQL Server Profiler ....................................................................................... 33 2.2.2 Using Dynamic Management Views ...................................................................... 44 2.2.3 Finding Current Users and Processes ..................................................................... 49 2.2.4 Decoding the Object Blocking a Process ................................................................ 49 2.2.5 Selected DBCC Commands ..................................................................................... 50

2.3 DATABASE I/O ................................................................................................................... 51 2.3.1 SQLIO – Disk Performance Test Tool ...................................................................... 51 2.3.2 SQLIOSim – Disk Stress Test Tool ........................................................................... 51 2.3.3 Instant File Initialization ........................................................................................ 51 2.3.4 Long I/O Requests ................................................................................................. 52

2.4 MONITORING DYNAMICS AX ................................................................................................. 53 2.4.1 Dynamics AX Trace Parser ..................................................................................... 53 2.4.2 Windows Event Tracing ......................................................................................... 60 2.4.3 WCF Service Trace Viewer Tool .............................................................................. 65 2.4.4 Microsoft Visual Studio Profiling Tools .................................................................. 68 2.4.5 PerformanceTester - WebServer Performance Tester ............................................ 70 2.4.6 Performance Testing for Enterprise Portal (Web Performance Test) ...................... 71 2.4.7 Performance Analyzer for Microsoft Dynamics AX (DynamicsPerf) ........................ 75 2.4.8 Process Monitor .................................................................................................... 77

3 SETUP AND CONFIGURATION .............................................................................................. 78

3.1 INPUT/OUTPUT (I/O) CONFIGURATION ................................................................................... 78 3.1.1 Redundant Array of Independent Disks (RAID) Configuration ................................ 78 3.1.2 Typical I/O Performance Recommended Range ..................................................... 79

3.2 FILES, AND FILEGROUPS ........................................................................................................ 80 3.3 TEMPDB PLACEMENT AND TUNING ......................................................................................... 80

3.4 DATA AND LOG FILE SIZING................................................................................................... 82 3.5 RECOVERY MODELS ............................................................................................................ 82

3.5.1 Simple Recovery Model......................................................................................... 82 3.5.2 Full Recovery Model ............................................................................................. 83 3.5.3 Bulk-Logged Recovery Model ................................................................................ 83

3.6 DATABASE OPTIONS ............................................................................................................ 83 3.6.1 Read Committed Snapshot Isolation (RCSI) ........................................................... 84 3.6.2 Asynchronous Statistics Update ............................................................................ 85 3.6.3 Parameterization .................................................................................................. 86 3.6.4 Auto Update Statistics .......................................................................................... 87 3.6.5 Auto Create Statistics ........................................................................................... 87

3.7 ANTIVIRUS SOFTWARE ......................................................................................................... 88 3.8 SQL SERVER CONFIGURATIONS ............................................................................................. 88

3.8.1 Installation Considerations ................................................................................... 88 3.8.2 Hyper-Threading ................................................................................................... 89 3.8.3 Memory Tuning .................................................................................................... 89 3.8.4 Important SP_Configure Parameters .................................................................... 92 3.8.5 Network Protocols and Pagefile............................................................................ 94 3.8.6 Advanced Performance Option ............................................................................. 95

3.9 NETWORK CAPACITY ........................................................................................................... 98 3.9.1 TCP Chimney ......................................................................................................... 98

3.10 DYNAMICS AX SETUP ..................................................................................................... 98 3.10.1 Statement Compilation .................................................................................... 98 3.10.2 Cache of Number Sequences ............................................................................ 99 3.10.3 Configuring HTTP Compression in IIS ................................................................ 99 3.10.4 Use AppFabric for NLB topology ......................................................................102

3.11 OFFLOAD REPORTING (SSRS) ..........................................................................................103

4 COMMON DYNAMICS AX PERFORMANCE PROBLEM ..........................................................104

4.1 LARGE NUMBER OF ROUNDTRIPS TO THE DATABASE ..................................................................104 4.1.1 Increase buffer size ..............................................................................................104 4.1.2 Set Based Operations ...........................................................................................104 4.1.3 AOS Authorization ...............................................................................................105

4.2 HIGH PROCESSOR UTILIZATION .............................................................................................105 4.3 DISK I/O BOTTLENECKS ......................................................................................................107 4.4 MEMORY BOTTLENECKS ......................................................................................................108 4.5 HIGH MEMORY USAGE WHEN RUN IN IL(BATCH MODE) ..............................................................109 4.6 BLOCKING AND DEADLOCKING ISSUES ....................................................................................109 4.7 EXTENSIVE LOGGING ...........................................................................................................109

4.7.1 Application Integration Framework .....................................................................110 4.7.2 Workflow Notifications ........................................................................................110

4.8 SQL SERVER WAIT TYPES ....................................................................................................111 4.8.1 CXPACKET ............................................................................................................112 4.8.2 PAGEIOLATCH_EX ................................................................................................112 4.8.3 ASYNC_NETWORK_IO ..........................................................................................113

4.9 PARAMETER SNIFFING ........................................................................................................113 4.9.1 Dealing with Bad Parameter Sniffing ...................................................................114 4.9.2 Turn Off Parameter Sniffing .................................................................................116

5 OPTIMIZING DYNAMICS AX SQL STATEMENT ......................................................................118

5.1 QUERYING SQL SERVER...................................................................................................... 119 5.2 DYNAMICS AX DEVELOPMENT BEST PRACTICE FOR PERFORMANCE .............................................. 120

5.2.1 Use Set Based Operations.................................................................................... 120 5.2.2 Use the SysGlobalCache /SysGlobalObjectCache Class ........................................ 120 5.2.3 Avoid Using Pessimisticlock in X++ ...................................................................... 120 5.2.4 Select wisely ........................................................................................................ 120 5.2.5 Use table caching ................................................................................................ 121 5.2.6 Use Display/Edit Method Caching Where Appropriate ........................................ 121

5.3 SQL PRE-PROCESSING IN MICROSOFT DYNAMICS AX ................................................................ 121 5.4 STATEMENT CACHE & MAXIMUM BUFFER SIZE ........................................................................ 123

5.4.1 Statement Cache (Open cursors) ......................................................................... 123 5.4.2 Maximum Buffer Size .......................................................................................... 124 5.4.3 Default Maximum Number of Rows Returned ..................................................... 125 5.4.4 Statement with ForceLiterals Keyword ................................................................ 127

5.5 USING HINTS ................................................................................................................... 128 5.5.1 Index Hints .......................................................................................................... 128 5.5.2 Plan Guides ......................................................................................................... 129 5.5.3 ODBC API Server Cursor Performance Enhancements .......................................... 129

5.6 TRACE FLAGS .................................................................................................................... 132 5.6.1 Controlling Trace Flags ........................................................................................ 132 5.6.2 Implementing Trace Flags ................................................................................... 132 5.6.3 Monitoring Trace Flags........................................................................................ 135 5.6.4 Trace Flags Recommended for use with Dynamics AX ......................................... 135

6 SQL SERVER PERFORMANCE AND COMPLIANCE OPTIMIZATIONS FOR DYNAMICS AX ......... 138

6.1 RESOURCE MANAGEMENT .................................................................................................. 138 6.1.1 Resource Governor .............................................................................................. 138

6.2 BACKUP AND STORAGE OPTIMIZATION .................................................................................. 139 6.2.1 Backup Compression ........................................................................................... 139 6.2.2 Data Compression ............................................................................................... 140

6.3 AUDITING AND COMPLIANCE ............................................................................................... 144 6.3.1 Transparent Data Encryption (TDE) ..................................................................... 144 6.3.2 SQL Server Audit .................................................................................................. 146

6.4 PERFORMANCE MONITORING AND DATA COLLECTION ............................................................... 148 6.4.1 Data Collector and Management Data Warehouse ............................................. 148 6.4.2 Memory Monitoring DMVs.................................................................................. 150 6.4.3 Extended Events .................................................................................................. 151

6.5 QUERY PERFORMANCE OPTIMIZATION ................................................................................... 153 6.5.1 Plan Freezing ....................................................................................................... 153 6.5.2 Lock Escalation .................................................................................................... 154

6.6 HARDWARE OPTIMIZATIONS ................................................................................................ 154 6.6.1 Hot Add CPU ........................................................................................................ 154 6.6.2 NUMA ................................................................................................................. 155

7 DATABASE MAINTENANCE ................................................................................................. 156

7.1 MANAGING INDEXES .......................................................................................................... 156 7.1.1 Index-Related Dynamic Management Views ....................................................... 156

7.2 DETECTING FRAGMENTATION............................................................................................... 159 7.3 REDUCING FRAGMENTATION ............................................................................................... 160 7.4 STATISTICS ....................................................................................................................... 162

7.4.1 AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS ...............................162 7.4.2 Disabling AUTO_UPDATE_STATISTICS at the Table Level .....................................163 7.4.3 User-Created Statistics.........................................................................................164 7.4.4 Updating Statistics...............................................................................................164 7.4.5 Viewing Statistics ................................................................................................165

7.5 CONTROLLING LOCKING BEHAVIOR ........................................................................................166 7.5.1 Isolation Levels ....................................................................................................166 7.5.2 Lock Granularity ..................................................................................................167 7.5.3 Lock Escalations ...................................................................................................168 7.5.4 Lock Escalation Trace Flags ..................................................................................169 7.5.5 Deadlocks ............................................................................................................170

7.6 DEDICATED ADMINISTRATOR CONNECTION (DAC)....................................................................174

8 INFRASTRUCTURE & HYPER-V VIRTUALIZATION ..................................................................175

8.1 HARDWARE SIZING ............................................................................................................175 8.1.1 Database Server ..................................................................................................175 8.1.2 Application Server hosting AOS ...........................................................................176 8.1.3 Web Server hosting Enterprise Portal ..................................................................177 8.1.4 Batch Servers .......................................................................................................177 8.1.5 Remote Desktop Session Host Server (RDSH Server) ............................................178 8.1.6 Firewall ................................................................................................................178

8.2 SIZING METHODOLOGY .......................................................................................................179 8.2.1 Work Load ...........................................................................................................179 8.2.2 Sizing Guidance for Each Components (AX 2012) .................................................181 8.2.3 Sizing Guidance for Each Components (AX 2009) .................................................184

8.3 DYNAMICS AX SERVER VIRTUALIZATION .................................................................................186 8.3.1 Virtualization Benefits .........................................................................................186 8.3.2 Hyper-V Virtualization .........................................................................................186 8.3.3 Hardware Considerations ....................................................................................187

8.4 HYPER-V BEST PRACTICES ...................................................................................................188 8.4.1 DO NOT virtualize SQL Server...............................................................................188 8.4.2 Increase Network Capacity ..................................................................................189 8.4.3 Implement Hyper-V Integration Services .............................................................189 8.4.4 Maximize Memory Usage ....................................................................................190 8.4.5 Minimize Operating System Overhead.................................................................190 8.4.6 Optimize SCSI and Disk Performance ...................................................................190

APPENDIX ...................................................................................................................................191

BIBLIOGRAPHY ............................................................................................................................194

INDEX .........................................................................................................................................195

Appendix

Analysis Tools for Troubleshooting Performance

Tools Description Used for

SQL Server Profile Trace

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine.

SQL Server

Performance Monitor

Use Performance Monitor to monitor the utilization of system resources. Collect and view real-time performance data in the form of counters, for server resources such as processor and memory use, and for many Microsoft SQL Server resources such as locks and transactions.

SQL Server AOS

SQLdiag utility The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers.

SQL Server

Performance Analyzer for Microsoft Dynamics

Performance Analyzer for Microsoft Dynamics is a set of scripts to collect and analyze performance information from the database and application tiers of Microsoft Dynamics products.

SQL Server

Process Monitor

Process Monitor is an advanced monitoring tool for Windows that shows real-time file system, Registry and process/thread activity. It combines the features of two legacy Sysinternals utilities, Filemon and Regmon, and adds an extensive list of enhancements including rich and non-destructive filtering, comprehensive event properties such session IDs and user names, reliable process information, full thread stacks with integrated symbol support for each operation, simultaneous logging to a file, and much more.

EP AOS

Network Monitor

Network Monitor is a protocol analyzer that allows the capture of network traffic and the ability to view and analyze it.

SQL Server EP AOS

Trace Parser Trace Parser consolidates information from multiple sources, such as remote procedure calls (RPCs) and Microsoft SQL Server, to provide an integrated view of application performance at run time.

X++ CIL

Visual Studio Profiling Tools

The Visual Studio Profiling Tools let developers measure, evaluate, and target performance-related issues in their code. These tools are fully integrated into the IDE to provide a seamless and approachable user experience.

CIL

Cursor Types in Dynamics AX

Version Cursor Repeatable Read

Support

RCSI Suppor

t

3.0 SP6 FFO for read-only Dynamic for pessimistic locking

No No

4.0 SP1, 4.0 SP2 FFO for read-only FFO for optimistic locking Dynamic for pessimistic locking

Yes Yes

2009, 2009 SP1 FFO for read-only FFO for optimistic locking Dynamic for pessimistic locking

Yes Yes

2009 SP1 HF (Build# 5.0.1500.3775)

FFO for read-only FFO for optimistic locking FFO for pessimistic locking

Yes Yes

2012 R1, 2012 R2 FFO for read-only FFO for optimistic locking FFO for pessimistic locking Dynamic for full-text search

Yes Yes

Bibliography

Literature

The Microsoft Dynamics AX Team, Inside Microsoft Dynamics AX 2012, Microsoft Press (2012)

Lars Dragheim Olsen/Michael Fruergaard Pontoppidan/Hans Jorgen Skovgaard/Tom Kaminski/Deepak Kumar/Satish Thomas, Inside Microsoft Dynamics AX 2009, Microsoft Press (2009)

Luis X. B. Mourão/ David Weiner, Dynamics AX: A Guide to Microsoft Axapta, Apress (2006)

Other Sources

Microsoft Dynamics AX Developer Center

(http://msdn.microsoft.com/en-us/dynamics/ax/)

Microsoft TechNet Library – Dynamics AX

(http://technet.microsoft.com/en-us/library/dd362025.aspx)

AX Support Blog

(http://blogs.msdn.com/b/axsupport/)

Dynamics AX in the Field Blog

(http://blogs.msdn.com/b/axinthefield)

MFP's Two Cents Blog

(http://blogs.msdn.com/b/mfp)

Brandon George's Microsoft Dynamics AX Blog

(http://dynamics-ax.blogspot.com)

Palle Agermarks Microsoft Dynamics AX blog

(http://palleagermark.blogspot.com.au)

Dilip's blog on DYNAMICS AX

(http://daxdilip.blogspot.com)

Index

-internal=nocursorreuse 117 Index 36,74,155 -internal=comments 117 Index hint 127 Application Integration Framework (AIF) 10,109 Instant File Initialization 50,51 AOS Authorization 105 Keyset-driven cursor 129 Application Object Server (AOS) 8,10,28,30, 119 Latency 78,154,177 Asynchronous Statistics Update 84 Lightweight pooling 91 Auto Create Statistics 86,87 Lock Escalation 153,167,168 Auto Update Statistics 86 Locking 83,124,125 Ax32serv.exe.config file 108 Max degree of parallelism 91,92,111 Address Windowing Extensions (AWE) 88,89 Maximum buffer size 123,124,125,127 Backup Compression 138,139 Named Pipes 94 Batch Server 176,177 Network Interface Controller (NIC) 186,188 Buffer size 103,112,122 Non-Uniform Memory Access (NUMA) 154 Bulk-Logged Recovery 82 ODBC API Server Cursor 128,129,130 Concurrency Control 16,17 OnlyFetchActive 119 CPU 23,43,44 OptimisticLock 17 Cursor 39 Optimistic concurrency control 16,17 CXPACKET 111 PAGEIOLATCH_EX 111 Data Compression 139,140 Parallelism 92,105 Database 30,50,82,103 Parameterization 85 Database trip 103,104 Performance Analyzer for Microsoft Dynamics 75 DBCC CACHESTATS 49 Performance Monitor 28,79 DBCC DBREINDEX 160 Page Compression 140 DBCC TRACEOFF 49,131,133 PerformanceTester 70 DBCC TRACEON 49,133,134 PessimisticLock 18,120,125 DBCC TRACESTATUS 49,131,134 Pessimistic locking 125,126,127 DBCC SQLPERF 46 Pessimistic concurrency control 17 Deadlock 33,83,135 Physical Address Extension (/PAE) 89 Dedicated Administrator Connection (DAC) 173 Plan freezing 153 Display/Edit Method Caching 120 Plan guide 129 DMV 110,147,149 Process Monitor 77 Dual Core 79,80 Processor Affinity 81 Dynamic Cursor 128,129 Quad Core 183,184 Enterprise Portal 9,11,29,70,98,100,176,182,184 Read Committed Snapshot Isolation (RCSI) 84 Event Tracing for Windows (ETW) 52,150 RDSH Server 178 Exclusive Lock 17,165 Remote Desktop Session Host Server 178 Execution Plan 36,45,84,92,152,161,163,172 Resource Governor 138 Fast Forward Cursor 32,121,129,135 Row Compression 141 FFO 128,191 Sales Order transaction lines per hour (SOTPH) 179 Fibre Channel 186 SCSI 190 FileGroups 79,160 Sessions 12,14 FIRSTFAST 118,127 Set Based Operations 104,105,120 Firewall 177 Shared Memory 95 ForceLiterals 113,114,126 ShowPlan XML 34 Full Recovery 82 Simple Recovery 82 High Processor Utilization 91,103,104 Sizing guidance 181,184 Hot Add CPU 153 Sizing Methodology 179 Hyper-V 174,178,185,186 SQL Server Audit 145,146 Hyper-Threading 88,176 SQL Server Profiler 32,36,37 I/O requests 50,51,79 SQL Server Reporting Service (SSRS) 102,181,183 I/O consumer 44 Statement cache 122,126 Implicit Cursor Conversion 129 SysClientSessions 11,12,13

SysGlobalCache 120 Trace Parser 52,53 SysGlobalObjectCache 120 Transparent Data Encryption (TDE) 143 SysServerSessions 10,11,12 Transaction log 24,50,79 Table caching 121 Two Node Configuration 186 TCP Chimney 98 Update conflict exception 17,20 TCP/IP 95 UpdLock 125

TempDB 81,84 Virtual hard disks (VHDs) 188 Thread 177,178,179 Virtual machines (VMs) 185 Throughput 107,187 Virtualization 185,186,187 Trace flag 131 Visual Studio Profiling Tools 67,68 1204 135,169,170 WCF Performance Counters 30,31 1211 153,168 WCF Service Trace Viewer 64 1224 135,153,168 Windows Performance Monitor 31,55,59 1244 135 4136 135 4199 131,134,135