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?
Hi jmetz,
Are you talking about Dynamics SL tables or other SQL tables? I wouldn't recommend removing indexes on SL tables.
Carolyn
Technical Support Engineer
**This posting is provided "AS IS" with no warranties and confers no rights.
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.
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?
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
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?
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.
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 ;-)
This is the Dynamics SL forum. You might get more help with your questions in the forum for AX.
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.