vendredi 27 mars 2015

Viewing execution plans for queries with parameters


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




Aucun commentaire:

Enregistrer un commentaire