axStart: Obsolete index

[Blog bot]

Источник: http://www.axstart.com/obsolete-inde...obsolete-index
==============

We all know indexes on AX tables will improve performance when querying these tables. There are all kinds of fancy features such as parameter sniffing, trace flags, etc. But if we look for example at the ledger Journal Trans table you will start thinking that maybe core dynamics AX has added too many indexes.

There are 27 indexes in AX 2012 R2. So what’s next…

These indexes are added, when specific features in AX are created. But do you use these features? Even when the configuration key is turned off the index is still there. Trust me, to maintain 27 indexes on a table is a huge overload. So how can we get right of these indexes?

The trick is to change the property Enabled to False. Next time you synchronize the database, they will disappear. But now you have to determine the obsolete indexes. I can give you some guidance:

• Unique indexes are never obsolete
• Indexes with 0 seeks are really not used. But check this over a longer period.
• Indexes with more updates then seeks are expensive.
• How unique is this index?

Find the answers to these questions and you know if it is obsolete. You have to investigate the sys.dm_db_index_usage_stats in SQL. In case this is too complex for you, an alternative could be the Kaya Performance Dashboard. This will collect all your data so you only have to set the property Enabled to False on the index in the AOT.



I can guarantee that the performance will double.



Источник: http://www.axstart.com/obsolete-inde...obsolete-index

[Vadik]

Цитата:

Сообщение от Blog bot

I can guarantee that the performance will double

Мамой клянусь! (с)

[gl00mie]

Цитата:

Сообщение от Blog bot

These indexes are added, when specific features in AX are created. Even when the configuration key is turned off the index is still there. Trust me, to maintain 27 indexes on a table is a huge overload. So how can we get right of these indexes? The trick is to change the property Enabled to False.

С одной стороны, конечно, спасибо за статью, а с другой, какой-то странный вывод делает автор: из контекста напрашивается решение "повесьте конфигурационные ключи на индексы", а не "отрубите их совсем".

Цитата:

Сообщение от Blog bot

I can guarantee that the performance will double.

- Вы можете гарантировать, что я получу за этот фильм премию Оскар?
- Я могу гарантировать все что угодно!
(с) Футурама