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
Best Answer
You can use this
if you are using sql server 2008 and above you can configure Extended Events as well to see plans etc.