Dynamics AX Performance Optimization Guide

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 realworld 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 [email protected] or [email protected] .

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 SQL Server Profile Trace

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

Used for 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

Process Monitor

Network Monitor

Trace Parser

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. 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. Network Monitor is a protocol analyzer that allows the capture of network traffic and the ability to view and analyze it. 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.

SQL Server

EP AOS

SQL Server EP AOS

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 3.0 SP6 4.0 SP1, 4.0 SP2 2009, 2009 SP1 2009 SP1 HF (Build# 5.0.1500.3775) 2012 R1, 2012 R2

Cursor FFO for read-only Dynamic for pessimistic locking FFO for read-only FFO for optimistic locking Dynamic for pessimistic locking FFO for read-only FFO for optimistic locking Dynamic for pessimistic locking FFO for read-only FFO for optimistic locking FFO for pessimistic locking FFO for read-only FFO for optimistic locking FFO for pessimistic locking Dynamic for full-text search

Repeatable Read Support No

RCSI Suppor t No

Yes

Yes

Yes

Yes

Yes

Yes

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 -internal=comments 117 Application Integration Framework (AIF) 10,109 AOS Authorization 105 Application Object Server (AOS) 8,10,28,30, 119 Asynchronous Statistics Update 84 Auto Create Statistics 86,87 Auto Update Statistics 86 Ax32serv.exe.config file 108 Address Windowing Extensions (AWE) 88,89 Backup Compression 138,139 Batch Server 176,177 Buffer size 103,112,122 Bulk-Logged Recovery 82 Concurrency Control 16,17 CPU 23,43,44 Cursor 39 CXPACKET 111 Data Compression 139,140 Database 30,50,82,103 Database trip 103,104 DBCC CACHESTATS 49 DBCC DBREINDEX 160 DBCC TRACEOFF 49,131,133 DBCC TRACEON 49,133,134 DBCC TRACESTATUS 49,131,134 DBCC SQLPERF 46 Deadlock 33,83,135 Dedicated Administrator Connection (DAC) 173 Display/Edit Method Caching 120 DMV 110,147,149 Dual Core 79,80 Dynamic Cursor 128,129 Enterprise Portal 9,11,29,70,98,100,176,182,184 Event Tracing for Windows (ETW) 52,150 Exclusive Lock 17,165 Execution Plan 36,45,84,92,152,161,163,172 Fast Forward Cursor 32,121,129,135 FFO 128,191 Fibre Channel 186 FileGroups 79,160 FIRSTFAST 118,127 Firewall 177 ForceLiterals 113,114,126 Full Recovery 82 High Processor Utilization 91,103,104 Hot Add CPU 153 Hyper-V 174,178,185,186 Hyper-Threading 88,176 I/O requests 50,51,79 I/O consumer 44 Implicit Cursor Conversion 129

Index 36,74,155 Index hint 127 Instant File Initialization 50,51 Keyset-driven cursor 129 Latency 78,154,177 Lightweight pooling 91 Lock Escalation 153,167,168 Locking 83,124,125 Max degree of parallelism 91,92,111 Maximum buffer size 123,124,125,127 Named Pipes 94 Network Interface Controller (NIC) 186,188 Non-Uniform Memory Access (NUMA) 154 ODBC API Server Cursor 128,129,130 OnlyFetchActive 119 OptimisticLock 17 Optimistic concurrency control 16,17 PAGEIOLATCH_EX 111 Parallelism 92,105 Parameterization 85 Performance Analyzer for Microsoft Dynamics 75 Performance Monitor 28,79 Page Compression 140 PerformanceTester 70 PessimisticLock 18,120,125 Pessimistic locking 125,126,127 Pessimistic concurrency control 17 Physical Address Extension (/PAE) 89 Plan freezing 153 Plan guide 129 Process Monitor 77 Processor Affinity 81 Quad Core 183,184 Read Committed Snapshot Isolation (RCSI) 84 RDSH Server 178 Remote Desktop Session Host Server 178 Resource Governor 138 Row Compression 141 Sales Order transaction lines per hour (SOTPH) 179 SCSI 190 Sessions 12,14 Set Based Operations 104,105,120 Shared Memory 95 ShowPlan XML 34 Simple Recovery 82 Sizing guidance 181,184 Sizing Methodology 179 SQL Server Audit 145,146 SQL Server Profiler 32,36,37 SQL Server Reporting Service (SSRS) 102,181,183 Statement cache 122,126 SysClientSessions 11,12,13

SysGlobalCache SysGlobalObjectCache SysServerSessions Table caching TCP Chimney TCP/IP TempDB Thread Throughput Trace flag 1204 1211 1224 1244 4136 4199

120 120 10,11,12 121 98 95 81,84 177,178,179 107,187 131 135,169,170 153,168 135,153,168 135 135 131,134,135

Trace Parser Transparent Data Encryption (TDE) Transaction log Two Node Configuration Update conflict exception UpdLock Virtual hard disks (VHDs) Virtual machines (VMs) Virtualization Visual Studio Profiling Tools WCF Performance Counters WCF Service Trace Viewer Windows Performance Monitor

52,53 143 24,50,79 186 17,20 125 188 185 185,186,187 67,68 30,31 64 31,55,59