Hello everyone,
We are experimenting constant slowdowns in Management Reporter; it is not the reports themselves that are slow but the delay after which the datamart gets the updated data from AX. It went from a couple of minutes to several hours. To the point where last Friday we had to turn off Management Reporter services as it was preventing our AX aos from being restarted. It seems like several locks were hold in the AX database (not the datamart, the AX production database!) from the Management Reporter scheduled tasks.
Our servers, either the SQL machine or the aos, don’t seem to be too intensely sollicitated. There’s no sign of peak usage when looking at the performance.
The Management Reporter services resides on one of our aos server.
The Management Reporter SQL server is the same as the AX SQL Server.
There is one extremely slow process in the M.R. scheduled tasks, General Ledger Transactions To Fact. It can run for several hours with a slow increase in its estimated progress status. We are having this issue even with our PRE-PROD environment. The following stats are actually pulled from PRE-PROD.
This query shows performance of the scheduled task for M.R.:
SELECT CIG.[Description]
, T.Name
, CASE TS.StateType
WHEN 3 THEN 'PROCESSING'
WHEN 5 THEN 'COMPLETE'
WHEN 7 THEN 'ERROR'
ELSE Convert(varchar(max),TS.StateType)
END AS StateType
, TS.Progress
, TR.Id AS TriggerId
, CASE TR.IsEnabled
WHEN 1 THEN 'ENABLED'
WHEN 0 THEN 'DISABLED'
ELSE Convert(varchar(max),TR.IsEnabled)
END AS TriggerStatus
, TR.Interval
, CASE TR.UnitOfMeasure
WHEN 4 THEN 'DAYS'
WHEN 3 THEN 'HOURS'
WHEN 2 THEN 'MINUTES'
WHEN 1 THEN 'SECONDS'
ELSE Convert(varchar(max),TR.UnitOfMeasure)
END AS IntervalTiming
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), TS.[LastRunTime]) as LocalLastRunTime
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), TS.[NextRunTime]) as LocalNextRunTime
--, TS.[LastRunTime] as UTCLastRunTime
--, TS.[NextRunTime] as UTCNextRunTime
FROM Scheduling.Task T with (nolock)
JOIN Scheduling.TaskState TS with (nolock) ON T.Id = TS.TaskId
JOIN Scheduling.[Trigger] TR with (nolock) ON TR.Id = T.TriggerId
JOIN Connector.IntegrationGroup CIG with (nolock) on CIG.[IntegrationId] = T.CategoryId
WHERE T.TypeId in ('55D3F71A-2618-4EAE-9AA6-D48767B974D8', '6F6B935B-FC0A-46B9-8F53-27C6AF7437F0', 'D81C1197-D486-4FB7-AF8C-078C110893A0')
ORDER BY CIG.[Description], T.Name
This is the result from PRE-PROD on Friday around 5pm:
General Ledger Transactions To Fact process was completed at 94.73% and it was started since 12h20 by looking at the LocalLastRunTime column.
Upon looking at the stats for General Ledger Transactions To Fact since I reloaded the PRE-PROD datamart on Thursday, we see that the initial load took 6630 seconds to insert more then 25 millions of rows and delete 259643 rows. But when checking the last three synchronization attempt that we see a major down in performance… notablt taking more than 10 hours (36531 seconds) to write 117175 rows and delete 177102.
select * from Scheduling.MessageSummary
where TaskName='AX 2012 General Ledger Transactions to Fact'
order by Time DESC
What that can we do to fix this problem^ It has come to the point where we are starting to look for a better alternative than M.R. With AX 2009, we had none of this problem as the report was reading directly into AX db; but the datamart and its synchronization architecture seems like a flawed and cumbersome approach.
Thanks in advance for any hints or help,
Eric
