Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Unanswered

Any Issues removing unused nonclustered indexes

Posted on by 60

I have been monitoring several nonclustered indexes for a couple of months now and would like to remove some that are not being read from.  Is there any issue or concern with removing "factory" created indexes that aren't being used?  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Any Issues removing unused nonclustered indexes

    I'm assuming the notion of index management is the same regardless of whether it's AX, SL, etc., correct?  Does SL have an AOT just like AX?  In addition, the concept of removing unused indexes is largely a DBA function and should be a transparent activity to the application.  But if you don't have a concrete answer, I will pose this question to the AX forum.  Thanks.

  • Re: Any Issues removing unused nonclustered indexes

    Hi Allen,

    This is the Dynamics SL forum.  You might get more help with your questions in the forum for AX.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Any Issues removing unused nonclustered indexes

    Example: the REQTRANS table is very heavily hit, and what I would consider a bit over-indexed.  One of the indexes is called FuturesIdx - it has been written to over 118 million times since the last instance restart and only read from 4 times - probably me querying it.  The index is not huge - 500MB - but considering I have six or seven other environments that I periodically restore production into, that 500MB can add up quick.  That's just one example - I have many other similar scenarios; hence my asking how to achieve it ;-)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Any Issues removing unused nonclustered indexes

    As as DBA I do realize the benefits - performance tuning is something I do on a regular basis and removing unused indexes can help in various areas.  I know if they are being used or not by querying the dynamic management views, and with some creative querying I could probably find out if any hints are being used - stored procs are not widely used in DAX 2009.  I have never seen a hint used, not to mention it's very poor practice, but it was something I considered.

    I'm not looking to delete them directly from the table.  What I need to know how to do is delete ones that may be in another layer.  I am not an AX developer by any means and have only added new indexes; never removed ones that were shipped with the product.  That is the guidance I'm looking for - how do I remove an index that shipped with DAX and is never used.  I understand the implications, etc.

  • Re: Any Issues removing unused nonclustered indexes

    Hi Allen,

    You might get a slight increase in performance and perhaps a decrease in disk space consumption.  We don't believe it will be great.

    However, there might be some stored procedure/query that is using an index hint that is hard coded to look at that particular index.  If that is the case, you will get an error message.   You also might run the risk of a decrease in performance if the index does happen to be needed for a task.

    Remember, if someone runs the "Update Master Views, Indexes, and Stored Procedures" scenario in Database Maintenance, it will rebuild all the indexes you deleted.

    What particular kind of guidance are you looking for?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Any Issues removing unused nonclustered indexes

    Carolyn,

    I too am looking at removing unused indexes.  The reason is simple: if a non-clustered index is getting written to over 2.5 million times but not read from, it's dead weight and degrades performance, not to mention consumes valuable disk depending on the size of the index.  Then couple that with the fact that IDMF has to have the same schema and I do restores into five non-prod environments and that disk space consumption starts to grow exponentially.

    I would like some guidance on how I can remove indexes.  It's safe to assume this will not harm the product - indexes are not something that the application is aware of, or at least should not be.

    Allen

  • Re: Any Issues removing unused nonclustered indexes

    Hi jmetz,

    I would not recommend removing any standard indexes within the Dynamics SL application database. The soplan table contains the planning records that are created by Process Manager so it would be expected to have a large number of writes to the table. The soshiplot table contains the warehouse / lot serial information related to lines on a Shipper. Again there could be a large number of writes to that table when Process Manager creates the Shippers. Is there any particular reason that you were looking at removing these indexes?

  • jmetz Profile Picture
    jmetz 60 on at
    Re: Any Issues removing unused nonclustered indexes

    Specifically the SOSHipLot and SOPlan tables.  I have been tracking 3 indexes between those two tables that are taking about 2+ million writes a week with no reads.  I would like to remove them but I wanted to know if the application would be able to handle that.

  • Re: Any Issues removing unused nonclustered indexes

    Hi jmetz,

    Are you talking about Dynamics SL tables or other SQL tables?  I wouldn't recommend removing indexes on SL tables.  

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,835 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,526 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans