1

I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.) I would like to display its execution plan and attempt to tune it by indexing or rewriting some parts of it. I cannot just copy/paste it into SSMS because there are numerous parameters of many data types. I don't even know what are the values for these parameters.

Is there a way to quickly identify the execution plan of this query? Maybe by querying some DMVs? I got the query text from monitoring software which must have done it.

SELECT 2                                 AS f1,
       T3.RECID                          AS f2,
       T4.RECID                          AS f3,
       T4.GENERALJOURNALACCOUNTENTRY     AS f4,
       T4.LEDGERDIMENSION                AS f5,
       Sum(T6.TRANSACTIONCURRENCYAMOUNT) AS f6,
       T6.TRANSACTIONCURRENCY            AS f7,
       T6.MONETARYAMOUNT                 AS f8,
       Sum(T7.ACCOUNTINGCURRENCYAMOUNT)  AS f9,
       N'aaa'                            AS DATAAREAID,
       1                                 AS RECVERSION,
       5637144576                        AS PARTITION,
       IDENTITY(bigint, 1, 1)            AS RECID
INTO   [##ax_tmp_tim99_151_7623]
FROM   SUBLEDGERJOURNALENTRY T1
       CROSS JOIN ACCOUNTINGDISTRIBUTION T2
       CROSS JOIN TAXTRANS T3
       CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T4
       CROSS JOIN ACCOUNTINGDISTRIBUTION T5
       CROSS JOIN ACCOUNTINGDISTRIBUTION T6
       CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRYDISTRIBUTION T7
WHERE  ( ( T1.PARTITION = @P1 )
         AND ( ( ( ( T1.TRANSFERID = @P2 )
                   AND ( T1.LEDGER = @P3 ) )
                 AND ( T1.TYPE <> @P4 ) )
               AND ( T1.TYPE <> @P5 ) ) )
       AND ( ( T2.PARTITION = @P6 )
             AND ( ( T2.ACCOUNTINGEVENT = T1.ACCOUNTINGEVENT )
                   AND ( ( ( T2.MONETARYAMOUNT <> @P7 )
                           AND ( T2.MONETARYAMOUNT <> @P8 ) )
                         AND ( T2.MONETARYAMOUNT <> @P9 ) ) ) )
       AND ( ( ( T3.PARTITION = @P10 )
               AND ( T3.DATAAREAID = @P11 ) )
             AND ( T3.SOURCEDOCUMENTLINE = T2.SOURCEDOCUMENTLINE ) )
       AND ( ( T4.PARTITION = @P12 )
             AND ( ( ( ( T4.SUBLEDGERJOURNALENTRY = T1.RECID )
                       AND ( T4.POSTINGTYPE <> @P13 ) )
                     AND ( T4.POSTINGTYPE <> @P14 ) )
                   AND ( T4.POSTINGTYPE <> @P15 ) ) )
       AND ( ( T5.PARTITION = @P16 )
             AND ( T5.RECID = T2.PARENTDISTRIBUTION ) )
       AND ( ( T6.PARTITION = @P17 )
             AND ( ( ( T6.SOURCEDOCUMENTLINE = T5.SOURCEDOCUMENTLINE )
                     AND ( T6.PARENTDISTRIBUTION = T5.RECID ) )
                   AND ( ( ( T6.MONETARYAMOUNT = @P18 )
                            OR ( T6.MONETARYAMOUNT = @P19 ) )
                          OR ( T6.MONETARYAMOUNT = @P20 ) ) ) )
       AND ( ( T7.PARTITION = @P21 )
             AND ( ( ( T7.SUBLEDGERJOURNALACCOUNTENTRY = T4.RECID )
                     AND ( T7.ACCOUNTINGDISTRIBUTION = T6.RECID ) )
                   AND ( ( ( T7.ACCOUNTINGCURRENCYAMOUNT >= @P22 )
                           AND ( T6.TRANSACTIONCURRENCYAMOUNT >= @P23 ) )
                          OR ( ( T7.ACCOUNTINGCURRENCYAMOUNT < @P24 )
                               AND ( T6.TRANSACTIONCURRENCYAMOUNT < @P25 ) ) ) ) )
GROUP  BY T3.RECID,
          T4.RECID,
          T4.GENERALJOURNALACCOUNTENTRY,
          T4.LEDGERDIMENSION,
          T6.TRANSACTIONCURRENCY,
          T6.MONETARYAMOUNT
ORDER  BY T3.RECID,
          T4.RECID,
          T4.GENERALJOURNALACCOUNTENTRY,
          T4.LEDGERDIMENSION,
          T6.TRANSACTIONCURRENCY,
          T6.MONETARYAMOUNT
Improve this question
7
  • 2
    I can't imagine it was really your intention to create a 7X Cartesian product!?!?
    – Aaron Bertrand
    Commented Mar 27, 2015 at 16:41
  • My eyes bleed. Allergic reaction to the redundant parentheses.
    – ypercubeᵀᴹ
    Commented Mar 27, 2015 at 16:47
  • Note to self do not use Microsoft Dynamics AX 2012. In all seriousness though you probably don't need to see the execution plan to determine what's wrong with this query. Replace your cross joins and that will alleviate the majority of your problems.
    – Zane
    Commented Mar 27, 2015 at 16:50
  • @AaronBertrand Absolutely not my idea. In fact this example is bad because it so obviously wrong. The thing is I have hundreds of queries that run slow and many of them look just as convoluted but are not that badly written. As far as what the developers told me, they don't write queries in Dynamics AX but use some kind of query builders. I would like to be able to tune these queries not only by rewriting them but maybe by proper indexing. And this is the reason why I would like to see the execution plan.
    – Łukasz Kastelik
    Commented Mar 27, 2015 at 22:47
  • 1
    Also about the funny cartesians in the query above. I have noticed that CROSS JOIN + WHERE is pretty much equivalent to INNER JOIN in terms of execution plan. Its really hard to figure out what was the intention of the artist behind all these cartesians and funny WHERE clauses.
    – Łukasz Kastelik
    Commented Mar 27, 2015 at 22:49

2 Answers 2

Reset to default
2

You can use this

 select st.text, qp.query_plan,  qs.plan_handle,
        total_worker_time/execution_count AS [Avg CPU Time]
 FROM sys.dm_exec_query_stats AS qs
    cross apply sys.dm_exec_sql_text(qs.plan_handle) st
    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

if you are using sql server 2008 and above you can configure Extended Events as well to see plans etc.

Improve this answer
1

I'm using Dynamics AX fairly extensively and you can view the estimated execution plan of this query relatively easy if you declare all your @p1..@Pn parameters like this:

declare @P1 bigint, @p2 nvarchar(10), ....
SELECT 2                                 AS f1,
   T3.RECID                          AS f2,
   T4.RECID                          AS f3,
   T4.GENERALJOURNALACCOUNTENTRY     AS f4,
   T4.LEDGERDIMENSION                AS f5,
   Sum(T6.TRANSACTIONCURRENCYAMOUNT) AS f6,
   T6.TRANSACTIONCURRENCY            AS f7,
   T6.MONETARYAMOUNT                 AS f8,
   Sum(T7.ACCOUNTINGCURRENCYAMOUNT)  AS f9,
   N'aaa'                            AS DATAAREAID,
   1                                 AS RECVERSION,
   5637144576                        AS PARTITION,
   IDENTITY(bigint, 1, 1)            AS RECID INTO   [##ax_tmp_tim99_151_7623] FROM   SUBLEDGERJOURNALENTRY T1
   CROSS JOIN ACCOUNTINGDISTRIBUTION T2
   CROSS JOIN TAXTRANS T3
   CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T4
   CROSS JOIN ACCOUNTINGDISTRIBUTION T5
   CROSS JOIN ACCOUNTINGDISTRIBUTION T6
   CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRYDISTRIBUTION T7 WHERE  ( ( T1.PARTITION = @P1 )
     AND ( ( ( ( T1.TRANSFERID = @P2 )
               AND ( T1.LEDGER = @P3 ) )
             AND ( T1.TYPE <> @P4 ) )
           AND ( T1.TYPE <> @P5 ) ) )
   AND ( ( T2.PARTITION = @P6 )
         AND ( ( T2.ACCOUNTINGEVENT = T1.ACCOUNTINGEVENT )
               AND ( ( ( T2.MONETARYAMOUNT <> @P7 )
                       AND ( T2.MONETARYAMOUNT <> @P8 ) )
                     AND ( T2.MONETARYAMOUNT <> @P9 ) ) ) )
   AND ( ( ( T3.PARTITION = @P10 )
           AND ( T3.DATAAREAID = @P11 ) )
         AND ( T3.SOURCEDOCUMENTLINE = T2.SOURCEDOCUMENTLINE ) )
   AND ( ( T4.PARTITION = @P12 )
         AND ( ( ( ( T4.SUBLEDGERJOURNALENTRY = T1.RECID )
                   AND ( T4.POSTINGTYPE <> @P13 ) )
                 AND ( T4.POSTINGTYPE <> @P14 ) )
               AND ( T4.POSTINGTYPE <> @P15 ) ) )
   AND ( ( T5.PARTITION = @P16 )
         AND ( T5.RECID = T2.PARENTDISTRIBUTION ) )
   AND ( ( T6.PARTITION = @P17 )
         AND ( ( ( T6.SOURCEDOCUMENTLINE = T5.SOURCEDOCUMENTLINE )
                 AND ( T6.PARENTDISTRIBUTION = T5.RECID ) )
               AND ( ( ( T6.MONETARYAMOUNT = @P18 )
                        OR ( T6.MONETARYAMOUNT = @P19 ) )
                      OR ( T6.MONETARYAMOUNT = @P20 ) ) ) )
   AND ( ( T7.PARTITION = @P21 )
         AND ( ( ( T7.SUBLEDGERJOURNALACCOUNTENTRY = T4.RECID )
                 AND ( T7.ACCOUNTINGDISTRIBUTION = T6.RECID ) )
               AND ( ( ( T7.ACCOUNTINGCURRENCYAMOUNT >= @P22 )
                       AND ( T6.TRANSACTIONCURRENCYAMOUNT >= @P23 ) )
                      OR ( ( T7.ACCOUNTINGCURRENCYAMOUNT < @P24 )
                           AND ( T6.TRANSACTIONCURRENCYAMOUNT < @P25 ) ) ) ) ) GROUP  BY T3.RECID,
      T4.RECID,
      T4.GENERALJOURNALACCOUNTENTRY,
      T4.LEDGERDIMENSION,
      T6.TRANSACTIONCURRENCY,
      T6.MONETARYAMOUNT ORDER  BY T3.RECID,
      T4.RECID,
      T4.GENERALJOURNALACCOUNTENTRY,
      T4.LEDGERDIMENSION,
      T6.TRANSACTIONCURRENCY,
      T6.MONETARYAMOUNT

@Aaron, this isn't effectively making a cartesian product because the cross join is limited by the where clause

  AND ( ( T2.ACCOUNTINGEVENT = T1.ACCOUNTINGEVENT )

which turns it in a regular inner join. I have absolutely no clue why they do it like that.

If I can give you one advice, get a look at the DynamicsPerf tool which is documented on the axinthefield PFE blog. This will log your querys with the compiled values and execution plans on a regular basis. I found the overhead to be fairly low and the information you get from it more than worth it.

EDIT:

If you can reproduce the issue in a test environment (because AX is fairly prone to parameter sniffing) you can use the built-in SQL Statement trace log to find the call stack for the query. If you add a forceliterals there AX will generate a query without parameters but with values. This will give you the real query in the trace and can possibly help with parameter sniffing issues.

You can also look into the Trace Parser tool to capture traces with the actual parameter values and the code trace up to the query. That can help a lot too.

EDIT2:

This doesn't help you a lot with the dba side but could get you some armor in a discussion with a dev.

What you see with the temp table is a table which is declared as "temporary type tempdb" in the AX development environment. The query generating this is probably some sort of construct with a set based insert_recordset into such temp table using a query

EDIT3:

Thinking some more about this query, it's building a temp table with a lot of your ledger transactions, if this is a huge dataset you should look into tempdb contention. Using this query from Jimmy May

SELECT [Drive],
    CASE 
        WHEN num_of_reads = 0 THEN 0 
        ELSE (io_stall_read_ms/num_of_reads) 
    END AS [Read Latency],
    CASE 
        WHEN io_stall_write_ms = 0 THEN 0 
        ELSE (io_stall_write_ms/num_of_writes) 
    END AS [Write Latency],
    CASE 
        WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
        ELSE (io_stall/(num_of_reads + num_of_writes)) 
    END AS [Overall Latency],
    CASE 
        WHEN num_of_reads = 0 THEN 0 
        ELSE (num_of_bytes_read/num_of_reads) 
    END AS [Avg Bytes/Read],
    CASE 
        WHEN io_stall_write_ms = 0 THEN 0 
        ELSE (num_of_bytes_written/num_of_writes) 
    END AS [Avg Bytes/Write],
    CASE 
        WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
        ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) 
    END AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
             SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
             SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
             SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
      GROUP BY LEFT(UPPER(mf.physical_name), 2)) AS tab
ORDER BY [Overall Latency] OPTION (RECOMPILE);

Make sure the latency on your tempdb drive isn't too high and else increase the number of tempdb files if you only have one

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.