AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 09.09.2014, 16:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,617 / 848 (80) +++++++
Регистрация: 28.10.2006
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:
  • Introduction and SQL Server/storage settings.
  • AX application and AOS configuration settings.
As with Part 1, each area (below) includes a link to a SQL script (or perfmon templates in the case of hardware analysis), which can assist you with data collection.

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:
  • Index changes are implemented in the AOT in the AX application. Index analysis (similar to the other areas in part 2) is a huge area in itself, but I plan to provide an indexing primer for AX in a future post.
  • 'Disable' below means to disable the index in the AOT, which will remove the index from the underlying database. When disabling indexes, take care with unique indexes in particular. However there may be an alternative index that can be used to enforce uniqueness.
Indexes to disable

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:
  1. You can use the index usage statistics for the table (history can be kept in Performance Analyser) to determine potential candidates.
  2. They are part of the physical structure of the table and determine the physical order of data.
  3. They are therefore good for queries returning large result sets or using 'order by'.
  4. Non-clustered indexes on the same table point to rows in the clustered index, so consider the impact on the table as a whole.
After analysis using the above script (and in accordance with your usual development/deployment procedures), change the ClusterIndex table property in the AOT to the name of the new clustered index. See http://msdn.microsoft.com/en-us/library/aa871620.aspxReview

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...
  • Queries with a high execution count may be resolved by table caching changes (see checklist part 1) or reducing loops in the application code (identify and trace the AX process, then review the database calls column in Trace Parser).
  • Queries with a high average or max duration may be resolved by index changes.
  • Queries with a large difference between minimum and maximum duration could be impacted by environmental factors such as blocking (see below) or parameter sniffing (see checklist part 1).
  • This will usually leave some queries which either require code analysis (use the call stack and userid to get information on the AX process) or be 'by design' due to the required business logic.
Expensive queries

Recommendation:-Review:
  • Queries consuming most TOTAL time on SQL Server
  • Queries potentially causing large disk i/o;
  • High execution count could be loops in application code.
How to...Review execution plans and whether index changes may help. Try to find the source (e.g. SQL Agent job, AX call stack, etc.) and tune the query.Missing index queries

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:AX trace collection

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:How to:You can identify the blocked and blocking processes in AX from the AX call stacks, by correlating SQL statements in long query traces and/or AX traces with SQL statements in the SQL profiler trace.

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):http://www.microsoft.com/en-gb/downl....aspx?id=29063
  • (works with the following SQL Server versions: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014)
Deadlocks

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:
  • SQL Server and AOS
  • AOS and Client
  • AOS and application files [AX 2009 and lower versions only]

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).
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
emeadaxsupport: AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration] Blog bot DAX Blogs 0 05.09.2014 21:11
emeadaxsupport: AX Performance Troubleshooting Checklist Part 1B [Application and AOS Configuration] Blog bot DAX Blogs 0 05.09.2014 21:11
atinkerersnotebook: Walkthrough & Tutorial Summary Blog bot DAX Blogs 1 09.09.2013 09:11
emeadaxsupport: New Content for Microsoft Dynamics AX 2012 : October 2011 Blog bot DAX Blogs 0 27.10.2011 17:11
axinthefield: Dynamics AX Event IDs Blog bot DAX Blogs 0 01.03.2011 22:11

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 12:55.