09.09.2014, 16:11
|
#1 |
|
Участник
|
emeadaxsupport: AX Performance Troubleshooting Checklist Part 2
Источник: http://blogs.msdn.com/b/axsupport/ar...st-part-2.aspx
============== This is Part 2 of a two part troubleshooting check list for general performance on Microsoft Dynamics AX. This part includes hardware, indexing, queries, blocking and code - at a high level. Please note this is just an outline of key areas for general guidance and not an exhaustive list. As you have landed here, you will probably be interested in this blog too: Top 10 issues discovered from Dynamics AX Health Check* http://blogs.msdn.com/b/axinthefield/archive/2013/06/18/top-10-issues-discovered-from-premier-field-engineer-dynamics-ax-health-check.aspx *Available through Premier Support Services. Part 2 naturally involves deeper analysis, which will be iterative and is probably where the vast majority of the time will be spent - but it requires a solid foundation. It is therefore recommended that you review Part 1 first, which is split over 2 pages:
Part 2A: SQL Server and AOS Logs Subsets of both of the following sets of logs can be found in the Performance Analyser. AOS: Windows Application and System Event Logs Recommendation:-One important example to look for affecting general performance: "RPC error: RPC exception 1726 occurred in session X. No ping from XX. Terminating the session. SPID XX for session id XX is still present in the database. Please delete the SPID from the database." See the following blogs. The first covers identifying the root cause, the second relates to resolving the issue if it is happening at the time.Steps to trace an AX session in the application event log back to a user http://blogs.msdn.com/b/axsupport/archive/2010/10/18/steps-to-trace-an-ax-session-in-the-application-event-log-back-to-a-user.aspx Dynamics AX and little Orphaned SPID Annie http://blogs.msdn.com/b/axinthefield...pid-annie.aspx How to...Connect to the SQL Server instance hosting your Performance Analyser database and run the following script: USE DYNAMICSPERF SELECT * FROM [dbo].[AOS_EVENTLOG]SQL Server Logs Recommendation:-One important example to look for affecting general performance: "SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file x." See the following blog in relation to diagnosing this particular error: http://blogs.msdn.com/b/karthick_pk/archive/2012/06/26/io_2d00_bottlenecks.aspx To investigate disk latency in more depth, in addition to perfmon you can also use PERF_HOURLY_IOSTATS_VW and PERF_IOSTATS_VW in the Performance Analyser. How to...Connect to the SQL Server instance hosting your Performance Analyser database and run the following script: USE DYNAMICSPERF SELECT * FROM SQLERRORLOG Part 2B: Hardware Analysis script:There is no SQL script as with other areas, but perfmon templates can be found in the Performance Analyser (details below). Collect SQL Server perfmon logs for 24h Recommendation-Collect perfmon logs using the relevant Performance Analyser 1.20 SQL Server template over a 24 hour period to capture a 'typical day'. How to...See http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide.aspx , section: 'Configure and Schedule Performance Counter Logging on Database Server'.Collect AOS perfmon logs for 24h Recommendation-Collect perfmon logs using the relevant Performance Analyser 1.20 AOS template over a 24 hour period to capture a 'typical day'. How to...See http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide-dynamics-ax-installation.aspx , section: 'Configure and Schedule Performance Counter Logging on AOS Server(s)'.PAL (Performance Analysis of Logs) tool Recommendation:-Analyse the above logs that you collected to identify potential hardware bottlenecks. PAL is a useful tool to get started with this. How to...See http://pal.codeplex.com/. Part 2C: Index analysis Analysis script: http://blogs.msdn.com/b/axsupport/archive/2014/09/01/microsoft-dynamics-ax-general-performance-analysis-scripts-page-2.aspx Note:
Recommendation:-This is a high risk area; as always, but particularly important here: be sure you understand the impact of what you are doing before going ahead. That said, it still needs to be done to avoid potential blocking issues and long durations on update, insert and delete operations.-Disable exact duplicates on the same table (it can happen!)-Disable left key subsets: indexes on the same table where some of the keys match from left to right, for example Index1 has index keys A, B, C and Index2 has index keys A, B, C, D. Disable Index2 (if for example Index2 is unique and Index1 is not, you can then make Index1 unique-Disable unused indexes: ensure you have taken into account periodic (monthly,quarterly,year end) processes. Usually I would suggest collecting index usage data continuously in the Performance Analyser and reviewing this on a quarterly basis. Of course if you added indexes recently or during the review period and know the reason why they were added, but they don't get the usage you expected you can always disable them earlier.-Indexes with a high number of included columns: either reduce the included columns or disable the index, depending on how each index is used.-Heavily updated indexes: Compare writes vs. reads. Consider disabling indexes with high writes vs. reads, or reducing the number of index columns. Avoid using highly updated columns, which can include enums , amounts and quantities (note this is a general rule again though; for example it may be valid for a high impact query using 'SELECT SUM(AMOUNT1)...' to have an included column on AMOUNT1).-Recversion indexes: RECVERSION should not be a part of AX Indexes due to the frequency of updates. Either remove the column or disable the index depending on how the index is used. How to...After analysis using the above script (if you are sure the index won't be required and in accordance with your usual development/deployment procedures), set the enabled property on each relevant index to No. Table Index Properties [AX 2012] http://msdn.microsoft.com/en-us/library/aa881522.aspx Non-clustered indexes Recommendation:-Missing indexes: see Query Analysis (Part 2D) below.-Index scans: Add missing indexes in the first instance (see Part 2D below). This may result in unused or low usage indexes (originally used for scans but superseded by new indexes), which you can then disable once you are confident they are no longer required. As troubleshooting becomes more in depth, query tuning (in the code) may also be required.-'Hidden index scans': This is an AX specific term. In a query execution plan it would appear to be a seek, hence the 'hidden' part. However the 'seek' is across an entire company (dataareaid) in AX, so due to the relatively high number of records it has to search through, it could be considered to be effectively a scan. Therefore the same advice as for index scans (above) will apply.-Indexes in SQL but not AX: identify indexes that are not defined in the AOT. They should be added to the AOT to be properly tracked as part of your code deployments and not lost during synchronisation. How to...After analysis using the above script (and in accordance with your usual development/deployment procedures), create indexes as per the following procedure. How to: Create an Index [AX 2012] http://msdn.microsoft.com/en-us/library/aa607289.aspx Clustered indexes Recommendation:-This is a high risk area so again be cautious and ensure you understand the impact, but if you get it right, it can in some cases lead to huge performance gains.-Tables without clustered indexes: Add an appropriate clustered index. See http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx-Changes to clustered indexes: As per the comments in the above Performance Analyser script, "Find clustered indexes that could be changed to one of the non-clustered indexes that has more usage than the clustered index [bearing in mind the above points]. This should be the LAST activity done in a performance tuning session. NOTE - CHANGING CLUSTERED INDEXES WILL TAKE A LONG TIME TO DO AND REQUIRES DOWNTIME TO IMPLEMENT." How to...Below is a few general points to consider when choosing a clustered index:
Recommendation:-Review all of the above in conjunction with the Performance Analyser and the analysis scripts provided. Index tuning is an iterative process so usually at least a few cycles of review/analysis/development/deployment can be expected. How to...Using the above analysis script. Part 2D: Query analysis Analysis scripts: http://blogs.msdn.com/b/axsupport/archive/2014/09/01/microsoft-dynamics-ax-general-performance-analysis-scripts-page-3.aspx http://blogs.msdn.com/b/axsupport/archive/2014/09/01/microsoft-dynamics-ax-general-performance-analysis-scripts-page-8.aspx Long queries from AX Recommendation:Iteratively review the long queries captured in the SQL statement trace log (SysTraceTableSQL table). In addition to the above scripts, you can use the following one to get an overview: USE DYNAMICSPERF select --cast(CALL_STACK as nvarchar(max)) as call_stack, --uncomment this line to get the call stacks cast(sql_text as nvarchar(max)) as sql_text, min(sql_duration) as min_duration, max(sql_duration) as max_duration, avg(sql_duration) as avg_duration, sum(sql_duration) as total_duration, count(*) as execution_count from [dbo].[AX_SQLTRACE_VW] where sql_duration>0 --filter out anything other than long queries and datepart(hh,CREATED_DATETIME) between 8 and 18 --long queries captured from the AX client during your typical business hours - change or comment as required --and call_stack like '%_%' --filter by a particular class/method --and sql_text like '%_%' --look for a particular SQL statement, e.g. one found in a blocking chain. group by --cast(CALL_STACK as nvarchar(max)), --also uncomment this line if you uncomment line 2 cast(sql_text as nvarchar(max)) order by sum(sql_duration) desc How to...
Recommendation:-Review:
Recommendation:-Identify queries that the optimizer suspects can be optimized by new or changed indexes. NOTE: DO NOT add these indexes verbatim without deep analysis. Large INCLUDED lists are NOT recommended for AX.How to...Create the index in the AOT after analysing the query execution plan(s) related to the missing index recommendation (query MISSING_INDEXES_CURR_VW in Performance Analyser to retrieve the execution plans - page 3 of the analysis scripts). How to: Create an Index [AX 2012] http://msdn.microsoft.com/en-us/library/aa607289.aspxScanning queries Recommendation:-Identify queries causing index / hidden scans in conjunction with index tuning above.How to...Review execution plans and whether index changes may help. Try to find the source and tune the query.OPTION (FAST) queries Recommendation:-Find queries option(fast) set that have sort operations (AX only). Either we don't have an index to match the order by clause or the query is potentially to complex for SQL to pick that index.How to...Review execution plans and whether index changes may help. Try to find the source and tune the query.Review Recommendation:Query tuning is an iterative process so usually at least a few cycles of review/analysis/development/deployment can be expected. Review all of the above in conjunction with baseline analysis queries to see which queries are faster, slower, etc between changes (remember that the baseline queries are only an indication and depend on multiple other factors that may be interacting between runs).How to... http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/baselines-with-performance-analyzer-for-microsoft-dynamics-dynamicsperf-are-here.aspx Part 2E: Blocking Analysis script: http://blogs.msdn.com/b/axsupport/archive/2014/09/01/microsoft-dynamics-ax-general-performance-analysis-scripts-page-4.aspx Performance analyser setup Recommendation:-Ensure that the SQL profiler trace is running and AX long query trace is running (part of Performance Analyser installation).How to...See Performance Analyser setup blogs:
Recommendation:-Try to collect AX server traces at the times when blocking is occurring (if possible).AX 2012: http://blogs.msdn.com/b/axperf/archive/2011/11/18/collect-ax-2012-event-traces-with-windows-performance-monitor.aspxAX 2009: http://blogs.msdn.com/b/axinthefield/archive/2010/12/28/dynamics-ax-tracing-part-1.aspx http://blogs.msdn.com/b/axinthefield/archive/2011/03/25/dynamics-ax-tracing-part-2.aspxBlocking analysis Recommendation:-The are multiple potential root causes of blocking (it can often be a symptom of another underlying problem), which can include:
Understanding and resolving SQL Server blocking problems: http://support.microsoft.com/kb/224453 TempDB Recommendation:-Performance Analyser can be used to identify whether there is an allocation bottleneck on tempdb.How to... http://blogs.msdn.com/b/axinthefield/archive/2011/06/12/tempdb-blocking-with-dynamics-ax.aspxReal time monitoring Recommendation:-Often blocking is best resolved by monitoring at the time it is occurring.How to...Do one of the following (depending on your AX version):
Recommendation:-If deadlocks were occurring you can see the details of these separately (including deadlock graphs) in the SQL profiler trace.How to...You can query DEFAULT_TRC_VW in Performance Analyser for deadlock events, then follow the advice below.Analyze Deadlocks with SQL Server Profiler: http://msdn.microsoft.com/en-us/library/ms188246.aspx Further information:
Part 2F: BI Performance General tips Recommendation:-Consider hosting SQL Server Reporting Services / SQL Server Analysis Services on a separate SQL server for improved performance and scalability. -Review the following articles:
Part 2G: Network Network Recommendation:-Confirm there are no losses of connectivity and minimum network requirements are being met as per the system requirements. AX 2009 system requirements: http://www.microsoft.com/en-us/downl....aspx?id=26568 AX 2012 system requirements: http://www.microsoft.com/en-us/downl....aspx?id=11094 How to...Review with your LAN/WAN provider. There are various ways of checking this but with the relevant approval, you can run a simple ping test to a text file, i.e. in the command prompt: ping [IP/machine name] -t >[filepath\filename]. Key points to check between:
For further information regarding AX architecture, see: System architecture [AX 2012] http://technet.microsoft.com/en-us/l.../dd362112.aspx AX 2009 implementation guide http://www.microsoft.com/en-us/downl...s.aspx?id=3974 Part 2H: Code review Subsets of both of the following sets of logs can be found in the Performance Analyser. Long query traces Recommendation:-You can use the call stacks from the long query traces to guide you in relation to processes which are intermittently slow, etc (see query analysis above). See above ('Query analysis').AX traces Recommendation:-Collect AX traces of specific processes when they are running slowly, then analyse using Trace Parser and identify bottlenecks. Dynamics AX Trace Parser (Part 3) http://blogs.msdn.com/b/axinthefield/archive/2011/06/26/dynamics-ax-tracing-part-3.aspxHotfixes Recommendation:-Use the issue search in https://lcs.dynamics.com/ to identify any kernel (binary) and/or application hotfixes which may help. The most effective searches are usually on specific methods (i.e. dependent on the above trace analysis). How to...See Microsoft Dynamics AX Lifecycle Services videos: https://www.youtube.com/playlist?list=PLt7Ttvo8Z9w8luk6FdMZuhTMwA8Um9bc0 or alternatively the user guide: http://technet.microsoft.com/en-us/library/dn268616.aspx Custom code tuning Recommendation:-Tune and / or redesign any processes where custom code is the bottleneck (based on above trace analysis). How to...Refer to your application partner or development team for guidance. See also: The book 'Inside Microsoft Dynamics AX' for your AX version: https://www.microsoftpressstore.com/search/index.aspx?query=inside+dynamics+ax&x=0&y=0 Code analysis best practices and tools: http://community.dynamics.com/ax/b/axresources/archive/2014/07/18/performance-resources-for-microsoft-dynamics-ax.aspx#Code Источник: http://blogs.msdn.com/b/axsupport/ar...st-part-2.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
|
| За это сообщение автора поблагодарили: Logger (3). | |
|
|
| Опции темы | Поиск в этой теме |
| Опции просмотра | |
|
Линейный вид
