Encore une fois, selon Dynamics Perf, la requête la plus "coûteuse" de mon environnement Dynamics AX provient de Management Reporter. Et encore une fois, c'est la requête responsable de l'intégration de la sécurité entre Management Reporter et Dynamics AX.
Lorsque j'exécute la requête manuellement, elle prend 14 heures à compléter et me retourne 14000 lignes. Pendant les 14 heures d'exécution, le CPU du serveur SQL tourne à l'entour de 25%. Cette requête ne posait pas problème il y a deux semaines, qu'est-ce qui a changé ? J'ai découvert que les permissions à certains utilisateurs ont été modifiées. En effet, chaque rôle assigné à un utilisateur a été modifié afin d'être appliqué à certaines entités légales au lieu de toutes les entités légales.
Bien sûr, le client ne veut pas changer les permissions en place, j'ai donc ouvert un billet avec Microsoft. On m'a dit que c'est un bogue connu:
Bug 3794820: AX DM Companies to Company user integration query slow due to HASH join
Quoique la requête ne soit pas optimale dans mon cas, Microsoft n'a pas l'intention de corriger le problème puisque la requête offre une bonne performance dans une majorité des cas. Heureusement, une autre solution s'est offerte, celle d'activer le SQL Trace Flag 4199.
Suite à l'activation du flag 4199, la requête prend 35 min à exécuter au lieu de 14 heures. N'oubliez pas que cette tâche est configurée pour être exécuté à toutes les 5 minutes par Management Reporter, je recommande de modifier la fréquence à 24 heures ici : DynamicsPerf : Requêtes Longues (Long Queries)
Voici la requête en question:
select T.USERKEY, T.NAME, T.ALIAS, T.DOMAIN, T.SECURITYID, MAX(T.GENERALLEDGERROLETYPE) GENERALLEDGERROLETYPE, T.COMPANYKEY, T.ISENABLED
from (
select UI.RECID USERKEY, UI.NAME, UI.NETWORKALIAS ALIAS, UI.NETWORKDOMAIN DOMAIN, SID SECURITYID,
CASE st.AOTNAME
WHEN 'SysSecSecurityMaintain' THEN 5
WHEN 'LedgerBalanceSheetDimMaintain' THEN 4
WHEN 'LedgerFinancialJournalReportBGenerate' THEN 3
WHEN 'LedgerBalanceSheetDimPrintGenerate' THEN 3
WHEN 'LedgerViewFinancialStatement' THEN 2
END GENERALLEDGERROLETYPE, l.RECID COMPANYKEY, UI.ENABLE ISENABLED
from [MicrosoftDynamicsAX_TESSIER]..USERINFO UI
inner join [MicrosoftDynamicsAX_TESSIER]..SECURITYUSERROLE sur on UI.ID = sur.USER_ and UI.PARTITION = sur.PARTITION
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLE sr on sur.SECURITYROLE = sr.RECID
and (GETUTCDATE() between sur.VALIDFROM and sur.VALIDTO OR
(sur.VALIDFROM = '1/1/1900' and sur.VALIDTO = '1/1/1900'))
inner join [MicrosoftDynamicsAX_TESSIER]..SECURITYUSERROLECONDITION c on c.SECURITYUSERROLE = sur.RECID and c.PARTITION = sur.PARTITION
inner join (SELECT T1.SECURITYTASK AS SECURITYTASK
,T2.SECURITYROLE AS SECURITYROLE
FROM [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLETASKGRANT T1
CROSS JOIN [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLEEXPLODEDGRAPH T2
WHERE (T1.SECURITYROLE = T2.SECURITYSUBROLE)
GROUP BY T1.SECURITYTASK
,T2.SECURITYROLE) v on v.SECURITYROLE = sr.RECID
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYTASKEXPLODEDGRAPH g on g.SECURITYTASK = v.SECURITYTASK
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYTASK st on g.SECURITYSUBTASK = st.RECID
inner join (Select l.RECID, l.PARTITION, CI.DATAAREA from [MicrosoftDynamicsAX_TESSIER]..LEDGER l
inner hash join [MicrosoftDynamicsAX_TESSIER]..DIRPARTYTABLE CI on CI.PARTITION = l.PARTITION and CI.INSTANCERELATIONTYPE = 41 and l.PRIMARYFORLEGALENTITY = CI.RECID) l on UI.PARTITION = l.PARTITION and l.DATAAREA = c.DATAAREA
Where
UI.EXTERNALUSER = 0 AND
UI.[SID] != '' AND
UI.[ACCOUNTTYPE] = 0 AND
sur.ASSIGNMENTSTATUS = 1 AND
st.AOTNAME in (
'SysSecSecurityMaintain',
'LedgerBalanceSheetDimMaintain',
'LedgerFinancialJournalReportBGenerate',
'LedgerBalanceSheetDimPrintGenerate',
'LedgerViewFinancialStatement')
union all
-- get users and their assigned tasks for all companies where the task hasn't been constrained to a company
select UI.RECID USERKEY, UI.NAME, UI.NETWORKALIAS ALIAS, UI.NETWORKDOMAIN DOMAIN, SID SECURITYID,
CASE st.AOTNAME
WHEN 'SysSecSecurityMaintain' THEN 5
WHEN 'LedgerBalanceSheetDimMaintain' THEN 4
WHEN 'LedgerFinancialJournalReportBGenerate' THEN 3
WHEN 'LedgerBalanceSheetDimPrintGenerate' THEN 3
WHEN 'LedgerViewFinancialStatement' THEN 2
END GENERALLEDGERROLETYPE, l.RECID COMPANYKEY, UI.ENABLE ISENABLED
from [MicrosoftDynamicsAX_TESSIER]..USERINFO UI
inner join [MicrosoftDynamicsAX_TESSIER]..SECURITYUSERROLE sur on UI.ID = sur.USER_ and UI.PARTITION = sur.PARTITION
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLE sr on sur.SECURITYROLE = sr.RECID
and (GETUTCDATE() between sur.VALIDFROM and sur.VALIDTO OR
(sur.VALIDFROM = '1/1/1900' and sur.VALIDTO = '1/1/1900'))
inner join (SELECT T1.SECURITYTASK AS SECURITYTASK
,T2.SECURITYROLE AS SECURITYROLE
FROM [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLETASKGRANT T1
CROSS JOIN [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLEEXPLODEDGRAPH T2
WHERE (T1.SECURITYROLE = T2.SECURITYSUBROLE)
GROUP BY T1.SECURITYTASK
,T2.SECURITYROLE) v on v.SECURITYROLE = sr.RECID
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYTASKEXPLODEDGRAPH g on g.SECURITYTASK = v.SECURITYTASK
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYTASK st on g.SECURITYSUBTASK = st.RECID
inner join (Select l.RECID, l.PARTITION from [MicrosoftDynamicsAX_TESSIER]..LEDGER l
inner hash join [MicrosoftDynamicsAX_TESSIER]..DIRPARTYTABLE CI on CI.PARTITION = l.PARTITION and CI.INSTANCERELATIONTYPE = 41 and l.PRIMARYFORLEGALENTITY = CI.RECID) l on UI.PARTITION = l.PARTITION
Where
UI.EXTERNALUSER = 0 AND
UI.[SID] != '' AND
UI.[ACCOUNTTYPE] = 0 AND
sur.ASSIGNMENTSTATUS = 1 AND
st.AOTNAME in (
'LedgerBalanceSheetDimMaintain',
'LedgerFinancialJournalReportBGenerate',
'LedgerBalanceSheetDimPrintGenerate',
'LedgerViewFinancialStatement',
'SysSecSecurityMaintain')
and not exists (select 1 from SECURITYUSERROLECONDITION c where c.SECURITYUSERROLE = sur.RECID and c.PARTITION = sur.PARTITION)
union all
-- get all administrators for all companies where the admin's aren't limited to specific companies
select UI.RECID, UI.NAME, UI.NETWORKALIAS, UI.NETWORKDOMAIN, SID, 5 RoleType, l.RECID, UI.ENABLE ISENABLED
from [MicrosoftDynamicsAX_TESSIER]..USERINFO UI
inner join [MicrosoftDynamicsAX_TESSIER]..SECURITYUSERROLE sur on UI.ID = sur.USER_ and UI.PARTITION = sur.PARTITION
inner join [MicrosoftDynamicsAX_TESSIER_Model]..SECURITYROLE sr on sr.RECID = sur.SECURITYROLE
and (GETUTCDATE() between sur.VALIDFROM and sur.VALIDTO OR
(sur.VALIDFROM = '1/1/1900' and sur.VALIDTO = '1/1/1900'))
inner join (Select l.RECID, l.PARTITION from [MicrosoftDynamicsAX_TESSIER]..LEDGER l
inner hash join [MicrosoftDynamicsAX_TESSIER]..DIRPARTYTABLE CI on CI.PARTITION = l.PARTITION and CI.INSTANCERELATIONTYPE = 41 and l.PRIMARYFORLEGALENTITY = CI.RECID) l on UI.PARTITION = l.PARTITION
where
UI.EXTERNALUSER = 0 AND
UI.[SID] != '' AND
UI.[ACCOUNTTYPE] = 0 AND
sur.ASSIGNMENTSTATUS = 1 AND
AOTNAME in ('SysSecSecurityAdministrator')
)
T
Group by T.USERKEY, T.NAME, T.ALIAS, T.DOMAIN, T.SECURITYID, T.COMPANYKEY, T.ISENABLED
order by T.COMPANYKEY