CODEWITHSUNDEEP
axaptamicrosoft-dynamicsdynamics-ax-2012
Greg
Greg

Reputation: 4045

Who is executing this particular query?

Looking for some help on the source of the below query. This query executes every 5 minutes against our DynamicsAX database, and average execution time is 25 sec. I know it's coming from our SSRS server that also hosts our ManagementReporter dbs, I I can't find a job that would be executing it, thus i suspect it's coming from some kind of app.

I'd like to see if the performance can be improved. Looking at the stats, modelSecurityRole and SubRole are the heavy hitters.

Assuming it's not a user generated query, is this execution time par for the course, or is it something we can optimize?

IO Stats:

...
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.
...

Query:

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

I'm using the latest build of SQL 2012

Upvotes: 3

Views: 810

Answers (1)

Tom V
Tom V

Reputation: 1496

Those queries are generated from Management Reporter and you can't change them. You could add indexes in the AOT for the tables that are in the [MicrosoftDynamicsAX] database but not for the tables that are in the [MicrosoftDynamicsAX_Model] database.

This means your tuning options are very limited and I've seen this query take multiple seconds on more than one occasion but the amount of IO seems off to me.

A couple of things come to mind though:

Make sure your statistics are up to date and your indexes are defragmented for the model database too. You can do this using regular maintenance plans, SQL Statements or Optimize-AXModelStore.

Make sure that your Management Reporter is up to date to the latest CU (Management reporter has it's own CU's separate from AX), Their fixes aren't always documented very well but they may have changed this query.

You're not using SQL 2014 but I'll add this information here anyway for future readers. I've seen this query misbehave on several occasions with SQL 2014's new Cardinality Estimator. So for the people using SQL 2014 make sure your SQL Server is up to date to the most recent CU as a lot of fixes have been released since the RTM version. If you still can't get the query to behave on SQL 2014 (which has happened a couple of times to me) you can revert this query to use the old cardinality estimator by creating a plan guide adding OPTION(QUERYTRACEON 9481) like this:

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

The symptoms of the 2014 issue wasn't what you describe though, It ran for minutes and consumed huge amounts of CPU.

Upvotes: 4

Related Questions