Sql-Server

SentryOne Plan Explorer 是否計算 UDF 中的讀取?

  • May 7, 2020

我有一個這樣的查詢:

select dbo.fn_complexFunction(t.id)
from mytable t

SQL Sentry Plan Explorer中,我注意到我必須執行 Get Estimated Plan 以使查詢計劃包含 UDF。

執行“獲取實際計劃”時,邏輯讀取和其他指標似乎不包括 UDF 中發生的操作。在這種情況下,使用 Profiler 是唯一的解決方法嗎?

我們在這裡遇到的最大問題是:

  1. 就像@JNK 所說,SQL Server 混淆了 UDF 的使用,並且無論如何都會用它們做可怕的事情(就像總是估計一行一樣)。當您在 SSMS 中生成實際計劃時,您也根本看不到它的用途。計劃資源管理器受到相同的限制,因為它只能提供有關 SQL Server 提供的計劃的資訊。
  2. 在生成實際計劃時,程式碼依賴於執行時指標的不同來源。不幸的是,計劃 XML 不包括函式呼叫,並且 SQL Server 沒有顯示函式在使用SET STATISTICS IO ON;其中任何一個時產生的 I/O(這是Table I/O填充選項卡的方式)。

考慮針對 AdventureWorks2012 的以下視圖和函式。給定標題表中的隨機行,這只是從詳細表中返回隨機行的愚蠢嘗試——主要是為了確保我們每次都生成盡可能多的 I/O。

CREATE VIEW dbo.myview 
WITH SCHEMABINDING
AS
 SELECT TOP (100000) rowguid, SalesOrderID, n = NEWID() 
   FROM Sales.SalesOrderDetail ORDER BY NEWID();
GO

CREATE FUNCTION dbo.whatever(@SalesOrderID INT)
RETURNS UNIQUEIDENTIFIER
WITH SCHEMABINDING
AS
BEGIN
 RETURN 
 (
   SELECT TOP (1) rowguid FROM dbo.myview 
    WHERE SalesOrderID = @SalesOrderID ORDER BY n
 );
END
GO

Management Studio 會(和不會)告訴您什麼

在 SSMS 中進行以下查詢:

SET STATISTICS IO ON;

 SELECT TOP (5) SalesOrderID, dbo.whatever(SalesOrderID) 
   FROM Sales.SalesOrderHeader ORDER BY NEWID();

SET STATISTICS IO OFF;

當你估計一個計劃時,你會得到一個查詢計劃一個函式計劃(不是 5,你可能希望):

Management Studio 中的估計計劃

顯然,您根本沒有得到任何 I/O 數據,因為查詢實際上並沒有被執行。現在,生成一個實際的計劃。您將在結果網格中獲得預期的 5 行,即以下計劃(絕對沒有提到 UDF,除了在 XML 中,您可以將其作為查詢文本的一部分和標量運算符的一部分找到):

Management Studio 中的實際計劃

以下輸出(儘管我們知道它必須從該表中讀取,但STATISTICS IO絕對沒有提及):Sales.SalesOrderDetail

表’SalesOrderHeader’。掃描計數 1,邏輯讀取 57,物理讀取 0,預讀讀取 0,lob 邏輯讀取 0,lob 物理讀取 0,lob 預讀讀取 0。

計劃探索者告訴你什麼

當 PE 為同一查詢生成估計計劃時,它知道的事情與 SSMS 相同。然而,它確實以稍微更直覺的方式顯示了一些東西。例如,外部查詢的估計計劃顯示了函式的輸出如何與查詢的輸出相結合,並且在單個計劃圖中可以立即清楚地知道兩個表都有 I/O

計劃資源管理器中的估計計劃

它還單獨顯示了函式的計劃,我只是為了完整性而將其包括在內:

計劃資源管理器中 UDF 的估計計劃

現在,讓我們看一個實際的計劃,它的用處要大幾千倍。這裡的缺點是,同樣,它只有 SQL Server 決定顯示的資訊,因此它只能顯示 SQL Server 提供的圖形計劃圖。這不是某人決定不向您展示有用的東西的情況。它只是根據提供的計劃 XML 對它一無所知。在這種情況下,就像在 SSMS 中一樣,您只能看到外部查詢的計劃,就好像根本沒有呼叫該函式

計劃資源管理器中的實際計劃

Table I/O 選項卡也仍然依賴於 的輸出STATISTICS IO,它也忽略了函式呼叫中執行的任何活動:

Plan Explorer 中實際計劃的表 I/O

但是,PE 會為您獲取整個呼叫堆棧。我偶爾會聽到有人問,“Pffft,我什麼時候需要呼叫堆棧?” 好吧,您實際上可以分解每個函式呼叫所花費的時間、使用的 CPU 和讀取次數(以及對於 TVF,生成的行數) :

計劃資源管理器中的呼叫堆棧,顯示 UDF 呼叫

不幸的是,您無法將其關聯回 I/O 來自哪個表(同樣,因為 SQL Server 不提供該資訊),並且它沒有標有 UDF 名稱(因為它被擷取為臨時語句,而不是函式呼叫本身)。但它確實讓您看到,Management Studio 沒有看到的是您的 UDF 是什麼狗。您仍然需要連接一些點,但是點更少並且它們更靠近。

關於探查器

最後,我強烈建議遠離 Profiler,除非它是設置一個伺服器端跟踪,您將編寫腳本然後在任何 UI 工具的範圍之外執行。對生產系統使用 Profiler 幾乎肯定會導致比它所解決的問題更多的問題。如果您想獲取此資訊,請使用伺服器端跟踪或擴展事件,並確保非常明智地進行過濾。即使沒有分析器,跟踪也會影響您的伺服器,通過擴展事件檢索顯示計劃也不是世界上最有效的事情

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