axapta - 谁在执行这个特定的查询？

Question

正在寻找有关以下查询来源的帮助。此查询对我们的 DynamicsAX 数据库每 5 分钟执行一次，平均执行时间为 25 秒。我知道它来 self 们的 SSRS 服务器，它也托管我们的 ManagementReporter 数据库，我找不到可以执行它的工作，因此我怀疑它来自某种应用程序。

我想看看是否可以提高性能。从统计数据来看，modelSecurityRole 和 SubRole 是重中之重。

假设这不是用户生成的查询，这个执行时间是类(class)的标准时间，还是我们可以优化的？

IO 统计:

...
Table 'Workfile'. Scan count 16, logical reads 20288, physical reads 2516, read-ahead reads 17772, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ModelSecurityRole'. Scan count 805305, logical reads 2316945, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ModelSecuritySubRole'. Scan count 56649, logical reads 1501880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
...

查询:

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]..USERINFO UI
        inner join [MicrosoftDynamicsAX]..SECURITYUSERROLE sur on UI.ID = sur.USER_ and UI.PARTITION = sur.PARTITION
        inner join [MicrosoftDynamicsAX_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]..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_Model]..SECURITYROLETASKGRANT T1
                    CROSS JOIN [MicrosoftDynamicsAX_Model]..SECURITYROLEEXPLODEDGRAPH T2
                    WHERE (T1.SECURITYROLE = T2.SECURITYSUBROLE)
                    GROUP BY T1.SECURITYTASK
                        ,T2.SECURITYROLE) v on v.SECURITYROLE = sr.RECID 
        inner join [MicrosoftDynamicsAX_Model]..SECURITYTASKEXPLODEDGRAPH g on g.SECURITYTASK = v.SECURITYTASK
        inner join [MicrosoftDynamicsAX_Model]..SECURITYTASK st on g.SECURITYSUBTASK = st.RECID
        inner join (Select l.RECID, l.PARTITION, CI.DATAAREA from [MicrosoftDynamicsAX]..LEDGER l 
            inner hash join [MicrosoftDynamicsAX]..DIRPARTYTABLE CI on CI.PARTITION = l.PARTITION 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]..USERINFO UI
        inner join [MicrosoftDynamicsAX]..SECURITYUSERROLE sur on UI.ID = sur.USER_ and UI.PARTITION = sur.PARTITION
        inner join [MicrosoftDynamicsAX_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_Model]..SECURITYROLETASKGRANT T1
                    CROSS JOIN [MicrosoftDynamicsAX_Model]..SECURITYROLEEXPLODEDGRAPH T2
                    WHERE (T1.SECURITYROLE = T2.SECURITYSUBROLE)
                    GROUP BY T1.SECURITYTASK
                        ,T2.SECURITYROLE) v on v.SECURITYROLE = sr.RECID  
        inner join [MicrosoftDynamicsAX_Model]..SECURITYTASKEXPLODEDGRAPH g on g.SECURITYTASK = v.SECURITYTASK
        inner join [MicrosoftDynamicsAX_Model]..SECURITYTASK st on g.SECURITYSUBTASK = st.RECID
        inner join (Select l.RECID, l.PARTITION from [MicrosoftDynamicsAX]..LEDGER l 
            inner hash join [MicrosoftDynamicsAX]..DIRPARTYTABLE CI on CI.PARTITION = l.PARTITION 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]..USERINFO UI
        inner join [MicrosoftDynamicsAX]..SECURITYUSERROLE sur on UI.ID = sur.USER_ and UI.PARTITION = sur.PARTITION
        inner join [MicrosoftDynamicsAX_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]..LEDGER l 
            inner hash join [MicrosoftDynamicsAX]..DIRPARTYTABLE CI on CI.PARTITION = l.PARTITION 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

我正在使用最新版本的 SQL 2012

Answer 1

这些查询是从 Management Reporter 生成的，您无法更改它们。您可以在 AOT 中为 [MicrosoftDynamicsAX] 数据库中的表添加索引，但不能为 [MicrosoftDynamicsAX_Model] 数据库中的表添加索引。

这意味着您的调优选项非常有限，而且我不止一次看到此查询需要花费数秒的时间，但 IO 量对我来说似乎不合适。

虽然我想到了一些事情:

确保您的统计数据是最新的，并且您的索引也针对模型数据库进行了碎片整理。您可以使用定期维护计划、SQL 语句或 Optimize-AXModelStore 来执行此操作.

确保您的 Management Reporter 是最新的 CU(Management Reporter 有它自己的 CU，与 AX 分开)，他们的修复并不总是记录得很好，但他们可能已经更改了这个查询。

您没有使用 SQL 2014，但无论如何我都会在此处添加此信息以供将来的读者使用。我曾多次看到此查询在 SQL 2014 的 new Cardinality Estimator 中出现异常.因此，对于使用 SQL 2014 的人，请确保您的 SQL Server 已更新到最新的 CU，因为自 RTM 版本以来已发布了许多修复程序。如果您仍然无法让查询在 SQL 2014 上运行(这对我来说已经发生过几次)，您可以通过创建计划指南添加 OPTION(QUERYTRACEON 9481) 来恢复此查询以使用旧的基数估计器 像这样:

EXEC sp_create_plan_guide @name = N'[Management Reporter guide]', @stmt = N'<the exact text of the query, whitespace and linebreaks included>', @hints = N' OPTION(QUERYTRACEON 9481)'
GO

虽然 2014 年问题的症状不是您描述的那样，但它运行了几分钟并消耗了大量 CPU。