Microsoft SQL Server 2005中查詢優化器使用的統計資訊三(zt)

Microsoft SQL Server 2005中查詢優化器使用的統計資訊三(zt)

SQL Server 2005在資料庫、表、索引或者統計資訊物件級別維護自動更新統計資訊的設定。當某人使用sp_autostats命令修改了某張表上所有物件的該項設定時，SQL Server通過單獨修改表上每個統計資訊物件和索引的設定來實現。不存在直接記錄整張表自動更新統計資訊設定為ON或OFF的後設資料。

下表顯示結合不同的資料庫、表、索引設定的效果：

資料庫設定	索引或統計資訊物件設定	該物件自動更新統計資訊的有效性
ON	ON	ON
ON	OFF	OFF
OFF	ON	OFF
OFF	OFF	OFF

在資料庫級別設定的OFF是無法通過在統計資訊物件級別將自動更新統計資訊選項設定為ON來改寫的。

自動更新統計資訊始終通過對錶或索引按預設的抽樣比率進行抽樣來完成。要想顯式地設定抽樣比率，執行CREATE或者UPDATE STATISTICS。

更新統計資訊包含在與建立統計資訊相同的SQL Profiler事件中。

字串摘要統計資訊

SQL Server 2005包含了專利技術用於評估LIKE條件的選擇性。該技術為字元型列的子串的頻率分佈建立統計摘要（字串摘要），包括資料型別為text，ntext，char，varchar和nvarchar的列。使用字串摘要，SQL Server能夠精確估算LIKE條件的選擇性，且LIKE條件的字串匹配模式中可以包含以任意方式組合的任何萬用字元。例如，SQL Server可以估算以下形式的謂詞的選擇性：

Column LIKE 'string%'

Column LIKE '%string'

Column LIKE '%string%'

Column LIKE 'string'

Column LIKE 'str_ing'

Column LIKE 'str[abc]ing'

Column LIKE '%abc%xy'

如果在LIKE模式中存在使用者指定的逃逸字元（例如，LIKE模式 ESCAPE逃逸字元），那麼SQL Server 2005將猜測選擇性。

SQL Server 2005在此處對SQL Server 2000進行了改進，SQL Server 2000對於LIKE模式中使用的任何萬用字元（除非用於字串尾部的%）都通過猜測來估算選擇性，因此估算的準確性十分有限。

如果統計物件中還包含了字串摘要，那麼DBCC SHOW_STATISTICS返回的第一個結果集的String Index欄位的值顯示為YES，但字串摘要的內容並不顯示出來。字串摘要中包含了許多無法通過直方圖檢視的其他資訊。

如果字串長度超過了80個字元，那麼會在建立字串摘要之前從字串中提取前40個和後40個字元，然後進行連線。因此無法對那些在字串被忽略的部分出現的子串進行準確的頻率評估。

在計算列上的統計資訊

SQL Server 2005支援在計算列上建立、更新和使用統計資訊，即使查詢中包含的不是計算列的名稱，而是計算列的表示式。SQL Server 2000則只能對那些在查詢中使用名稱的計算列自動建立、更新和使用統計資訊。

如果您在SQL Server 2005中執行下面的Transact-SQL指令碼，就可以觀察到為AdventureWorks資料庫的Sales.SalesOrderHeader.TotalDue列自動建立的計算列統計資訊：

USE AdventureWorks

GO

-- 刪除Sales.SalesOrderHeader上所有的統計資訊物件

DECLARE c CURSOR FOR

SELECT name FROM sys.stats

WHERE object_id = object_id('Sales.SalesOrderHeader')

AND auto_created <> 0 AND user_created <> 0

DECLARE @name NVARCHAR(255)

OPEN c

FETCH next FROM c INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC ('drop statistics Sales.SalesOrderHeader.' + @name)

FETCH NEXT FROM c INTO @name

END

CLOSE c

DEALLOCATE c

-- 使用與TotalDue計算列等價的表示式

--((isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))

-- 查詢Sales.SalesOrderHeader表

SELECT *

FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderID

AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00

ORDER BY TotalDue DESC

-- 列出已建立的統計資訊物件。觀察即使未在查詢中引用TotalDue列，但

-- SQL Server依然為該列建立了統計資訊

sp_helpstats 'Sales.SalesOrderHeader'

SQL Server 2005不支援在包含了CLR （公共語言執行時）表示式的non-persisted計算列上建立統計資訊，例如包含了一個CLR使用者定義函式的列。要想在CLR計算列上建立統計資訊，列必須被標記為PERSISTED。

在CLR使用者定義型別的列上的統計資訊

如果使用者定義型別支援二進位制排序，那麼SQL Server 2005支援在CLR使用者定義資料型別的列上建立、更新和使用統計資訊，不支援二進位制排序的使用者定義型別上是不支援統計資訊的。在型別定義中，如果在SqlUserDefinedType屬性中將IsByteOrdered設定為true，那麼型別就是支援二進位制排序的。 某種型別支援二進位制排序意味著該型別從語義上來講正確的順序就是標準的二進位制排序順序。

統計資訊和索引檢視

通常索引檢視並不需要統計資訊。這是因為在索引檢視被引入查詢計劃之前，所有底層表和索引的統計資訊已經被附加到了查詢計劃中。但是也有一個例外：如果在FROM子句中使用NOEXPAND提示直接引用了檢視，那麼將使用統計資訊。注意：如果在沒有索引的檢視上使用NOEXPAND提示將導致錯誤，也不會建立執行計劃。

由於索引檢視統計資訊受限制的使用方式，因此使用sp_createstats不會在索引檢視上建立統計資訊，使用sp_updatestats也不會更新索引檢視上的統計資訊。auto update和auto create statistics特性可以在索引檢視上工作。但是和前面一樣，請注意只有在查詢中通過NOEXPAND提示使用索引檢視，並且自動建立/更新統計資訊選項被開啟時，查詢優化器才需要並隨後建立統計資訊您也可以在索引檢視列上以手動方式執行CREATE STATISTICS，或者使用UPDATE STATISTICS手動地更新某一列或索引的統計資訊。

管理統計資訊的最佳實踐

在SQL Server中使用統計資訊的目標就是讓查詢優化器獲得良好的cardinality estimates，這樣就可以找到好的查詢執行計劃，同時將任何與統計資訊收集有關的開銷或延遲控制在合理的範圍內。下面我們將列出在SQL Server中管理統計資訊的最佳實踐，最重要的排在第一位。

使用自動建立和自動更新統計資訊

對於絕大多數SQL Server安裝而言，最重要的最佳實踐就是在整個資料庫範圍內使用自動建立和自動更新統計資訊。預設情況下自動建立和自動更新統計資訊是啟用的。如果您發現了糟糕的執行計劃並且懷疑是由於缺失統計資訊或過時的統計資訊引起的，請驗證自動建立和自動更新統計資訊被啟用了。

如果需要，有選擇性的使用FULLSCAN統計資訊

如果您正在使用自動建立和自動更新統計資訊，由於不準確或非最新的統計資訊您獲得了一個糟糕的執行計劃，請您完成下面的步驟：

· 繼續啟用自動建立和自動更新統計資訊，然後

· 僅僅對於那些不準確或非最新的統計資訊，使用 CREATE STATISTICS … WITH FULLSCAN, NORECOMPUTE 以及一個批處理作業，負責做 UPDATE STATISTICS … WITH FULLSCAN, NORECOMPUTE periodically.

統計資訊更新的頻率取決於您的應用程式，可能還需要一些經驗進行定奪。一個不錯的fullscan統計資訊更新頻率的參照點是：如果您正在關注的表更新頻率較高，那麼每天晚上執行一次fullscan統計資訊更新。如果表的更新頻率較低，那麼每週執行一次fullscan統計資訊更新。

避免在查詢中使用區域性變數

如果在查詢謂詞中使用區域性變數而不是引數或者文字常量，那麼會降低優化器對謂詞選擇性的估算質量，甚至猜測謂詞的選擇性。在查詢中使用引數或文字常量取代區域性變數，查詢優化器通常將選取一個更好的執行計劃。例如，考慮下面使用一個區域性變數的查詢：

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId

AND h.OrderDate >= @StartOrderDate

優化器估算Sales.SalesOrderHeader表中滿足h.OrderDate >= @StartOrderDate條件的行數是9439.5，即表大小的30%。您可以使用該查詢的圖形化執行計劃並右鍵單擊Sales.SalesOrderHeader執行計劃節點來檢視基數估算。由於準備該白皮書時使用的是SQL Server 2005預覽版，因此選中了使用合併連線的執行計劃（以下報告是基於相同的SQL Server 2005版本，根據您的SQL Server版本及可用記憶體等配置的不同，您的結果可能會有所不同）。現在，考慮一個等價但不使用區域性變數的查詢：

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId

AND h.OrderDate >= '20040731'

在查詢的圖形化顯示計劃中（右鍵單擊filter運算子），謂詞“h.OrderDate >= '20040731'”的選擇性是0.13%，那麼結果集的基數估算是40。由於提高了估算的準確性，因此巢狀迴圈連線而不是合併連線的執行計劃被選中。

即使在查詢中使用了區域性變數，那麼在等值判定謂詞中估算選擇性也好於猜測選擇性。“@local_variable = column_name”形式的查詢條件的選擇性是使用column_name列直方圖的平均資料分佈頻率來估算的。因此，舉例來說，如果column_name列的值全都是唯一值，那麼使用1/（列中唯一值數目）來估算選擇性就是準確的。

要消除對區域性變數的使用，請考慮：（1）重寫查詢，使用文字常量取代變數（2）通過帶引數的sp_executesql取代區域性變數的使用（3）使用帶引數的儲存過程替換區域性變數的使用。通過EXEC執行動態的SQL語句也可以消除區域性變數的使用，但這樣做通常會導致更高的編譯開銷。

考慮限制使用多語句的TVFs和表變數

多語句的表值函式（TVFs）沒有統計資訊。查詢優化器必須猜測函式運算結果規模的大小。類似的，表變數也沒有統計資訊，對於它們的基數優化器也必須採用猜測的方式。如果是因為這種猜測而導致一個糟糕的執行計劃，那麼考慮使用一張標準表或者臨時表作為TVF運算結果的臨時儲存，或者作為表變數的替換。這樣將允許查詢優化器做出更佳的基數評估。

不可摺疊表示式和內建的單值函式可能導致猜測

SQL Server在編譯時只能計算那些包含常量的表示式。這稱為constant folding。在估算選擇性時可摺疊的表示式將作為文字常量處理，而不可摺疊的表示式將導致猜測。例如，考慮下面的Transact-SQL指令碼。該指令碼加工一個含有200行的UserLog。半數行的UserName值不相同的而另外半數行的UserName值是相同的，導致了資料的扭曲分佈。

IF object_id('UserLog') IS NOT NULL

DROP TABLE UserLog

GO

CREATE TABLE UserLog (UserName NVARCHAR(255), Action NVARCHAR(1000))

DECLARE @i INT

SET @i = 1

SET nocount ON

WHILE @i <= 100

BEGIN

INSERT UserLog VALUES(suser_sname(), 'login')

INSERT UserLog VALUES(newid(), 'login')

SET @i = @i + 1

END

內建的suser_sname() 函式為當前的Windows使用者返回domain_nameuser_name，而newid() 用於返回唯一的使用者名稱。現在我們執行兩個等價的查詢。下面顯示的第一個查詢包含了對UserName = suser_sname()的預測。查詢優化器不得不去猜測結果的基數，猜測的值為1.98（由於SET STATISTICS XML ON，您可以在生成的XML顯示執行計劃的EstimateRows屬性中看到這個值）。由於實際的基數是100，因此猜測的值有很大的偏差。

GO

SET STATISTICS XML ON

GO

SELECT * FROM UserLog WHERE UserName = suser_sname()

GO

SET STATISTICS XML OFF

GO

第二個查詢通過sp_executesql執行了一個引數化查詢。suser_sname()值作為引數傳遞給查詢，而不是作為表示式出現在查詢中。

SET STATISTICS XML ON

GO

DECLARE @UserName NVARCHAR(255)

SET @UserName = suser_sname()

EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n',

N'@n nvarchar(255)', @UserName

GO

SET STATISTICS XML OFF

GO

這一次查詢優化器對於UserName = @n的選擇性使用了50%的準確評估。如果您檢視生成的XML執行計劃，您將發現EstimateRows是100，是完全正確的。 如果在一個更龐大的資料集上執行一個更長更復雜的查詢，那麼這種錯誤可能導致一個糟糕的執行計劃被選中。如果您的應用程式存在這個問題，考慮使用上面舉例闡述的技術。使用sp_executesql或者包含了存在問題查詢的儲存過程，然後將不可摺疊表示式預先計算好的結果作為引數傳遞給儲存過程。這樣就可以解決您的問題並因此獲得良好的基數估算。

在包含多列查詢條件的查詢中使用多列統計資訊

當查詢中含有多列查詢條件時，如果您懷疑查詢優化器沒有為查詢生成最佳執行計劃，考慮使用多列統計資訊。建立多列索引時自動產生多列統計資訊，因此如果已經存在了一個多列索引並且該索引支援多列查詢條件，那麼就無需顯示地建立多列統計資訊了。自動建立統計資訊只建立單列的統計資訊，永遠不會建立多列統計資訊。因此如果您需要多列統計資訊，要麼手動建立它們，要麼建立一個多列索引。

考慮存取AdventureWorks.Person.Contact表的查詢，它包含了如下的查詢條件：

FirstName = 'Catherine' AND LastName = 'Abel'

建立下面的統計資訊物件，查詢將獲得更加精確的選擇性估算：

CREATE STATISTICS LastFirst ON Person.Contact(LastName,FirstName)

該統計資訊物件對於在LastName和FirstName進行條件判斷，以及單獨在LastName列上作條件判斷的查詢將很有益。通常，一個多列統計物件任何字首列上的謂詞選擇性均可以使用該統計資訊物件進行估算。

要想使一個統計物件完全支援多列的查詢條件，那麼多列統計資訊物件的列字首必須包含查詢條件中所有的列。例如在列（a,b,c）上建立的多列統計資訊物件只能夠部分地支援a=1 AND c=1的查詢條件。SQL Server將使用直方圖估算a=1的選擇性但不會使用c的密度資訊，因為查詢條件中缺失了b。在（a,c）或者（a,c,b）上建立的多列統計資訊將支援查詢條件a=1 AND c=1，並且可以使用密度資訊來提高選擇性估算的準確性。

警惕會導致SQL Server猜測選擇性的情形

SQL Server在幾種情形下會猜測選擇性。通常猜測是合理的，如果資料的規模很小，或者猜測不會導致糟糕的執行計劃，那麼猜測都不成問題。然而，SQL Server猜測查詢謂詞的選擇性有時會導致非最佳的執行計劃。如果某個查詢的效能並非如您所願，並且您懷疑一個非最佳的執行計劃被選中，請檢視查詢和執行計劃中是否有跡象表明選擇性是通過猜測而不是根據統計資訊估算的。在很多情況下您可以通過稍微修改查詢或應用程式就能夠避免猜測。下表列出了可以導致猜測的一些情形，以及可能的解決辦法：

· 缺失統計資訊： 檢查是否啟用了自動建立統計資訊或者確保使用CREATE STATISTICS或 sp_createstats手動建立了統計資訊。檢查資料庫是否為只讀的，如果是，應防止自動建立統計資訊生效並工作。

· 使用區域性變數： 在查詢條件中（該問題已在前面闡述）

· 非常量-可摺疊的表示式：在查詢條件中（例如：T.x+1 = 0，suser_sname() = T.UserName）。 重寫查詢以避免出現表示式，或者在查詢執行前求出表示式的值，再將結果作為引數（而不是區域性變數）傳遞給查詢。就T.x+1 = 0而言，將表示式重寫為T.x = -1，這樣不僅結果相同而且還允許進行精確的評估而不是猜測。

· 複雜表示式： 例如 "Price + Tax >100" 或 "Price * (1+TaxRate) > 100"。如果在這種情況下您遇到了低於期望的查詢效能，考慮使用同樣的表示式建立一個計算列，並且在計算列上建立統計資訊或索引。如果存在計算列的話，自動建立統計資訊也同樣會為計算列建立統計資訊，這樣如果啟用了“自動建立統計資訊”，您就無需手動為計算列建立統計資訊了。

避免在查詢中使用引數值之前就在SP中修改儲存過程的引數值

為獲得最佳的查詢效能，在某些情況下應避免在儲存過程體中為引數分配新的值，然後在查詢中使用該引數值。儲存過程以及其包含的所有查詢最初將使用首次傳遞進來的查詢引數進行編譯。這有時也稱為引數嗅探。 考慮下面的儲存過程，它用於獲取在某個指定日期或之後的銷售值，如果將NULL作為引數傳遞給儲存過程，則獲取最後三個月的銷售值：

CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS

BEGIN

IF @date IS NULL

SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE)

FROM Sales.SalesOrderHeader))

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderID

AND h.OrderDate > @date

END

如果使用NULL呼叫該儲存過程，最後的SELECT語句將根據@date = NULL進行優化。由於不存在OrderDate為NULL的行，因此當在SalesOrderHeader上應用該過濾條件時，對結果的基數估算非常低（1行）。可是在儲存過程執行時日期卻並不為NULL，而是最後的OrderDate之前的三個月。 滿足條件的SalesOrderHeader記錄真正有5,736行。當將NULL傳遞給GetRecentSales時，查詢優化器為該查詢選擇了巢狀迴圈連線的執行方式，而優化的執行計劃則是使用合併連線方式。您可以使用下面的指令碼檢視選中的執行計劃，以及估算的和實際的基數：

SET STATISTICS PROFILE ON

GO

EXEC GetRecentSales NULL

GO

SET STATISTICS PROFILE OFF

GO

注意在前面的GetRecentSales儲存過程上指定的WITH RECOMPILE並沒有避免基數估算的錯誤。為了確保該示例中的查詢能夠根據正確的引數值進行優化，並因此獲得良好的選擇性估算，方法之一就是按以下方式修改儲存過程，將其分為幾部分：

CREATE PROCEDURE GetRecentSales (@date datetime) AS

BEGIN

IF @date IS NULL

SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE)

FROM Sales.SalesOrderHeader))

EXEC GetRecentSalesHelper @date

END

CREATE PROCEDURE GetRecentSalesHelper (@date datetime) WITH RECOMPILE AS

BEGIN

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderID

AND h.OrderDate > @date -- @date is unchanged from compile time,

-- so a good plan is obtained.

END

考慮對降序排序的鍵進行更頻繁地統計資訊收集

鍵值降序排序的列，例如IDENTITY列或者表示現實生活中時間戳的datetime列，由於頻繁地插入資料會導致表中的統計資訊不準確，因為新插入的值都位於統計資訊直方圖之外。如果您的應用程式看似使用了不適當的查詢計劃為那些使用鍵值降序排序的列作為查詢條件的查詢，考慮使用批處理作業更頻繁地更新這些列上的統計資訊。多久執行一次批處理作業執行取決於您的應用程式。考慮每天或每週執行一次，如果您的應用需要，也可以更頻繁。

使用非同步更新統計資訊如果同步更新統計資訊導致不希望的延遲

如果您有一個大型資料庫且進行OLTP處理，並且啟用了AUTO_UPDATE_STATISTICS，那麼有些通常只需零點幾秒就可以執行完成的事務將由於統計資訊自動更新而耗費幾秒甚至更長時間才能執行結束。如果您希望避免這種明顯延遲出現的可能性，請啟用 AUTO_UPDATE_STATISTICS_ASYNC。對於那些長時間執行的工作負載而言，一個更好的執行計劃要比編譯時不經常出現的延遲更為重要。在這種情況下，請使用非同步而不是同步方式自動更新統計資訊。

總結

SQL Server 2005中包含許多關於統計資訊管理能力的增強。最重要的是，在大多數情況下您可以藉助自動建立和自動更新統計資訊以確保良好的執行計劃。 當使用預設抽樣比率的自動統計功能無法滿足需求時，您還可以顯式地控制統計資訊的抽樣比率、建立和更新時間。如果您發現存在非最佳的執行計劃是與統計資訊或代價評估有關的，請考慮使用該白皮書中描述的最佳實踐。

請參閱

[Mar04] Arun Marathe, SQL Server 2005中批編譯，重新編譯和計劃快取的問題 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx, July 2004.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242526/，如需轉載，請註明出處，否則將追究法律責任。