Microsoft Dynamics AX (Archived)

How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

(0) Share

Report

Posted on by Community Member

Hi All,

Can Someone Suggest me like how to find out missing index. And upon findings how to add the same to the table.

Please Let me know .

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

Hi MS Mania,

when are you getting this error, please provide more information.

You can check event viewer there must be table name which is missing index.

Was this reply helpful? Yes No
Martin Dráb 236,336 Most Valuable Professional on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

Do you mean indexes that would improve performance of commonly run queries? SQL Server can give you such information; you can also get it from Performance Analyzer for Microsoft Dynamics (DynamicsPerf) rather then configuring SQL Server by yourself.

Regarding adding indexes to AX tables, talk to your X++ developer. You need somebody with access to AOT and knowledge of your company development practices (naming conventions, version control etc.) - he or she can then simply right-click the Indexes node of a table, choose New Index, set its properties and add required fields.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

Hi MArtin,

I ran the following SQL Query to find the missing indexes.

========================================================

SELECT db_name(d.database_id) dbname

, object_name(d.object_id) tablename

, d.equality_columns

, d.inequality_columns

, d.included_columns

,'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)

+ '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'

+ ' ON ' + d.statement

+ ' (' + ISNULL (d.equality_columns,'')

+ CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END

+ ISNULL (d.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement

FROM sys.dm_db_missing_index_groups g

join sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle

join sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle

WHERE d.database_id = '8' and d.object_id = d.object_id

ORDER BY 2 DESC

================================================================

In the Output the query did not pull the table I Required .

Does this mean that there is no Indexes are missing in the Required Table ?

One more Question creating Indexes to all the fields in the table will create any problem ?

Was this reply helpful? Yes No
Martin Dráb 236,336 Most Valuable Professional on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

If you have a problem with your query, split it to smaller pieces and debug them by one. For example, do you have anything in dm_db_missing_index_groups? If not, there clearly isn't any data collected and you don't have to look at anything else. If it contains data, it's filtered out by some part of the query. By the way, generating the CREATE INDEX statement is waste of time; you won't be able to use in AX. If you throw it away, your query will become easier to debug.

Creating unnecessary indexes surely is a problem, because they must be maintained on every insert, update and delete (which is wrong for performance) and they consume space in database (so you have to buy bigger disks, backups and restores take more time etc.).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

Actually I have A batch which usually runs for about 2 to 3 Hrs. But nowadays it is taking more than 5 hours.

This Batch job deals with the Table Called "pmf_ZeroLineSalesConfirm" .

Adding missing indexes could resolve this issue?

Is there any other way to determine why this batch job is getting executed than it's normal time.

Was this reply helpful? Yes No
Martin Dráb 236,336 Most Valuable Professional on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

You usually can't fix problems without understand what's wrong. Adding an index will help if the problem is caused by a missing index, but that's just your guess. If it's waiting for a locked resource, you have a bug in code, there is a problem with hard disks or anything else, adding an index won't help.

If you believe that a slow-running query is responsible, collect details about the query and run in SSMS. There you can analyze statistics and the execution plan, which will tell you which part is the problem and may also suggest some indexes.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

Thanks for your suggestion. But Am new to AX.

Please let me know how to collect the details to run in SSMS.

And if I want to Find the missing Index then how to run the following query (dm_db_missing_index_groups) in SSMS

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: How to find the Missing Index in a Table and To Add Missing Index to the Necessary table

Sounds like you are asking for someone to do your job, but most troublesome, it sounds like you don't have the credentials for what you are attempting to do. I would urge you to not to touch nothing until you are guided by an experienced developer in your team.

Was this reply helpful? Yes No