SentryOne Plan Explorer 是否計算 UDF 中的讀取?
我有一個這樣的查詢:
select dbo.fn_complexFunction(t.id) from mytable t
在SQL Sentry Plan Explorer中,我注意到我必須執行 Get Estimated Plan 以使查詢計劃包含 UDF。
執行“獲取實際計劃”時,邏輯讀取和其他指標似乎不包括 UDF 中發生的操作。在這種情況下,使用 Profiler 是唯一的解決方法嗎?
我們在這裡遇到的最大問題是:
- 就像@JNK 所說,SQL Server 混淆了 UDF 的使用,並且無論如何都會用它們做可怕的事情(就像總是估計一行一樣)。當您在 SSMS 中生成實際計劃時,您也根本看不到它的用途。計劃資源管理器受到相同的限制,因為它只能提供有關 SQL Server 提供的計劃的資訊。
- 在生成實際計劃時,程式碼依賴於執行時指標的不同來源。不幸的是,計劃 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,你可能希望):
顯然,您根本沒有得到任何 I/O 數據,因為查詢實際上並沒有被執行。現在,生成一個實際的計劃。您將在結果網格中獲得預期的 5 行,即以下計劃(絕對沒有提到 UDF,除了在 XML 中,您可以將其作為查詢文本的一部分和標量運算符的一部分找到):
以下輸出(儘管我們知道它必須從該表中讀取,但
STATISTICS IO
絕對沒有提及):Sales.SalesOrderDetail
表’SalesOrderHeader’。掃描計數 1,邏輯讀取 57,物理讀取 0,預讀讀取 0,lob 邏輯讀取 0,lob 物理讀取 0,lob 預讀讀取 0。
計劃探索者告訴你什麼
當 PE 為同一查詢生成估計計劃時,它知道的事情與 SSMS 相同。然而,它確實以稍微更直覺的方式顯示了一些東西。例如,外部查詢的估計計劃顯示了函式的輸出如何與查詢的輸出相結合,並且在單個計劃圖中可以立即清楚地知道兩個表都有 I/O:
它還單獨顯示了函式的計劃,我只是為了完整性而將其包括在內:
現在,讓我們看一個實際的計劃,它的用處要大幾千倍。這裡的缺點是,同樣,它只有 SQL Server 決定顯示的資訊,因此它只能顯示 SQL Server 提供的圖形計劃圖。這不是某人決定不向您展示有用的東西的情況。它只是根據提供的計劃 XML 對它一無所知。在這種情況下,就像在 SSMS 中一樣,您只能看到外部查詢的計劃,就好像根本沒有呼叫該函式:
Table I/O 選項卡也仍然依賴於 的輸出
STATISTICS IO
,它也忽略了函式呼叫中執行的任何活動:但是,PE 會為您獲取整個呼叫堆棧。我偶爾會聽到有人問,“Pffft,我什麼時候需要呼叫堆棧?” 好吧,您實際上可以分解每個函式呼叫所花費的時間、使用的 CPU 和讀取次數(以及對於 TVF,生成的行數) :
不幸的是,您無法將其關聯回 I/O 來自哪個表(同樣,因為 SQL Server 不提供該資訊),並且它沒有標有 UDF 名稱(因為它被擷取為臨時語句,而不是函式呼叫本身)。但它確實讓您看到,Management Studio 沒有看到的是您的 UDF 是什麼狗。您仍然需要連接一些點,但是點更少並且它們更靠近。
關於探查器
最後,我強烈建議遠離 Profiler,除非它是設置一個伺服器端跟踪,您將編寫腳本然後在任何 UI 工具的範圍之外執行。對生產系統使用 Profiler 幾乎肯定會導致比它所解決的問題更多的問題。如果您想獲取此資訊,請使用伺服器端跟踪或擴展事件,並確保非常明智地進行過濾。即使沒有分析器,跟踪也會影響您的伺服器,通過擴展事件檢索顯示計劃也不是世界上最有效的事情。