為什麼我的標量 UDF 在兩個不同（但極其相似）的伺服器上執行如此不同？
最近，我正在對一個影響應用程序生產環境的奇怪性能問題進行故障排除，但對任何較低的環境都沒有影響。我設法用這個查詢以最簡單的形式複制了這個問題：
SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Products
TranslateStatusToActive是一個非常簡單的標量 UDF，它基本上只是將給定的值連接到另一個表並根據case語句返回 1 或 0。我會發布程式碼，但它是供應商編寫的函式，我對今天被起訴並不特別感興趣。（是的，可以內聯邏輯。是的，它解決了性能問題。是的，我們已經說服供應商實施更改。這不是我的問題。）在生產環境中執行時，查詢需要 10 到 20 秒才能返回結果。在開發中，相同的查詢在不到 3 秒內返回。執行計劃幾乎相同，只是顯示 CPU 時間在生產中約為 15000 毫秒，在其他地方約為 3000 毫秒。
我懷疑存在一些環境差異，所以我設置了另一台伺服器，盡可能地複制生產條件：我確定了 CPU 的數量、分配給 SQL Server 的記憶體量以及特定的更新檔級別（13.0.0.0）。 4451) 相同。
我將生產數據庫的副本恢復到這個新的沙盒伺服器，令我驚訝的是，查詢的執行速度與它在開發中的執行速度差不多。再次，計劃和數據是相同的，除了額外的 CPU 時間。執行計劃中列出的等待是相同的類型，並且在每個環境中都在幾毫秒內。
不知道下一步該做什麼，我
optimize for ad hoc workloads在生產伺服器上啟用了。這解決了性能問題！但有一件事：其他環境都沒有啟用此設置。在測試過程中，我一直在定期清除每個環境中的過程和系統記憶體，所以我認為這不是更改設置導致重新編譯的結果。問題
- 儘管有相同的計劃和幾乎相同的系統，是什麼導致 UDF 在每個環境中執行如此不同？
- 為什么生產環境需要
optimize for ad hoc workloads啟用才能與其他沒有啟用它的環境一樣好？- 是否有一些我沒想到要檢查的設置可能會導致如此大的差異？
開發是共享的，而生產目前僅由該應用程序使用。第三個盒子的用法與生產的盒子幾乎相同。我幾乎清除了他們發出
DBCC命令的所有記憶體。開發環境經常用作培訓系統，所以我相當確信這不是計劃記憶體問題。與第三個盒子的唯一區別是它沒有連接應用程序，但是當我在生產中測試該功能時幾乎沒有應用程序使用，所以根據我在此環境中工作的經驗，區別在於，可以忽略不計。我唯一不能做的就是重新啟動生產伺服器，但微軟的文件明確指出啟用
optimize for ad hoc workloads不會清除或影響任何現有計劃，所以我看不出會有什麼區別。
當啟用某種監視（跟踪、擴展事件會話、某些第三方工具）時，您所描述的情況可能會發生，它會在每個 UDF 執行（甚至UDF中的每個語句）執行某種日誌記錄或工作。
如果在查詢中多次執行 UDF，則執行該監視可能會產生大量成本。如果監控只發生在一台伺服器上，那麼您會看到它們之間存在很大的性能差異。
我會試著用一個比喻來回答。將 SQL Server 視為汽車。
將*“針對臨時工作負載優化”*設置視為複雜的自動齒輪箱。當它注意到越野上坡時，它會設置到與直道不同的檔位。無論汽車經過何種地形，乘客都能享受順暢的旅程。
但是，當設置不存在時，這如何解釋相同查詢在類似伺服器中的不同行為？
在這種情況下，齒輪箱仍然是自動的，但並不那麼複雜。他們注意到並區分地形，因此（在汽車 A 中）它第一次看到特定地形時，比如越野上坡，它會設置一個特定的檔位。問題是，他們可能會錯過某些細節。下一次它看到類似的地形（比如越野下坡，或者高速公路有點上坡）時，它仍然使用與第一次相同的檔位。乘客抱怨因為裝備不是最好的。
第二輛汽車（B）從一條不同的路線開始，先是在高速公路上，然後是越野，所以變速箱在第一次遇到相似的（與 A 的）地形時做出的決定略有不同。幸運的是事情進展順利，乘客沒有抱怨。當然，如果下一回合後的地形需要與已經用於類似裝備不同的裝備，這可能會改變。
術語解釋：
automobile : SQL Server "adhoc workload" setting : automatic gear box terrain : query terrain details : parameters, table statistics, workload gear : query plan passengers : users, developers, DBAs還有一些注意事項：
- 當然，這個類比並不完美。關於如何使用“臨時工作負載”設置（打開或關閉）保存和重用（或不重用）計劃有很多細節。
- 設置不是解決所有問題的神奇按鈕。
OFF雖然它在許多情況下很有用，但 SQL Server 將其設置為預設值是有原因的。我敢肯定，在很多情況下它根本不會產生任何影響，甚至會降低性能。- 對於具體問題，我不能說我確定上面的描述是原因。這只是一種看似合理的可能解釋（因為更改此設置修復了它）。不同的行為可能還有其他原因（例如，您在生產伺服器和測試伺服器之間錯過了一些其他設置）或在兩個環境中執行的其他服務/程序。幾乎不可能有 2 個相同的設置。
- 如果您可以提供查詢和（類似的）計劃，那麼站點中的其他使用者可以更清楚地了解該問題。