Download as PDF, PPTX
Uploaded byJuan Fabian
Optimization SQL Server for Dynamics AX 2012 R3
This document provides guidance on sizing and configuring SQL Server, the Application Object Server (AOS), Enterprise Portal, and other components for a Microsoft Dynamics AX implementation. It includes recommendations for hardware sizing based on transaction volumes and user counts. It also describes best practices for SQL Server configuration settings, indexing, statistics maintenance, and other tasks to ensure optimal performance of the Dynamics AX database and system.
Optimization SQL Server for Dynamics AX 2012 R3
- 3. Value of LifecycleServices for Key Personas
- 4. Microsoft Dynamics AXArchitectural Review
- 5. Microsoft Dynamics AXArchitectural Review • Three-Tier Architecture Data • Microsoft Dynamics AX database, the model store, the Reporting Services database, the Analysis Services database, and the Enterprise Portal database. Application • Application Object Server (AOS), Enterprise Portal, Reporting and Analytics, Workflow, Services and Application Integration Framework, and Help Server Presentation • Microsoft Dynamics AX Rich Client and Enterprise Portal
- 6. Microsoft Dynamics AXArchitectural Review • Reporting Architecture Menu item Parameters form Report viewer Report (.rdl file) Data extension Customizatio n extension Metadata service Query web service Query 1 2 3 45
- 7. Microsoft Dynamics AXArchitectural Review •Enterprise Portal Architecture
- 8. Sizing Questions • Whatis the Maximum Transaction Volume expected in an Hour, during Peak Season? • What is the Record Count for Master Data? • Which modules will be used in the Microsoft Dynamics AX application? • Will any Add-on to Microsoft Dynamics AX be used? • Will Microsoft Dynamics AX have to integrate to third-party systems? • Are there any major customizations that must be created? • How many users will access the Microsoft Dynamics AX system?
- 9. Database Sizing Guidelines •CPU – The database server should have one core for every 4,000 to 12,000 transactions entered for every hour, with a minimum of four cores. For example, an organization that enters 48,000 transactions for each hour during peak hours should have 4 to 12 cores. • Memory – For every core on the database server, 2 to 4 gigabytes (GB) of memory should be allocated. For example, a database server that has 8 cores should have 16 to 32 GB of memory. • Storage – Data, transaction logs, and tempdb should be stored in a raid 0+1 array. The data drive must be large enough to hold the production database, and have the disk throughput to handle Microsoft Dynamics AX transaction volumes.
- 10. Application Object ServerSizing Guidelines • CPU – Each AOS should have one core for every 8,000 to 12,000 transactions entered for every hour. Additionally, one core should exist for every 25 to 100 concurrent users who access the Microsoft Dynamics AX 2012 system. • Memory – 4 GB to 8 GB of memory should be allocated to each AOS instance. • Batch Server – AOS servers designated to run batch jobs should have one to four threads allocated for each CPU core.
- 11. Enterprise Portal ServerSizing Guidelines • CPU – The Enterprise Portal web server should have 2 to 16 cores, depending on the user count and the transaction volume/complexity. Generally, one core for every 40 to 120 concurrent users should be added. • Memory – 1/2 GB to 2 GB of memory should be allocated for every core. • Network – Enterprise Portal web servers should be equipped with Gigabit Ethernet Network Interface Cards and exist on a Gigabit network.
- 12. Terminal Server SizingGuidelines • CPU – A minimum of two cores should be allocated for the base Terminal Server. • Memory – A minimum of 4 GB of memory should be installed in the base Terminal Server. In addition, 100 Megabytes (MB) to 200 MB of memory should be allocated for every client connection. • Network – If latency on the Local Area Network is over 50 milliseconds or access to the Microsoft Dynamics AX Rich Client is required over a Wide Area Network, Remote Desktop Services should be used.
- 13. SQL Configuration • Scenario: Simon,the Systems Implementer, is helping Contoso to configure its SQL Server to make sure that the SQL Server continues to run without interruptions on AX. • Configure the Operating System BIOS Disable Hyper-Threading and power saving features. • Operating System Settings Select a high performance power plan, and set processor scheduling prioritized for background services.
- 14. SQL Configuration • SQLServer Service Account Configuration • Lock pages in memory • Perform Volume Maintenance Tasks
- 15. SQL Configuration • Configurationof the Instance of the SQL Server • Max Degree of Parallelism • Maximum server memory • Minimum server memory • Default index fill factor
- 16. SQL Configuration • TraceFlags 1224 - Disables lock escalation based on the number of locks. 4136 - Can significantly improve the performance of the Microsoft Dynamics AX implementation if the structure of the Microsoft Dynamics AX data has parameter sniffing issues. 4199 - Activates query optimizer fixes from several hotfixes. 1117 - Keep tempdb data files the same size in case tempdb must autogrow. 2371 - Changes the threshold to automatically update the statistics for the indexes on a table from 20 percent to a formula that is based on both the percent change and the number of rows in the table.
- 17. SQL Configuration • Configuretempdb • Move tempdb, log files, and data files to separate high performance disks • Set the tempdb to 20 percent of the size of the database • Create one tempdb data file for each processor core • Configure Autogrow to between 250 and 500 MB for each file
- 18. SQL Configuration • Configurethe AX Database Property Recommended setting READ_COMMITTED_SNAPSHOT ON AUTO_CREATE_STATISTICS ON AUTO_UPDATE_STATISTICS ON AUTO_UPDATE_STATISTICS_ASYNC OFF AUTO_SHRINK OFF AUTO_CLOSE OFF • Physical Storage Configuration ALTER DATABASE <ax database name> SET READ_COMMITTED_SNAPSHOT ON GO
- 19. Microsoft Dynamics AXSettings • Microsoft Dynamics AX Server Configuration Enable breakpoints to debug X++ code running on this server Enable clients to trace their interactions with the AOS instance. Enable global breakpoints Enable the debugging X++ code that is invoked by using Query Services
- 20. Microsoft Dynamics AXSettings • Microsoft Dynamics AX Server Configuration
- 21. Microsoft Dynamics AXSettings • Microsoft Dynamics AX Server Configuration Statement cache Determines how many statements the AOS caches. Maximum buffer size (KB) The maximum size of the buffer the kernel allocates for holding input/output data to and from the SQL server.
- 22. Microsoft Dynamics AXSettings • Microsoft Dynamics AX Server Configuration
- 23. Microsoft Dynamics AXSettings • Server Configuration • Error on invalid field access • Error on exceeding maximum number of tables in join • Maximum number of tables in join • Client record cache factor • Entire table cache size • Global Object Cache elements • Record cache elements for <Table group> table group
- 24. Microsoft Dynamics AXSettings • AOS Load Balancing • Data Cleanup • Client Performance Options • Additional areas affecting performance • Database log • Number sequences • Credit limit check • Complex Company structure • In Virtual company setup and views
- 25. Batch Configuration • BatchConfiguration • Maximum batch threads • Batch server groups
- 26. Design Effective SQLServer Maintanence Plans Maintenance plans Index fragmentation Statistics Maintenance Frequency