This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

Analysis scripts for Performance Analyzer v2.0 Page 10

This is page 10 of 10 of the general performance analysis scripts online for the Performance Analyzer 2.0 tool. See page 1 for the introduction. Use the links in the table below to navigate between pages.

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
–WHERE CREATED_DATETIME > = ‘20150101’
GROUP BY SQL_TEXT,
CAST(CALL_STACK AS NVARCHAR(4000))
ORDER BY 4 DESC

–get an overview how frequently expensive queries are being logged by day and hour:

SELECT TOP 100 *
FROM HIDDEN_SCANS_CURR_VW
ORDER BY TOTAL_ELAPSED_TIME DESC

—
— OPTION_FAST_QUERIES
—
————————————————————————-
— Find queries option(fast) set that have sort operations
— Dynamics AX only query
—
— 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
————————————————————————–

SELECT TOP 100 *
FROM QUERY_STATS_CURR_VW QS
— INNER JOIN FT_CTE2 FT2 ON QS.QUERY_PLAN_HASH = FT2.QUERY_PLAN_HASH
WHERE QS.SQL_TEXT LIKE ‘%OPTION(FAST%’
AND QS.QUERY_PLAN_TEXT LIKE ‘%PhysicalOp=”Sort”%’
ORDER BY QS.TOTAL_ELAPSED_TIME DESC

INSERT #STMT VALUES(“SELECT SUM(T1.ACCOUNTINGCURRENCYAMOUNT) FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN GENERALJOURNALENTRY T2 WHERE (T1.PARTITION=?) AND ((T2.PARTITION=?) AND ((((T2.POSTINGLAYER=?) AND (T2.LEDGER=?)) AND ((T2.ACCOUNTINGDATE>=?) AND (T2.ACCOUNTINGDATE<=?))) AND (T1.GENERALJOURNALENTRY=T2.RECID))) AND (EXISTS (SELECT ‘x’ FROM DIMENSIONATTRIBUTELEVELVALUEVIEW T3 WHERE ((((T3.PARTITION=?) AND (T3.PARTITION#2=?)) AND (T3.PARTITION#3=?)) AND (((T3.DIMENSIONATTRIBUTE=?) AND (T3.DISPLAYVALUE=?)) AND (T1.LEDGERDIMENSION=T3.VALUECOMBINATIONRECID))))) AND (EXISTS (SELECT ‘x’ FROM DIMENSIONATTRIBUTELEVELVALUEVIEW T4 WHERE ((((T4.PARTITION=?) AND (T4.PARTITION#2=?)) AND (T4.PARTITION#3=?)) AND (((T4.DIMENSIONATTRIBUTE=?) AND (T4.DISPLAYVALUE=?)) AND (T1.LEDGERDIMENSION=T4.VALUECOMBINATIONRECID))))) AND (EXISTS (SELECT ‘x’ FROM DIMENSIONATTRIBUTELEVELVALUEVIEW T5 WHERE ((((T5.PARTITION=?) AND (T5.PARTITION#2=?)) AND (T5.PARTITION#3=?)) AND (((T5.DIMENSIONATTRIBUTE=?) AND (T5.DISPLAYVALUE=?)) AND (T1.LEDGERDIMENSION=T5.VALUECOMBINATIONRECID)))))” )