Sql-Server

查看帶有參數的查詢的執行計劃

  • May 7, 2021

我有一個由應用程序（在本例中為 Microsoft Dynamics AX 2012）生成的範例查詢，它在性能方面無效（交叉連接、排序等）我想顯示它的執行計劃並嘗試通過索引或重寫來調整它它的某些部分。我不能只是將它複製/粘貼到 SSMS 中，因為有許多數據類型的參數。我什至不知道這些參數的值是什麼。

有沒有辦法快速辨識這個查詢的執行計劃？也許通過查詢一些DMV？我從監控軟體那裡得到了查詢文本，它一定已經完成了。

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

你可以用這個

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

如果您使用的是 sql server 2008 及更高版本，您還可以配置擴展事件以查看計劃等。

我相當廣泛地使用 Dynamics AX，如果您像這樣聲明所有 @p1..@Pn 參數，您可以相對容易地查看此查詢的估計執行計劃：

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，這並不能有效地製作笛卡爾積，因為交叉連接受 where 子句的限制

 AND ( ( T2.ACCOUNTINGEVENT = T1.ACCOUNTINGEVENT )

這使它變成了一個正常的內部連接。我完全不知道他們為什麼這樣做。

如果我能給您一個建議，請查看axinthefield PFE 部落格上記錄的DynamicsPerf工具。這將定期使用編譯值和執行計劃記錄您的查詢。我發現成本相當低，而且您從中獲得的資訊非常值得。

編輯：

如果您可以在測試環境中重現該問題（因為 AX 很容易進行參數嗅探），您可以使用內置的SQL 語句跟踪日誌來查找查詢的呼叫堆棧。如果您添加一個 forceliterals，則 AX 將生成一個沒有參數但有值的查詢。這將為您提供跟踪中的真實查詢，並可能有助於解決參數嗅探問題。

您還可以查看Trace Parser 工具，以使用實際參數值和查詢的程式碼跟踪來擷取跟踪。這也有很大幫助。

編輯2：

這在 dba 方面對您沒有多大幫助，但可以在與開發人員的討論中為您提供一些盔甲。

您在臨時表中看到的是在 AX 開發環境中聲明為“臨時類型 tempdb”的表。生成此查詢的查詢可能是某種構造，其集合基於insert_recordset使用查詢的此類臨時表

編輯3：

對這個查詢進行更多思考，它正在建構一個包含大量分類帳事務的臨時表，如果這是一個巨大的數據集，您應該查看 tempdb 爭用。使用 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);

確保您的 tempdb 驅動器上的延遲不會太高，如果您只有一個，請增加 tempdb 文件的數量

引用自：https://dba.stackexchange.com/questions/96436

相關問答

Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

如果 CTE 在查詢中定義並且從未使用過，它會發出聲音嗎？

  • December 3, 2021
檢視問答
Sql-Server

TOP 如何（以及為什麼）影響執行計劃？

  • November 27, 2021
檢視問答
Sql-Server

非聚集索引更新未顯示在執行計劃中

  • August 12, 2021
檢視問答
Sql-Server

為什麼我的查詢在環境 A 中執行得很快，而在環境 B 中卻很慢？

  • June 30, 2021
檢視問答
Sql-Server

參數嗅探即使在禁用後也會發生嗎？

  • June 15, 2021
檢視問答