Adjust Cost is run every day. Normally taking about 15 minutes.
Suddenly for no reason it starts taking 8-10 hours everyday that it is run, and it is run everyday.
I looked for a long time, in the end looking at what the SQL was doing. It was using the index (ILE) Item No.,Variant Code,Drop Shipment,Location Code,Posting Date. (Enabled, Maintained in SQL as well)
This was when it got to ItemLedgeEntry.Next = 0 OR Level Exceeded on the function “MakeSingleLevelAdjustment”
During monitoring I found that this command cause a duration of on average 2400 ms (2.4 seconds) and in one instance ~33000 (33 seconds) this was when I was on the only user in the database.
I tried to do an update statistics on the Item Ledger Entry Table but this did not work.
To resolve this I add, as the second key the list, an index that was simple “Item No.”.
The routine dropped down to 4 minutes, and SQL used the key “Item No.” with FAST 5.
Anyone else experienced this, or have any idea why SQL would suddenly start struggling on an Index?
PLEASE NOTE : I am 99.5% certain that nothing has been modified that would affect the ACIE routine.
Sorry I forgot to mention I also did a DBCC Showcontig and the results were great, Fragmentation, Extents, Scan Density all a lot better than I have seen on most customer sites.
As I said above I am aware that it was using the wrong index, hence why I added one. The question is, with the database in great optimisation, why does the SQL engine use a poor index?