SQL Server-聚焦什麼時候用OPTION(COMPILE)呢？

標籤：硬體   儲存   post   產生   cut   篩選條件   外部   解決方案   參數   

前言

上一篇我們探討了在靜態語句中使用WHERE Column = @Param OR @Param IS NULL的問題，有對OPTION(COMPILE)的評論，那這節我們來探討OPTION(COMPILE)的問題。

探討OPTION(COMPILE)問題

在SQL SERVER中任何時候第一次調用預存程序時，此時預存程序將會被SQL SERVER最佳化且查詢計劃在記憶體中會被緩衝。由於查詢計劃緩衝，當運行相同的預存程序時，它都將使用相同的查詢計劃，從而無需每次運行時對同一預存程序進行最佳化和編譯。因此，如果我們需要每天運行相同的預存程序若干次，那麼可以節省大量的時間和硬體資源。

如果每次啟動並執行預存程序中的在WHERE子句中具有相同的參數，則重複使用預存程序的相同查詢計劃是有意義的。但是，如果運行相同的預存程序，但是參數的值會改變呢？發生什麼取決於參數的典型性。如果預存程序的參數的值從執行到執行相似，那麼緩衝的查詢計劃將正常工作，查詢將按照執行最佳來。但是，如果參數不是典型的，那麼被重用的緩衝查詢計劃可能不是最優的，導致查詢運行更慢，因為它使用的查詢計劃並不是真正為所使用的參數設計的。下面我們藉助AdventureWorks2012樣本資料庫來用執行個體講解上述所描述的情況。

DECLARE    @AddressLine1 NVARCHAR(60) = NULL,    @AddressLine2 NVARCHAR(60) = NULL,    @City NVARCHAR(30) = NULL,    @PostalCode NVARCHAR(15) = NULL,    @StateProvinceID INT = NULL SET @City = 'Bothell'SET @PostalCode = '98011'SET @StateProvinceID = 79DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500)SET @ColumnName = 'a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Address AS a WHERE 1 = 1'IF (@AddressLine1 IS NOT NULL)    SET @SQL = @SQL + ' AND a.AddressLine1 LIKE ''%'' + @AddressLine1 + ''%'''IF (@AddressLine2 IS NOT NULL)    SET @SQL = @SQL + ' AND a.AddressLine2 LIKE ''%'' + @AddressLine2 + ''%'''IF (@City IS NOT NULL)    SET @SQL = @SQL + ' AND a.City LIKE ''%'' + @City + ''%'''IF (@PostalCode IS NOT NULL)    SET @SQL = @SQL + ' AND a.PostalCode LIKE ''%'' + @PostalCode + ''%'''IF (@StateProvinceID IS NOT NULL)    SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID' SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),                         @AddressLine2 NVARCHAR(60),                         @City NVARCHAR(30),                         @PostalCode NVARCHAR(15),                         @StateProvinceID INT'EXECUTE sp_executesql @SQL,@ParamDefinition,                           @AddressLine1  = @AddressLine1,                           @AddressLine2 = @AddressLine2,                           @City = @City,                           @PostalCode = @PostalCode,                           @StateProvinceID = @StateProvinceIDGO

我們運行上述查詢1次，看到查詢結果如下和計畫快取次數如下：

此時我們將外部變數StateProvinceID類型修改為SMALLINT，然後再來執行查詢和緩衝計劃，此時會出現查詢計劃使用次數是為2，還是出現兩條1呢？

此時我們再來將動態SQL中內部變數StateProvinceID類型修改為SMALLINT，此時會出現查詢計劃使用次數是為3，還是出現兩條，次數分別為2和1呢？

由上可知，如果我們修改外部變數參數類型不會影響查詢計劃緩衝即會達到重用目的，若修改動態SQL內部變數參數類型則不會重用查詢計劃緩衝。

大多數情況下，我們可能不需要擔心上述問題。但是，在某些情況下，假設從查詢的執行到執行的參數變化很大，則會引起問題。如果我們確定預存程序通常運行正常，但有時運行緩慢，則很可能會看到上述問題。在這種情況下，我們可以做的是改變預存程序，並添加OPTION(RECOMPILE)選項。

添加此選項後，預存程序將始終重新編譯自身，並在每次運行時建立一個新的查詢計劃。當然這會消除查詢計劃重用的好處，但確保了每次執行查詢時都使用正確的查詢計劃。如果預存程序中有多個查詢，那麼它將重新編譯預存程序中的所有查詢，即使那些不受非典型參數影響的查詢也是如此。

講完OPTION(COMPILE)，接下來我們講講如何建立效能稍高的預存程序。有些童鞋可能會建立如下預存程序。

CREATE PROC [dbo].[HighPerformanceExample](    @AddressLine1 NVARCHAR(60) = NULL,    @AddressLine2 NVARCHAR(60) = NULL,    @City NVARCHAR(30) = NULL,    @PostalCode NVARCHAR(15) = NULL,    @StateProvinceID SMALLINT = NULL )AS SET NOCOUNT ONSELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid FROM Person.Address AS aWHERE (a.AddressLine1 = @AddressLine1 OR @AddressLine1 IS NULL) AND      (a.AddressLine2 = @AddressLine2 OR @AddressLine2 IS NULL) AND      (a.City = @City OR @City IS NULL) AND      (a.PostalCode = @PostalCode OR @PostalCode IS NULL) AND      (a.StateProvinceID = @StateProvinceID OR @StateProvinceID IS NULL)--或者SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid FROM Person.Address AS aWHERE a.AddressLine1 = COALESCE(@AddressLine1, a.AddressLine1) AND      a.AddressLine2 = COALESCE(@AddressLine2, a.AddressLine2) AND      a.City = COALESCE(@City, a.City) AND      a.PostalCode = COALESCE(@PostalCode, a.PostalCode) AND      a.StateProvinceID = COALESCE(@StateProvinceID, a.StateProvinceID) --或者SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid FROM Person.Address AS aWHERE a.AddressLine1 = CASE WHEN @AddressLine1 IS NULL THEN a.AddressLine1 ELSE @AddressLine1 END AND  a.AddressLine2 = CASE WHEN @AddressLine2 IS NULL THEN a.AddressLine1 ELSE @AddressLine2 END AND  a.City = CASE WHEN @City IS NULL THEN a.City ELSE @City END AND  a.PostalCode = CASE WHEN @PostalCode IS NULL THEN a.PostalCode ELSE @PostalCode END AND  a.StateProvinceID = CASE WHEN @StateProvinceID IS NULL THEN a.StateProvinceID ELSE @StateProvinceID END GOSET NOCOUNT OFF

上述無論怎樣執行都將表現的非常糟糕。因為SQL SERVER不能將其很好地進行最佳化，如果這是由不同的參數組合產生，那麼我們可能會得到一個絕對糟糕的計劃。不難理解，當執行一個預存程序，並且還沒有產生一個查詢快取計劃。所以，管理員可能會更新統計資料或強制重新編譯（或者，甚至重新啟動SQL Server）來嘗試解決此問題，但這些都不是最佳解決方案。OPTION(COMPILE)重新編譯是個好東西，但是我們是不是像如下簡單加上重新編譯就可以了呢。

SELECT ...FROM ...WHERE ...OPTION (RECOMPILE);

如果我們要使用重新編譯，那麼我們是否需要考慮以下兩個問題呢？

如果我們知道一個特定的語句總是返回相同數量的行並使用相同的計劃（並且我們已測試過並知道這一點），那麼我們會正常建立預存程序並讓計劃得到緩衝。

如果我們知道一個特定的語句從執行到執行是不一樣的，最佳查詢計劃也會有所不同（我們也應該從執行多個測試樣本中知道這一點），然後我們會如正常一樣建立預存程序，然後使用OPTION(RECOMPILE)以確保語句的計劃不會被預存程序緩衝或儲存。在每次執行時，預存程序將獲得不同的參數，如此一來語句將在每次執行時得到一個新的計劃。

為了實現這點，我們需要分析所查詢的預存程序，例如在每個企業下有對應的使用者，我們想象一下所呈現的UI介面，首先是所有使用者，查詢條件則是企業下拉框，然後是使用者名稱或者員工工號等。當沒有任何篩選條件時則走查詢計劃緩衝，若選擇企業，或者還選擇了員工相關篩選條件則重新編譯。類似如下預存程序。

CREATE PROC [dbo].[HighPerformanceExample](    @AddressLine1 NVARCHAR(60) = NULL,    @AddressLine2 NVARCHAR(60) = NULL,    @City NVARCHAR(30) = NULL,    @PostalCode NVARCHAR(15) = NULL,    @StateProvinceID SMALLINT = NULL )AS SET NOCOUNT ONDECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500),@Recompile  BIT = 1;SET @ColumnName = 'a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Address AS a WHERE 1 = 1'IF (@StateProvinceID IS NOT NULL)    SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID' IF (@AddressLine1 IS NOT NULL)    SET @SQL = @SQL + ' AND a.AddressLine1 LIKE @AddressLine1'IF (@AddressLine2 IS NOT NULL)    SET @SQL = @SQL + ' AND a.AddressLine2 LIKE @AddressLine2'IF (@City IS NOT NULL)    SET @SQL = @SQL + ' AND a.City LIKE @City'IF (@PostalCode IS NOT NULL)    SET @SQL = @SQL + ' AND a.PostalCode LIKE @PostalCode'IF (@StateProvinceID IS NOT NULL)    SET @Recompile = 0IF (PATINDEX('%[%_?]%',@AddressLine1) >= 4        OR PATINDEX('%[%_?]%', @AddressLine2) = 0)    AND (PATINDEX('%[%_?]%', @City) >= 4        OR PATINDEX('%[%_?]%', @PostalCode) = 0)    SET @Recompile = 0IF @Recompile = 1BEGIN    SET @SQL = @SQL + N' OPTION(RECOMPILE)';END;SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),                         @AddressLine2 NVARCHAR(60),                         @City NVARCHAR(30),                         @PostalCode NVARCHAR(15),                         @StateProvinceID SMALLINT'EXECUTE sp_executesql @SQL,@ParamDefinition,                           @AddressLine1  = @AddressLine1,                           @AddressLine2 = @AddressLine2,                           @City = @City,                           @PostalCode = @PostalCode,                           @StateProvinceID = @StateProvinceIDGOSET NOCOUNT OFF

總結

本節我們講解了如何在預存程序中使用OPTION(COMPILE)，並且使得預存程序效能達到最佳，我想這是根據實際情境分析預存程序相對來說首選和最佳的方法，以至於我們不必每次都重新編譯。從而給我們長期更好的可擴充性。

SQL Server-聚焦什麼時候用OPTION(COMPILE)呢？