Transcript Performance that Exceeds Expectations

Dynamics AX: Performance that
Exceeds Expectations
Anna Kupchenko| Celenia Software
Introduction
• Celenia
– For more info
• www.celenia.com
• Anna Kupchenko, PMP
– Division Manager, Senior Solution Architect at Celenia Software,
[email protected]
Agenda
• Introduction
• Addressing performance
– Problem identification
– Troubleshooting: conceptual approach
– Recommended tools
• Planning for performance
• Implementation examples Case Studies:
– Issues
– Troubleshooting techniques
– Results
• Summary
Problem Identification
How do you know that performance is poor?
• Slow response time
• Batch jobs long in duration
• Users are not able to complete their daily tasks
• Small documents take too long to process
• Response time is slow for a particular user / machine / operation
• AX is ‘slow’ compared to the old system
Problem Identification
When did the problem start?
• From the very beginning of system go-live
• When trying to process lots of data
• After N+ users started simultaneously working in AX
• After installing update xxxxx.yyy.
Problem Identification
What are the performance expectations?
• Forms response time should be under 5 seconds
• Invoicing should be completed during the weekend
• Warehouse receipt should not take longer than…
Performance Troubleshooting
Don’t panic!
– do not replace AX with another ERP system
– do not roll back to the prior system
– do not fire your developer
• Start to analyze, localize and break down the problem
• Remove bottleneck in the infrastructure or the code
• Fine tune, fine tune, fine tune 
At least 50% of performance cases have a simple &
quick solution!
Performance Troubleshooting
•
•
•
•
Infrastructure, network
SQL Server
Dynamics AX, AOS
Advanced troubleshooting
Infrastructure
Solutions for the majority of users and operations:
• Check SQL Server and AOS machine status
• RAM usage, CPU load, IO Operations, disk space
Tools:
• Task Manager, Network Monitor, Visual Studio Profiling Tools
• SQL Activity monitor: http://msdn.microsoft.com/enus/library/ms175518.aspx
• Performance monitor: http://technet.microsoft.com/enus/library/cc749115.aspx
Infrastructure
•
Check your installation topology
– Is the line stable, bandwidth and latency normal between:
• AOS and SQL Server?
• SQL Server and data storage?
• Client and AOS?
•
•
•
Are any of the servers shared with other heavily loaded systems?
Check event logs for any suspicious events, especially recurring ones
Verify software versions, updates, anti-viruses etc. are correct
Tools – SQL Server layer
• DynamicsPerf (Performance analyzer for
Microsoft Dynamics)
– we love it!
• SQL Server Profile Trace
• SQLdiag utility
AX: Finding bottlenecks
If you have problems with a particular task,
form or report
• Do a Code review: static analysis
• Analyze call stack: dynamic analysis
• Look for Non-code related reasons
• Complete advanced fine tuning
Code review
Best practice issues
Redundant, unnecessary or wrongly placed database calls,
Select fields list
Nested loops instead of joins, using outer join instead of inner-,
inner- join instead of exists• Missing or redundant index
• Create / update / delete records one by one instead of bulk
operations
• Aggregate functions misused
•
•
•
•
Code review
• Clustered index not used
• Selecting records that are not used (use firstonly, firstfast)
• Check if special select keywords are used
(forcePlaceholders | forceLiterals]
| forceselectorder |forceNestedLoop | repeatableRead | index hint)
• Update without updates
• Assigning fields with new values that are not changed
• Updating % of records selected for update
• Table inheritance used on transactional data
Code review
•
•
•
•
•
•
•
•
Wrong field justification
A heavy operation inside loop that can be moved outside
User input or external calls inside a transaction
Wrongly placed code
DB operations on client side
– a lot of client-server roundtrips
Form is overloaded with code
– can be moved to table or class level or skipped
Redundant recursive calls and even endless loops
On-disk temporary tables instead of in-memory
Code review
• Slow data types and formats used
– Excel vs CSV
– Containers vs sets
• Not optimized number sequences
– Continuous NS
– Generate ID yourself if possible
• Potential deadlocks: update and delete the same table at the same
time
• Numerous selects of the same structure instead of views with
calculated columns
Tools – AX layer
• New and hot! Performance Benchmark SDK for
Dynamics AX: http://technet.microsoft.com/ENUS/library/dn527257.aspx
• Trace Parser:
– Is an optional component on an AX install
• Code Profiler:
– Available inside MorphX
Traceparser tool. SQL queries
Traceparser tool. Top 5
Traceparser tool. Callstack
Non-code related Solutions
•
•
•
•
•
•
Configuration keys turned “on” that are not used
“Hanging” user sessions
Slow external components / calls
Slow workflows
Redundant security checks
XDS, RLS
Advanced Tuning
SQL administration
Check SQL Server or database setup
Check SQL queries execution plans – no full scans at least
Check Database recovery model
SQL table partitioning
Split huge tables by key prefix and move to different hard drives for
parallel processing
• Move tables that are frequently jointed to different hard drives
• DB statistics update
•
•
•
•
•
•
Advanced Tuning
Large data sets
• Use batch parallelism
http://blogs.msdn.com/b/axperf/archive/2012/02/24/batch-parallelism-in-ax-part-i.aspx
• Pre-process data for more of an even load
• Consolidate data
Advanced Tuning
AOS and client setup
• Clustering
• AOS configuration
– Caching
– Buffer size
Advanced Tuning
AOS and client
setup
• Client configuration
EP troubleshooting
Enterprise Portal performance
• Process Monitor
• Network Monitor
• ‘Performance Tester’ tool
• Windows Performance Monitor
Slow Standard Batches
Typical problems:
• Invoice posting, inventory closing, master
planning, settlements…
• AX is built from complex code, disable not used
one (e.g. credit limit checks)
• Write your custom posting using a minimum
number of standard objects
Code Redesign
• Skip delete actions, use doDelete(), doInsert(), doUpdate(),
maintain consistency in other way
• Normalize tables (e.g. for faster updates or data reduction)
• De-normalize tables (e.g. for faster reads)
• Use direct SQL / stored procedures
• AX may generate a filtered cross join instead of inner join. TSQL inner join can improve work orders – magnitudes faster
Data Cleanup
•
•
•
•
•
•
Parm data
Closed Orders
Old journals
Temp data
Logs
Standard AX cleanup procedures
Archive Data
• Move only master data, balances and open transactions during
upgrade
• Intelligent data management framework
http://technet.microsoft.com/en-us/library/hh378082.aspx
• Purge function removes or deletes data from a set of related tables,
from the production database
• Archive function moves data from all related tables to an archive
database which can be used for reporting purposes
External Tools for Heavy
Imports
•
•
•
•
SQL integration services
Direct T-SQL integrations, bulk insert
DIXF
Third-party solutions
Conceptual Redesign
Reduce number of dimensions
Choose parameters that require less calculations
Shorter retrospective
Simple costing model
120 page sales report run every week VS a sales cube?
Detailed transactions in AX imported from another system VS just a
consolidated view?
• Apply misc. charges to 20K invoice records VS post 10 expense
lines?
•
•
•
•
•
•
Case Study
Wrong Hardware configuration
Problem:
– Customer complains that the system is slow for all users during
the first hour of work
Steps:
– Log into DB and AOS server via RDP
– Check CPU and RAM usage
Solution:
– There was only 8 GB of RAM on the SQL server machine. Move
SQL to another machine, add more RAM
Case Study
Design relations by Alternative key instead of RecID
Problem:
– Import large plain CSV files into one header and five child tables.
With RecID only one-by-one insert is possible
Solution:
– Generate a custom alternative key (line number in the file),
create an additional alternate-key based relation, use bulk insert
operations
Case Study
Select field list
• A billing invoicing batch was ~5 times faster (48
hours to 9 hours) after removing “not used”
fields form select statement fields list
Case Study
Join optimization
• Operation performance
increased > 10 times after
replacing one outer join
with 6 separate inner joins
Planning for Performance
•
Define measurable and realistic performance benchmarks in your contract or TDD
•
Estimate the amount of data, legacy data, yearly increment, peak loads (e.g. ecommerce during the Christmas season)
•
Estimate amount of users, concurrent users, legal entities, web-users, posted lines
per hour, yearly incremental users and peak values
•
Design heavy jobs to run at night or off-peak times
•
Check recommended MS hardware requirements and plan the budget in advance
http://www.microsoft.com/en-us/download/details.aspx?id=11094
Planning for Performance
Define critical time-sensitive tasks, like
– Invoicing must finish during the night before the 1st day of a
month
– Customer on the phone should not wait more than 10 seconds to
create an order
• Plan and schedule performance tests in advance
– Your test data should be as close to the production system as
possible. Ideally – a backup of production DB
• Try to emulate production workload and system topology
Planning for Performance
Challenge: There could be an issue in
standard AX code!
– Do a ‘soft’ go-live: do not put your client’s business
under too much risk
– Run most critical processes through performance
monitoring tools in advance, ideally during the Build
Phase
– Identify data volumes to be processed, and plan
alternative solutions in advance
Summary
• Addressing performance
– Problem identification
– Troubleshooting: conceptual approach
– Most efficient and effective tools
• Implementation examples: issues, ways to
troubleshoot, results
• Planning for performance
Thank you!
• Questions?
• Suggestions?
• Comments?
Anna Kupchenko ([email protected])
THANK YOU
Please fill out your evaluation