Investigating DAX »Story #1: update. They were using DAX2. Every time when they were running inventory closing, the system locked all (or at least – most of the) invent.

Trans records, safely preventing users from any regular work with logistics module. Before I came to the customer I already heard rumors that new functionality of non- financial transfer closing can cause long duration locks to inventory data. Now I had a chance to witness it with my own eyes. Windows 10 Full Install Updates Keygen Installer here. The sql statement which was a reason for show- stopper problem in IC was very easy to find. It was the following query from Invent. Cost. Non. Financial.

If you’ve never checked on your app subscriptions, I don’t blame you. It’s a feature buried pretty deeply in your iOS device. But then, you could discover an. This is opulence. Suddenly, there is extra light blasting from behind my TV screen, making a day-glow title sequence positively atomic. The DreamScreen, a.

1. In my previous post, I stated that InventSumLogTTS table is not updated after related inventory transaction was physically posted. I was totally wrong.
2. GIF made from video of the THAAD missile defense system test launched yesterday from Alaska to hit a target sent from north of Hawaii (US Missile Defense Agency) The.

Transfer. Handler. Invent. Trans() method: update? If you explode nested views, you find out that this query is joining 1.

After I started to look onto the query, I understood that from application logic point of view, the query is Okey. It must update all non- financial transfers for a given Dimension Group in one update.

It should not lock users from current operations (e. I made an assumption, that maybe this query is just causing lock escalation from record level to page level and then to table level, thus causing total lockup. I tried to disable lock escalation for invent. Trans with alter table set(lock.

It did not help. Then I tried to analyze lock information by selecting data from sys. I found out that this statement placed U- lock over seemingly every page of the invent. Trans (Or, at least, all pages with given dataareaid). I never had a lot of experience with U- locks before. I saw them in a database from time to time, but I red somewhere in SQL BOL that it is a kind of short duration locks issued by update statement to maintain consistency and prevent deadlocks. When I tried to find more information on update locks in internet, I ran into perfect article  Read Committed and Updates by Craig Freedman.

It turned out that update statement in SQL (and update. What happens next ? If update statement has simple logic (like update table set nonindex. So for simple cases, U- lock is placed on a row for really short duration – from reading of a page into memory until end of the page’s in- memory scan.

But in complex queries, especially in queries with self- join, U- lock is placed in the very beginning of SQL Statement execution (when the page is being read for the first time) and kept until query reached a home- run stage, when all records to be updated are defined and the query started update phase itself. Since our query in question has a join of 1. In the worst case (and this worst case happens too often), the system was choosing incorrect execution plan. In this case, U- Locks persisted for 3. It was locking all the pages in the table, because the system was simply reading all pages with given data. Area. Id (because invent.

Trans do not have more specific index suitable for query conditions) thus leading to a total lock- up for 3. BTW, there is no much point in locking here, because these updates being called only from starting part of Inventory Closing, which is executed in single user mode.

Chances that these invent. Trans records would be updated but some other user activity (not IC) are pretty thin: There is no much you can do with already financially updated inventory transfer transactions. The only thing which is coming to my mind is an idea that some crazy user will start to change Invent. Dim. Id of inventory transactions via Item Details- > Functions- > Edit dimensions function. But I think we can ignore this possibility .

First thing  I tried  to replace an update. Since outermost select does not use . Inner select use . As a next stage, I tried to split one select onto two nested selects. For everyone who is into SQL Statement tuning it sounds like very counter- natural, but since we are unable to directly influence the query plan in X++ with hints, we must somehow ensure SQL Server selecting more or less decent execution plan.

I tried the following approach: On outer level I placed a query which was simply producing a list of item. Ids, which have non- closed transfers (any transfers, maybe even financial ones), in nested while select I used the copy of original select, only with adding . This approach brings good results: Processing of non- financial transfers in my case took only 1- 1.

Maybe in your case, if you have many items with transfers, it will take about 3. Well, maybe this approach is not the most perfect one. Maybe I should try to combine outermost select (to select item list) and inner update. Maybe this U- Locks won’t be so nasty after all. If the statement takes only 5- 1.

But for my case, it was acceptable solution and the customer kept to use it. As far as I understand, the whole feature was first designed for DAX2. DAX2. 00. 9 without too much testing.

Also, to my knowledge currently MS is working on a patch for this feature in DAX2. Story #2:  Wrong query execution plan can ruin the helper- based algorithm in Axapta. On another project, the customer often had performance issues with inventory closing cancellation. In the beginning, it was a mere issue of total execution time.

When they were running IC cancellation in interactive mode, it took around 3- 4- 5 hours to complete. I pointed them that to the fact, that if you execute IC cancellation in batch mode, it runs many times faster than in interactive. The trick is simple: When IC cancellation is started, it checks the mode of execution. If  it is  running in a batch mode, it checks which items has invent. Settlement records in inventory closing being canceled, then it spawn a batch task  of class Invent. Cost. Closing. Cancel. Next it iterates over all indirect cost journals created by inventory closing being canceled, and for every cost journal, it spawns a batch job of class invent.

Cost. Closing. Cancel. Then all these jobs start to execute (up to a batch server maximum number of thread at a time), thus brings significant (like 5- 1.

I decided that SQL server generates silly execution plans sometimes and prescribed to run update statistics on invent. Trans/invent. Settlement/prod. Table. Jour before running IC cancelation. For a while  this well known home remedy against spontaneous slowness of queries helped to resolve the issue. Next I ran SQL Profiler, I also found out that the system creates several deadlock in a second. Finally I started to check the list of running queries and resources they are waiting for. I found out, that all processes was waiting on newly inserted key into invent.

Settlement. Date. Voucher. Idx or prod. Table. Jour. date. Voucher. Idx. I drew a conclusion that they are waiting on new key, because when I tried to select data from the tables using %%lockres%% pseudo- column, I was unable to find record being locked).

I made assumption that a transaction insert record, then everyone wait on it and then transaction is getting rolled- back because of deadlock detection. Meaning of IC cancellation is quite simple: It simply insert exact copy of closing records, but with reversed sign. Since insert. Since this insert and update are made in 1 transaction, no other user can read our half- cooked records with un- inverted sign. From application logic point of view, since, every helper thread is processing its own item. Id or Journal. Id, no lock conflicts or deadlock can occur.

But since an update statement in SQL  (and update. But from time, SQL Server having  a bad day and it is selecting voucher+date index for both queries. In this case,1st helper thread inserts records for item A and locks the related key entries in Voucher+Date index; Then the 1st thread start to iterate over WHOLE index entries for given voucher and date (which are common for all helpers, since all invent. Settlement records of the cancellation use the same voucher and date). Sooner or later, this scan was trying to read uncommitted inserts of the second helper thread, which caused 1st thread to be locked until 2nd thread commit transaction.