Who is executing this particular query?

Question

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

I think it's one of the `Management Reporter` services. Look on the mgmt reporter server and you'll see two services running that start with `Management...`. Not sure how you'd modify this. I think it gets the users/roles and ledger data. — Alex Kwitny, Sep 28 '16 at 20:35

score 4 · Accepted Answer · answered Sep 30 '16 at 11:08

4

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.

answered Sep 30 '16 at 11:08

Tom V

1,498
18
24

1

I've narrowed it down to the middle query of the three, and the problematic statement seems to be the subquery in the last join clause = `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`. If I remove the "hash" hint, then the overall query completes in sub seconds. – Greg Oct 06 '16 at 13:02
We have this same issue on SQL Server 2012. As with @Greg, I found that removing the hash join hint solves the problem. However, it isn't our code to change, it is in Microsoft's application. – Mark Freeman Mar 30 '17 at 16:13
2

@MarkFreeman You can raise a support ticket with them, but if you know how to fix it you could fix it yourself while waiting for a new Cumulative Update – Tom V Mar 30 '17 at 19:48
1

Hard to imagine removing a hash join hint would break the code in any way at all. – Hannah Vernon Mar 30 '17 at 19:51
1

This product is "owned" by a different (and very conservative) group, so it's a political problem as much as a technical one. I'm pushing them to agree to apply CU14 that allegedly addresses this issue and I am advocating that we modify the schedule to run this statement hourly instead of every 5 minutes to at least reduce the pain until they apply the CU. – Mark Freeman Mar 31 '17 at 15:07
I found this to be potentially helpful, although there are some minor typos in the code: https://translate.googleusercontent.com/translate_c?depth=1&hl=en&prev=search&rurl=translate.google.com&sl=fr&sp=nmt4&u=http://www.mathdax.ca/2015/10/dynamics-ax-2012-management-reporter.html&usg=ALkJrhjIM6H888OqZvGcwMc_VWU15bQwrA – Mark Freeman Mar 31 '17 at 15:07

Who is executing this particular query?

1 Answers1