I have been silent for a long time due mostly to being overworked, something I am sure we are all familiar with :-).
Anyhow I have just completed a process whereby we have migrated our current core application to Ax 2009, it was less of a positive experience than I had hoped.
During the upgrade touching the few objects that we had changed in our developments in Ax I noted some pretty silly little details and I also noted that one of my favourite performance bugbears which ostensibly had been fixed had not really been adressed.
I noted that during the upgrade process in the ReleaseUpdate Classes the old customer return codes are being mapped accross to a new table ReturnDispositionCode and in the process the return location is stripped from the data, and horror of horrors another method then simply deletes the information.
We would not have noted this if we hade not made a modification surrounding this process to allow us to add more handling around the returns process.
The funny part of all this is that the method allowing for a setting of the inventory dimensions based on the return code still exists.
I have nothing against the new returns functionality but I cannot accept a functional regression with no warning.
A further funny is the way this return functionality has been implemented it now creates shadow lines one or more which means if you had solutions relying on totalling the order lines you will be caught out by these new "hidden" lines.
As you know if you have read some of the earlier posts I am very concerned in general with performance in the solution as I believe this is not only an issue for large installations but also for all installations in general and in particular high volume ones.
I have touched on my metric as regards the summary tables LedgerBalancesTrans and LedgerBalancesDimTrans and the fact that in most cases these so called summary tables are not really a summary and therefore financial reporting is needlessly long.
In V3 and V4 the system creates upto 10 or upto 20 entries per day per dimension combination in V5 this has been reduced to one per day per dimension with a transient (or vector) one made temporarily during the update for each stream doing an update.
This off course reduces the number of transactions but not really that significantly by dimension, as in many of my real world cases the dimensions are strongly used and therefore there were not that many transactions for any one day & dimension combination. We have gone from 80% of transaction mass in the so called summary to 50-60% which in my book is still too much.
I hold out for having one transaction / account / dimension per financial period. This would really improve summary reporting performance.
Anyhow more about this and other V5-2009 matters later.
Take Care
Sven
Showing posts with label Performance. Show all posts
Showing posts with label Performance. Show all posts
Friday, 13 March 2009
Friday, 20 June 2008
Performance Issues Resolution in Ax Part II
After finishing this work on Ax V4 Ax V2009 was rolled out and my work should really start over again.
However as I am lazy I will simply try to migrate the code and tell you more afterwards.
Noted some interesting changes in V5 as regards multi threaded execution of processes using the new batch service.
Will report back on this later in more detail.
/Sven
However as I am lazy I will simply try to migrate the code and tell you more afterwards.
Noted some interesting changes in V5 as regards multi threaded execution of processes using the new batch service.
Will report back on this later in more detail.
/Sven
Monday, 26 May 2008
In search of Performance (Finance this Time)
I have not noticed when exactly the change was made but when posting to the LedgerBalanceTrans and LedgerBalanceDimTrans the date is no longer always the first or last day of a financial period it is the equivalent of the Transaction date. Validated from V3 onwards.
This has important performance impacts on reporting when you use financial dimensions actively.
Normally you would expect to see a factor of reduction in number of transactions going from LedgerTrans to LedgerBalanceTrans and DimTrans, in practical use I see that most of the implementations I visit the relationship is more like a 10% reduction in DimTrans and 20% in BalanceTrans which is not much and almost makes these tables almost redundant.
In any case the advantage in performance using these tables rather than the LedgerTrans directly for reporting is not very big.
An easy means of seeing whether you have this issue is to configure your chart of accounts to show the current balance, if the display is jerky and slow which is the case very often it means the level of summary provided by the LedgerBalance View on the LedgerBalanceTrans is not sufficient to allow for a speedy summary of the ledgerbalances to date.
Another is to review the number of rows you have in LedgerTrans and in LedgerBalanceTrans & DimTrans respectively if the later are much smaller the optimisation is functional in your case if not you will eventually have a slower system as regards reporting.
What I would suggest is to ensure that it is possible to have a level of consolidation in the Balances so that they are reasonably fast for reporting purposes of course this means that we will slow down posting slightly however I believe the gains in reporting justify the expenditure :-).
/Sven
This has important performance impacts on reporting when you use financial dimensions actively.
Normally you would expect to see a factor of reduction in number of transactions going from LedgerTrans to LedgerBalanceTrans and DimTrans, in practical use I see that most of the implementations I visit the relationship is more like a 10% reduction in DimTrans and 20% in BalanceTrans which is not much and almost makes these tables almost redundant.
In any case the advantage in performance using these tables rather than the LedgerTrans directly for reporting is not very big.
An easy means of seeing whether you have this issue is to configure your chart of accounts to show the current balance, if the display is jerky and slow which is the case very often it means the level of summary provided by the LedgerBalance View on the LedgerBalanceTrans is not sufficient to allow for a speedy summary of the ledgerbalances to date.
Another is to review the number of rows you have in LedgerTrans and in LedgerBalanceTrans & DimTrans respectively if the later are much smaller the optimisation is functional in your case if not you will eventually have a slower system as regards reporting.
What I would suggest is to ensure that it is possible to have a level of consolidation in the Balances so that they are reasonably fast for reporting purposes of course this means that we will slow down posting slightly however I believe the gains in reporting justify the expenditure :-).
/Sven
Friday, 2 May 2008
Performance and Inventdim PII
I wrote on this subject a few months ago and a comment was made that perhaps a solution could be found by creating appropriate views and stored procedures.
I have in several instances tried the problem is the sheer volume of information that the SQL db is asked to manipulate.
Concrete example, the customer had built up a database of 24 million inventory transactions, there were 2,5 million lines in inventdim, inventsum had 6 million lines, the only active dimensions were the configuration on the inventory side and the warehouse, location and palett on the storage side. No serial numbers or Batch numbers so reasonably limited usage of inventdimid's. They used FIFO costing so inventsettlements were not too bad around 16 Million.
We ran a valuation report as at 3 months in the past. The report was unable to complete after 100 hours of processing time. The server was a quad processor with 32 Gb of RAM and a dedicated 15 K RPM raid 0+1 array with 24 disks in it, no penny pinching there.
The problem lies in the volume of data.
A consultant that helped design the above data model came and installed his solution which basically takes the data from inventtrans + Inventdim + inventsettlement and creates a flat table from the result. 12 hours of processing time. Afterwards the valuation report was printed in less than 10 minutes.
I think the above illustrates my problem with the current data model, having said all this many customers will never notice the issue as they do not have huge amounts of transactions. But as we all know over time systems grow and well this one does not have an archiving tool :-).
/Sven
I have in several instances tried the problem is the sheer volume of information that the SQL db is asked to manipulate.
Concrete example, the customer had built up a database of 24 million inventory transactions, there were 2,5 million lines in inventdim, inventsum had 6 million lines, the only active dimensions were the configuration on the inventory side and the warehouse, location and palett on the storage side. No serial numbers or Batch numbers so reasonably limited usage of inventdimid's. They used FIFO costing so inventsettlements were not too bad around 16 Million.
We ran a valuation report as at 3 months in the past. The report was unable to complete after 100 hours of processing time. The server was a quad processor with 32 Gb of RAM and a dedicated 15 K RPM raid 0+1 array with 24 disks in it, no penny pinching there.
The problem lies in the volume of data.
A consultant that helped design the above data model came and installed his solution which basically takes the data from inventtrans + Inventdim + inventsettlement and creates a flat table from the result. 12 hours of processing time. Afterwards the valuation report was printed in less than 10 minutes.
I think the above illustrates my problem with the current data model, having said all this many customers will never notice the issue as they do not have huge amounts of transactions. But as we all know over time systems grow and well this one does not have an archiving tool :-).
/Sven
Thursday, 19 July 2007
Performance issues resolution in Ax Part I
This is a subject on which a lot has been written by any number of people so I will concentrate on some small parts of the issues relating to this, and this is not meant as anything else but a starting point.
Mostly when you have problems in Ax, as in most other ERP systems :-), it is related to some sort of contention that is concurrent access to key central information, or alternatively to large quantities of data being used/created.
In most of the instances I have seen the quickest fix on SQL is always just adding more memory, not that I am a big advocate of throwing money at the problem but well that is normally the cheapest solution these days.
In SQL 2000 and 2005, the main issue is centered around one fact, lock escalation, basically your server can if it deems to be running out of memory decide to start locking not each row but a page (unit of storage used to contain multiple rows in the database) and even the whole table. When this happens you tend to get a situation where everyone is waiting for each other.
Picture Picadilly circus bus stop on a busy afternoon where you have lots of busses all coming in to pick up / drop off passengers and having to wait for each other and thereby causing havoc for other traffic, that is the best physical representation.
In Ax say the CustTrans table is concerned or InventTrans then as one process is using it another 5 different ones want to use it too, they cannot as the first is blocking and they use other tables which they then block for others thus making a traffic jam in the database.
SQL decides to do this lock escalation when it has used more than a certain percentage of the available memory that it has to manage locks, thus if you give it more memory to play with it is less likely to do lock escalation and therefore to create the traffic jam.
In SQL 2005 some new functionality has been added concerning Index Lock escalation, however this is hard to add in Ax as information on the indexes, as these are created by the syncronization engine. You would have to add the information by hand on each index :-( after creation and then redo this everytime Ax runs a syncronization.
Additionally as you can see in this KB where means of reducing the lock escalation are discussed it is possible to switch of using the startup flag -T1211 however the resulting error condition of it running out of memory is not pretty.
One option that seems intriguing is the option of making an incompatible higher level block
BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAIT FOR DELAY '1:00:00'
COMMIT TRAN
in Ax the tables classically affected are a series of well known ones, the problem with the above in Ax (as well as most other ERP's) is that one hides others etc. If f.ex. you resolve your issues on CustTrans then perhaps CustTransOpen or CustSettlement becomes the problem etc etc so this is often not a solution that we can use to resolve our escalation issues, also unless we do the locking for the whole business day we do not handle all the cases :-(.
Changing our queries is the next possibility mentioned to create smaller transactions.
Well, that is something that is possible in a limited fashion, you will have to clarify functionally with the business whether they can accept the consequences, f.ex. not make sales orders with moe than xx lines in them. Not do recap invoicing, etc etc.
What you can do is use the system to help you detect where you have problems and also use the system to dissect exactly the problem is being originated from.
A number of tools are availeable to you, the main one which I recommend all Ax sysadmins to get familiar with is the SQL tab under the User Options of each User.
This provides an invaluable help in order to get information about what parts of your system are not performing as expected.
What I suggest all sysadmins to do is to as a minimum activate the SQL tracing set a threshold of perhaps 2000 (2 secs it is in milliseconds) and to store long queries and deadlocks in the Table (database).
This will store in the database (accessible under Administration/Inquiries/Database/SQL Trace log the detail of every time a query took more than 2 seconds to execute, including the complete call stack of the process when this happened. You can even jump directly to the offending code and view / edit it.
With a little reflection it is always possible to optimise such queries and to thus reduce your performance issues, it is just a question of thinking about it clearly.
In V4 a few impediments have been placed on your way to having this level of information you have to enable Client Tracing on the AOS instance otherwise the table will be empty as the process will not be called.
Also in V3 KR1+ you have an extended toolkit that you can install, after installing the KR which you should have acces to through customersource or partnersource this allows you to control in detail what is being tracked and also allows you to log blocking behaviour, any process waiting more than 3 seconds on another will automatically log the fact that it is being blocked and thus you can see the effect that the "picadilly peak hour jam" has had on your system.
The good thing about logs is you can be factual about the increase / resolutions that you apply to your system.
Unfortunately MS have not seen fit to extend the above tracing extension into V4 probably because it was a separate development path. I have I think now managed to migrate most of it onto a V4 SP1 based system, the only issue I have is with the blocking monitor, I need to work a little on the SQL side to find out what I need to do exactly in order for it to operate.
Once it is complete I will post it here for you.
Take Care
Sven
Mostly when you have problems in Ax, as in most other ERP systems :-), it is related to some sort of contention that is concurrent access to key central information, or alternatively to large quantities of data being used/created.
In most of the instances I have seen the quickest fix on SQL is always just adding more memory, not that I am a big advocate of throwing money at the problem but well that is normally the cheapest solution these days.
In SQL 2000 and 2005, the main issue is centered around one fact, lock escalation, basically your server can if it deems to be running out of memory decide to start locking not each row but a page (unit of storage used to contain multiple rows in the database) and even the whole table. When this happens you tend to get a situation where everyone is waiting for each other.
Picture Picadilly circus bus stop on a busy afternoon where you have lots of busses all coming in to pick up / drop off passengers and having to wait for each other and thereby causing havoc for other traffic, that is the best physical representation.
In Ax say the CustTrans table is concerned or InventTrans then as one process is using it another 5 different ones want to use it too, they cannot as the first is blocking and they use other tables which they then block for others thus making a traffic jam in the database.
SQL decides to do this lock escalation when it has used more than a certain percentage of the available memory that it has to manage locks, thus if you give it more memory to play with it is less likely to do lock escalation and therefore to create the traffic jam.
In SQL 2005 some new functionality has been added concerning Index Lock escalation, however this is hard to add in Ax as information on the indexes, as these are created by the syncronization engine. You would have to add the information by hand on each index :-( after creation and then redo this everytime Ax runs a syncronization.
Additionally as you can see in this KB where means of reducing the lock escalation are discussed it is possible to switch of using the startup flag -T1211 however the resulting error condition of it running out of memory is not pretty.
One option that seems intriguing is the option of making an incompatible higher level block
BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAIT FOR DELAY '1:00:00'
COMMIT TRAN
in Ax the tables classically affected are a series of well known ones, the problem with the above in Ax (as well as most other ERP's) is that one hides others etc. If f.ex. you resolve your issues on CustTrans then perhaps CustTransOpen or CustSettlement becomes the problem etc etc so this is often not a solution that we can use to resolve our escalation issues, also unless we do the locking for the whole business day we do not handle all the cases :-(.
Changing our queries is the next possibility mentioned to create smaller transactions.
Well, that is something that is possible in a limited fashion, you will have to clarify functionally with the business whether they can accept the consequences, f.ex. not make sales orders with moe than xx lines in them. Not do recap invoicing, etc etc.
What you can do is use the system to help you detect where you have problems and also use the system to dissect exactly the problem is being originated from.
A number of tools are availeable to you, the main one which I recommend all Ax sysadmins to get familiar with is the SQL tab under the User Options of each User.
This provides an invaluable help in order to get information about what parts of your system are not performing as expected.
What I suggest all sysadmins to do is to as a minimum activate the SQL tracing set a threshold of perhaps 2000 (2 secs it is in milliseconds) and to store long queries and deadlocks in the Table (database).
This will store in the database (accessible under Administration/Inquiries/Database/SQL Trace log the detail of every time a query took more than 2 seconds to execute, including the complete call stack of the process when this happened. You can even jump directly to the offending code and view / edit it.
With a little reflection it is always possible to optimise such queries and to thus reduce your performance issues, it is just a question of thinking about it clearly.
In V4 a few impediments have been placed on your way to having this level of information you have to enable Client Tracing on the AOS instance otherwise the table will be empty as the process will not be called.
Also in V3 KR1+ you have an extended toolkit that you can install, after installing the KR which you should have acces to through customersource or partnersource this allows you to control in detail what is being tracked and also allows you to log blocking behaviour, any process waiting more than 3 seconds on another will automatically log the fact that it is being blocked and thus you can see the effect that the "picadilly peak hour jam" has had on your system.
The good thing about logs is you can be factual about the increase / resolutions that you apply to your system.
Unfortunately MS have not seen fit to extend the above tracing extension into V4 probably because it was a separate development path. I have I think now managed to migrate most of it onto a V4 SP1 based system, the only issue I have is with the blocking monitor, I need to work a little on the SQL side to find out what I need to do exactly in order for it to operate.
Once it is complete I will post it here for you.
Take Care
Sven
Subscribe to:
Posts (Atom)