Sql-Server

帶有函式呼叫的估計查詢計劃與實際查詢計劃

  • November 14, 2019

我在 SQL 伺服器上有這個查詢,一個合併複製查詢:

SELECT DISTINCT
   b.tablenick,
   b.rowguid,
   c.generation,
   sys.fn_MSgeneration_downloadonly
   (
       c.generation,
       c.tablenick
   )
FROM #belong b
LEFT OUTER JOIN dbo.MSmerge_contents c ON 
   c.tablenick = b.tablenick
   AND c.rowguid = b.rowguid;

估計的查詢計劃包括有關 3 個查詢的資訊:

  1. 上面的查詢
  2. 對 fn_MSgeneration_downloadonly 的函式呼叫
  3. 對 fn_MSArticle_has_downloadonly_property 的函式呼叫

實際的查詢計劃僅包含以下資訊:

  1. 上面的查詢

沒有什麼關於功能的。為什麼實際計劃中缺少功能資訊?

我嘗試了這些選項:

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

它創建了一個實際計劃,但它缺少與我在 Management Studio 中使用實際查詢計劃選項時相同的第 2 部分和第 3 部分。

例如,如果我要使用 Profiler 來擷取有關函式呼叫的資訊,我會選擇哪些事件?


沒有找到與查詢計劃特別相關的答案,但我分析了 SP:StmtStarting 和 SP:StmtCompleted 並顯示了函式呼叫。

並沒有關於功能。為什麼實際計劃中缺少功能資訊?

出於性能原因,這是設計使然。

包含BEGINEND在定義中的函式為每個輸入行創建一個新的 T-SQL 堆棧框架。換句話說,函式體為每個輸入 row 單獨執行。這一事實解釋了與 T-SQL 標量和多語句函式相關的大多數性能問題(請注意,內聯表值函式不使用該BEGIN...END語法)。

在您的問題的上下文中,這將導致SHOWPLAN每一行的完整輸出。XML 計劃輸出非常冗長且生成成本很高,因此為每一行生成完整輸出一般來說是個壞主意。

例子

考慮下面的 T-SQL 標量函式,它是在AdventureWorks範例數據庫中創建的,它返回給定 ID 的產品名稱:

CREATE FUNCTION dbo.DumbNameLookup
(
   @ProductID integer
)
RETURNS dbo.Name
AS
BEGIN
   RETURN
   (
       SELECT
           p.Name
       FROM Production.Product AS p
       WHERE
           p.ProductID = @ProductID
   );
END;

執行前計劃

預執行計劃(SSMS 中的估計計劃)顯示父語句和嵌套函式呼叫的計劃資訊:

-- Pre-execution plan shows main query and nested function call
SET SHOWPLAN_XML ON;
GO
SELECT dbo.DumbNameLookup(1);
GO
SET SHOWPLAN_XML OFF;

SSMS 輸出:

SSMS預執行計劃

在SQL Sentry Plan Explorer中查看的相同 XML更清楚地顯示了呼叫的嵌套性質:

PE預執行計劃

執行後輸出

當請求執行後計劃輸出時,SSMS 僅顯示主查詢的詳細資訊:

-- Post-execution plan shows main query only
SET STATISTICS XML ON;
SELECT dbo.DumbNameLookup(1);
SET STATISTICS XML OFF;

SSMS 執行後

可以使用 SQL Server Profiler 中的Showplan XML Statistics Profile Event Class顯示不這樣做的性能影響,使用多次呼叫該函式的查詢(每個輸入行一次):

SELECT TOP (5)
   p.ProductID,
   dbo.DumbNameLookup(p.ProductID)
FROM Production.Product AS p;

探查器輸出:

跟踪輸出

函式執行有五個單獨的執行後計劃,父查詢有一個。五個功能計劃在分析器下部窗格中如下所示:

功能計劃

父查詢計劃是:

家長計劃

執行不帶該TOP (5)子句的查詢會為 Product 表中的 504 行中的每一行生成一個完整的執行計劃。您可能會看到這將如何在更大的表中迅速失控。

觸發器的情況正好相反。這些不顯示任何執行前計劃資訊,但包含執行後計劃。這反映了觸發器的基於集合的性質;每個受影響的行都會觸發一次,而不是每行一次。

引用自:https://dba.stackexchange.com/questions/29742