StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="https://community.dynamics.com/product/nav/navtechnical/b/navteam/archive/2010/11/04/performance-analyzer-1-0-for-microsoft-dynamics.aspx" rel="nofollow">Performance Analyzer for Microsoft Dynamics</a> can be used to analyze expensive and long running queries, missing clustered indexes, incorrect and missing indexes, hidden clustered index scans, etc. in AX DB.</p> <p>A way to eliminate the amount of unnecessary indexes is to search for indexes that are a left key subset of another index on the same table. Unless the subset key is unique, its usefulness is subsumed of the superset key. To get a list of such indexes you can run the following query:</p> <pre><code>SELECT * FROM INDEX_STATS_CURR_VW O WHERE INDEX_DESCRIPTION NOT LIKE '%UNIQUE%' AND EXISTS ( SELECT * FROM INDEX_STATS_VW I WHERE I.RUN_NAME = O.RUN_NAME AND I.TABLE_NAME = O.TABLE_NAME AND I.INDEX_KEYS <> O.INDEX_KEYS AND I.INDEX_KEYS LIKE O.INDEX_KEYS + ',%' AND O.USER_SEEKS = 0 ) ORDER BY TABLE_NAME, INDEX_KEYS </code></pre> <p>To get an overview of all indexes that haven't been used during the complete monitoring period you could run the following query:</p> <pre><code>SELECT TABLE_NAME, INDEX_NAME, INDEX_DESCRIPTION, INDEX_KEYS, INCLUDED_COLUMNS, SUM(USER_SEEKS) AS USER_SEEKS, SUM(USER_SCANS) AS USER_SCANS, SUM(USER_LOOKUPS) AS USER_LOOKUPS, SUM(USER_UPDATES) AS USER_UPDATES FROM INDEX_STATS_VW WHERE INDEX_DESCRIPTION NOT LIKE '%UNIQUE%' GROUP BY TABLE_NAME, INDEX_NAME, INDEX_DESCRIPTION, INDEX_KEYS, INCLUDED_COLUMNS HAVING SUM(USER_SEEKS) = 0 AND SUM(USER_SCANS) = 0 AND SUM(USER_LOOKUPS) = 0 ORDER BY 9 DESC </code></pre> <p>You could also identify queries that use an index seek to filter data:</p> <pre><code>SELECT TOP 100 * FROM HIDDEN_SCANS_CURR_VW ORDER BY TOTAL_ELAPSED_TIME DESC </code></pre> <p>The following would display 10 most expensive queries ordered by average logical reads from a SQL Server DMV perspective:</p> <pre><code>SELECT TOP 10 SQL_TEXT, QUERY_PLAN, TOTAL_ELAPSED_TIME, AVG_ELAPSED_TIME, MAX_ELAPSED_TIME, AVG_LOGICAL_READS, EXECUTION_COUNT FROM QUERY_STATS_CURR_VW ORDER BY AVG_LOGICAL_READS DESC </code></pre> <p>You'd also need to have a look at other parameters like execution count (how many times queries were executed).</p> <p>If you wanted to have an overview of the AX queries running longer than 1000 ms you could run the following query:</p> <pre><code>SELECT CONVERT(nvarchar,CREATED_DATETIME,101) AS CREATED_DATE, DATEPART (hh, CREATED_DATETIME) AS HOUR_OF_DAY, COUNT (CREATED_DATETIME) AS EXECUTION_COUNT, SUM (SQL_DURATION) AS TOTAL_DURATION, AVG (SQL_DURATION) AS AVERAGE_DURATION FROM AX_SQLTRACE_VW WHERE SQL_DURATION > 1000 and CREATED_DATETIME > '04/01/2011' GROUP BY CONVERT(nvarchar, CREATED_DATETIME, 101), DATEPART (hh, CREATED_DATETIME) ORDER BY CREATED_DATE, HOUR_OF_DAY </code></pre> <p>Hope that helps.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload