參數嗅探如何影響 SQL 預存程序的效能以及如何對其進行最佳化？

SQL 預存程序執行計畫最佳化：參數嗅探及其影響

SQL 預存程序的效能可能會受到已知現象的顯著影響作為參數嗅探。當資料庫引擎根據編譯時提供的輸入參數的值來編譯預存程序的執行計劃時，就會發生這種情況。

考慮以下預存程序：

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

在此場景中，如果@MyDate在初始編譯時傳入NULL，資料庫會針對該值最佳化執行計畫。但是，使用其他輸入值對過程進行後續呼叫可能會導致效能不佳，即使這些值也是 NULL。

參數嗅探的影響

參數嗅探可以產生一些負面後果：

• 不可預測的執行時間：預存程序的效能可能會根據傳入的輸入值而發生巨大變化，導致不可預測的執行時間。
• 較差的執行計劃：引擎可能會產生低效的執行對於正在處理的實際資料來說不是最佳的計劃，導致查詢執行緩慢。

案例研究：參數嗅探出錯

在上述情況下，即使使用的值為NULL，為@MyDate 產生的執行計劃也很差。這種行為很不尋常，顯示參數嗅探有問題。

解決方案：禁用參數嗅探

解決方案是透過使用參數變數來停用參數嗅探，如下所示：

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

洞察問題

SQL Server 2005中的根本問題是某些補丁層級中的參數嗅探的已知問題。在 SQL Server 2008 中，可以使用 OPTIMIZE FOR UNKNOWN 子句來解決此類問題。

其他注意事項

為了進一步提高使用帶輸入參數的預存程序時的效能，建議：

• 使用參數化查詢來確保資料庫優化執行高效計劃。
• 避免使用 NULL 值作為輸入參數，因為它們可能會引發意外的最佳化問題。
• 定期監控​​預存程序的效能並根據需要調整執行計劃。

以上是參數嗅探如何影響 SQL 預存程序的效能以及如何對其進行最佳化？的詳細內容。更多資訊請關注PHP中文網其他相關文章！