GET OPTIMAL PERFORMANCE FROM AX DURING PEAK – PART 2

GET OPTIMAL PERFORMANCE FROM AX DURING PEAK - PART 2

This is the second of our weekly posts over the Peak period outlining how to get started with monitoring and improving your systems. If you haven’t had a chance to read the first in our series of weekly posts, head over there now.

The run up to Christmas can be a fraught time for online retailers as they battle to ensure optimal sales aren’t hindered by software performance issues. The key to keeping one step ahead is setting up a comprehensive monitoring system to allow you to identify and tackle red flags before they cause more serious issues.

If you’ve already read last week’s blog then you’ve installed DynamicsPerf and have started to gather data about your system’s performance – but what exactly should you be looking for?

We would suggest that the first thing to look at is long running queries, which are resource hungry and slow your system down, leading to poor performance and potentially a bad user experience.

To identify the top 25 long running queries, run the following SQL query on your DynamicsPerf database:

USE DynamicsPerf

SELECT TOP 25 SQL_TEXT, CAST(CALL_STACK AS NVARCHAR(4000)) AS CALL_STACK, COUNT(SQL_TEXT) AS EXECUTION_COUNT, AVG(SQL_DURATION) AS AVG_DURATION_MS, AVG(ROWS_AFFECTED) AS AVG_ROWS_AFFECTED

FROM AX_SQLTRACE_VW

GROUP BY SQL_TEXT, CAST(CALL_STACK AS NVARCHAR(4000))

ORDER BY AVG_DURATION_MS DESC

The result is a list of the longest running queries, grouped by unique stack trace and sorted by the average time that the query takes to run. The stack trace can be used to pinpoint why a query runs slowly.

Another query that is useful to run on your DynamicsPerf database is shown below:

USE DynamicsPerf

SELECT TOP 25 UNIQUE_QUERIES.SQL_TEXT, QUERY_PLAN, (AVG_ELAPSED_TIME/1000) AS AVG_DURATION_SECONDS, EXECUTION_COUNT, TOTAL_ELAPSED_TIME, AVG_LOGICAL_READS, STATS_TIME

FROM QUERY_STATS_VW, INNER JOIN

(SELECT SQL_TEXT, MAX(STATS_TIME) AS LATEST_STATS_TIME FROM QUERY_STATS_VW GROUP BY SQL_TEXT

) AS UNIQUE_QUERIES ON QUERY_STATS_VW.SQL_TEXT = UNIQUE_QUERIES.SQL_TEXT AND QUERY_STATS_VW.STATS_TIME = UNIQUE_QUERIES.LATEST_STATS_TIME ORDER BY AVG_ELAPSED_TIME DESC

This query extracts similar information from a different view in the DynamicsPerf database and the results include the query plan, which can be useful in identifying the root cause for a query running slowly.

Armed with this information, you can focus your efforts to derive maximum benefit; a query with a high average run time and a high execution count is likely to result in poor performance. Having worked out which queries deserve attention, you can investigate these to identify ways in which their performance can be improved, for example through code improvements or SQL maintenance.

You can’t tackle issues that you don’t know about so it’s really important to keep one step ahead of any potential problems. If you’re short on time or resources and need a bit of help, please don’t hesitate to get in touch.

 
READ: GET OPTIMAL PERFORMANCE FROM AX DURING PEAK – PART 3
 

Don’t forget to follow AgileCadence on LinkedIn for more information on how to diagnose and rectify any issues with your systems, we will post hints and tips every week over peak.