Question :
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
Answer :
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.
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