Sample Header Ad - 728x90

Viewing execution plans for queries with parameters

1 vote
2 answers
1232 views
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
Asked by Ɓukasz Kastelik (1405 rep)
Mar 27, 2015, 10:13 AM
Last activity: Apr 24, 2023, 11:03 AM