SQL Server 2005中的統計資料

Microsoft® SQL Server™ 2005收集關於單列的統計資訊（單列統計資訊），或者一組列的統計資訊（多列統計資訊）。查詢優化器使用統計資訊來評估表示式的選擇性，並由此來評估一個查詢中間結果和最終結果的規模大小。好的統計資訊允許優化器精確地判斷不同執行計劃的成本，並選擇一個高質量的執行計劃。一個統計物件的所有相關資訊儲存在sysindexes表中一行的多個列中，統計資訊中二進位制大物件（statblob）儲存在一張內部表中。此外，可以在新的後設資料檢視sys.stats和sys.indexes中找到關於統計物件的有關資訊。

統計特性概述

SQL Server 2005在維護統計資訊方面具有許多特性。最為重要的一點就是能夠自動建立和更新統計資訊。在SQL Server 2005和SQL Server 2000中預設將啟用該功能。大約98%的SQL Server 2000安裝將啟用該功能，這也是最佳實踐。對於大多數的資料庫和應用程式而言，開發人員和管理員可以藉助自動建立和更新統計資訊的功能以提供全面且準確的關於資料的統計資訊。這允許SQL Server 2005查詢優化器生成一致且有效的查詢計劃，同時使開發和管理的成本維持在較低的水平。如果您希望進一步控制統計資訊的建立和更新以獲得最佳的執行計劃並管理由於統計資訊收集而產生的開銷，您可以使用手動建立和更新統計資訊的功能。

對於大吞吐量聯機事務處理應用環境而言，一個重要的新特性就是以非同步方式自動更新統計資訊。 這種方式可以顯著提高OLAP環境中的查詢響應時間。

SQL Server 2005中的統計功能允許您：

· 使用預設的抽樣比率隱式地建立和更新統計資訊。（如果開啟了自動建立和更新統計資訊功能，那麼在SELECT，INSERT，DELETE和UPDATE命令的查詢條件如WHERE和UPDATE子句中使用某一列時，將根據需要自動建立或者更新統計資訊）

· 使用期望的抽樣比率手動建立和更新統計資訊、刪除統計資訊（CREATE STATISTICS, UPDATE STATISTICS, DROP STATISTICS, CREATE INDEX, DROP INDEX）。

· 為資料庫中所有表的所有列批量建立統計資訊（sp_createstats）。

· 手動更新資料庫中所有已存在的統計資訊（sp_updatestats）。

· 列出資料庫或表中所有統計資訊物件（sp_helpstats，目錄檢視sys.stats, sys.stats_columns）。

· 顯示統計資訊物件的描述資訊（DBCC SHOW_STATISTICS）。

· 在整個資料庫級別或者單張表或單個統計物件級別啟用或禁用自動建立和更新統計資訊功能（ALTER DATABASE的AUTO_CREATE_STATISTICS和AUTO_UPDATE_STATISTICS選項，sp_autostats以及 CREATE STATISTICS和UPDATE STATISTICS的NORECOMPUTE選項）。

· 啟用或禁用非同步自動更新統計資訊（ALTER DATABASE的 AUTO_UPDATE_STATISTICS_ASYNC選項）。

此外，SQL Server Management Studio允許您通過Object Explorer檢視以圖形化方式瀏覽和管理統計資訊物件。Object Explorer在每個表物件下方的資料夾中列出所有的統計物件。

SQL Server 2005中的統計資訊有哪些新特性？

SQL Server 2005包含許多新的統計特性，這些新特性允許查詢優化器為各種型別的查詢選擇更佳的執行計劃，或者用於增強統計資訊的管理功能。這些增強包括：

· 字串摘要統計：為字元型別的列維護的關於字串子串頻率分佈的資訊。這些資訊可以幫助查詢優化器更好地評估那些使用LIKE操作符的查詢條件的選擇性。

· 非同步自動更新統計資訊：SQL Server 2005中ALTER DATABASE命令提供了新的AUTO_UPDATE_STATISTICS_ASYNC選項。預設該選項是關閉的。如果啟用該選項，SQL Server 2005將在後臺進行自動統計資訊更新，這樣查詢就不會由於統計資訊更新而被阻塞，而是繼續使用舊的統計資訊進行處理。這種方式可以顯著提高某些工作負載的響應時間。

· 計算列上的統計資訊： 可以在計算列上手動或自動地建立和更新統計資訊（SQL Server 2000僅部分支援該功能，且相關的文件也不多）

· 大物件支援：可以將資料型別為ntext，text和image，也包括新資料型別nvarchar(max), varchar(max)以及varbinary(max) 的列指定為統計列。

· 增強的統計資訊載入框架：同SQL Server 2000相比，查詢優化器在內部對統計資訊的載入進行了增強。如今優化器將載入所有必須載入的統計資訊，從而提高了優化的質量和效能。

· 在計算列上自動建立統計資訊功能的增強：如果查詢中包含了與某個計算列表示式等價的表示式，那麼SQL Server 2005將在需要時自動在計算列上建立統計資訊。

· 最小抽樣規模： 在收集統計資訊時最少抽樣8 M資料。

· 統計資訊數目限度的增強：每張表上允許建立統計資訊的列數增加到了2,000。此外還允許同時存在249個索引統計，即每張表上最多允許2,249個統計資訊。

· 增強的DBCC SHOW_STATISTICS輸出：如今DBCC SHOW_STATISTICS將顯示統計物件的名稱，從而避免了二義性。

· 基於列的更新計數器的自動更新統計資訊：SQL Server 2000根據表中更新的行數來決定何時更新統計資訊。如今是在列的級別上跟蹤資料變更的，這樣就可以避免在那些變更還不夠充分的列上自動更新統計資訊。

· 內部表上的統計資訊：可以在sys.internal_tables列出的所有內部表上建立統計資訊，包括XML和全文索引、服務代理佇列，以及查詢通知表。

· DBCC SHOW_STATISTICS單一結果集輸出：DBCC SHOW_STATISTICS支援將標題、密度資訊以及直方圖作為單個結果集輸出。這樣更便於通過程式設計的方式自動處理DBCC SHOW_STATISTICS的輸出結果。

· 統計資訊最多可支援32列： 在一個統計物件中允許的列數由16列增加到32列。

· 分割槽表統計資訊：分割槽表是SQL Server 2005的新特性，並且完全支援統計資訊功能。SQL Server為每張表（而不是每個分割槽）維護直方圖資訊。

· 以並行方式進行fullscan統計資訊收集：使用fullscan方式收集統計資訊時，可以通過並行方式在無分割槽表或分割槽表上建立單個統計資訊物件。

· 當缺失統計資訊時增強的重新編譯和建立統計資訊功能：在自動建立統計資訊失敗的情況下，如果以後生成執行計劃時統計資訊是缺失的，那麼優化器將自動建立統計資訊並重新編譯執行計劃；因此不會持續出現統計資訊缺失的情況。更多詳細資訊請閱讀由Marathe發表的白皮書SQL Server 2005中批編譯、重新編譯，以及計劃快取的問題 。

· 增強的對空表重新編譯的邏輯和更新統計資訊功能：表中行數從0變化到大於0將導致查詢被重新編譯以及統計資訊被更新。更多詳細資訊請閱讀由Marathe發表的白皮書SQL Server 2005中批編譯、重新編譯，以及計劃快取的問題。

· 更加清晰和一致的統計資訊直方圖顯示：由於儲存直方圖之前總是先將直方圖按比例進行縮放，因此增強了DBCC SHOW_STATISTICS顯示直方圖的效果。

· 推導的資料關聯約束：通過啟用 DATE_CORRELATION_OPTIMIZATION資料庫設定，SQL Server可以維護通過外來鍵連線的兩張表的datetime列之間的關聯資訊。某些查詢可以使用這些資訊對隱含的條件進行判斷。儘管這些資訊並不直接用於查詢優化器進行選擇性評估或成本估算，因此從嚴格意義上來講，這些資訊並不是統計資訊，但卻與統計資訊密切相關，因為它們可以作為輔助資訊以幫助優化器獲得更好的查詢計劃。

· sp_updatestats: 在SQL Server 2005中，sp_updatestats根據sys.sysindexes相容檢視中記錄的rowmodctr資訊僅對那些需要更新的統計資訊進行更新。對於那些執行在90或更高相容級別下的資料庫，sp_updatestats將保持特定索引或統計資訊物件的自動UPDATE STATISTICS配置選項。

此外統計資訊的行為還有其它一些細微的變化。特別的，sys.sysindexes表statblob列的值始終為NULL，statblob本身儲存在一張內部目錄表中。

定義

我們先定義以下這些與SQL Server 2005統計資訊相關的術語：

· statblob：二進位制大物件統計資訊。該物件儲存在一個內部目錄檢視sys.sysobjvalues中。

· 字串摘要：對字串列的子串的頻率分佈進行彙總的一種統計資訊，用於幫助評估LIKE謂詞的選擇性。字串列的摘要資訊儲存在statblob中。

· sysindexes：sys.sysindexes目錄檢視，包含了表和索引的相關資訊。

· 謂詞：結果為true或者false的判定條件。謂詞出現在資料庫查詢的WHERE或者JOIN子句中。

· 選擇性：滿足謂詞的行數佔輸入資料集行數的比例。更復雜的選擇性評估可用於估算連線、DISTINCT以及其它操作產生的行數。例如，SQL Server 2005估算AdventureWorks資料庫的“Sales.SalesOrderHeader.OrderID = 43659”謂詞的選擇性是1/31465 = 0.00003178。

· 基數評估：估算結果集的大小。例如：如果表T包含100,000行記錄，查詢包含了形如T.a=10的選擇謂詞，直方圖顯示T.a=10的選擇性是10%，那麼查詢要考慮的表T中滿足條件的行數，即基數評估就是10% * 100,000 = 10,000。

· LOB: 大物件（text, ntext, image, varchar(max), nvarchar(max), varbinary(max) 型別的值）。

由SQL Server 2005收集的統計資訊

SQL Server 2005在表級別維護以下資訊。這些資訊並不屬於統計資訊物件，而是SQL Server 2005在某些情況下用來進行查詢成本估算的。

· 表或索引的行數（sys.sysindexes表的rows列）

· 表或索引佔用的頁面數（sys.sysindexes表的dpages列）

SQL Server 2005收集關於表中列的下述統計資訊，並儲存在一個統計資訊物件中（statblob）：

· 統計資訊收集的時間。

· 用於生成直方圖和密度資訊的行數（將在下面講解）。

· 平均鍵的長度。

· 單列直方圖，包含了步數。

· 字串摘要（如果某一列含有字串資訊）。如果一個統計資訊物件包含了字串摘要，那麼DBCC SHOW_STATISTICS輸出就會包含“String Index”列，並且該列的值為YES。

一個直方圖是給定列的最多200個值的集合。給定列的所有值或抽樣值會被排序；排序後的序列最多被劃分為199個間隔，以便獲得最有效的統計資訊。間隔的大小通常是不等的。下面的值或足以派生這些值的資訊被儲存在直方圖的每一步中。

SQL Server 2005只能為單列，或者多列統計資訊物件中的第一列建立直方圖。

SQL Server 2005通過三步從已排序的一組列值中建立直方圖：

· 初始化直方圖：在第一步，依次處理以排序集中第一個值作為起點的每個值，最多收集200個RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS以及DISTINCT_RANGE_ROWS（在這一步中，RANGE_ROWS和DISTINCT_RANGE_ROWS始終為零）。當所有輸入都被用盡，或者已經找到了200個值，第一步就結束了。

· 使用桶合併進行掃描：按照排序方式處理統計資訊中首列的其餘列值；每個值要麼被新增到最後一個區間，要麼被新增到一個新區間（由於輸入的值已經排序，因此允許這樣做）。如果建立了一個新區間，現有相鄰的一對區間將被合併到一個區間。選取相鄰的一對區間是為了將資訊損失的程度降到最低。整個過程當所有區間合併完成後步數依然是200。該方法是基於maxdiff直方圖的一種變形方法。

· 合併直方圖：在第三步，如果資訊損失的數量不十分明顯，就有更多的區間被合併。因此，即使列中唯一值的數目超過了200，那麼直方圖的步數也可能少於200。

如果通過抽樣來生成直方圖，那麼 RANGE_ROWS、EQ_ROWS、DISTINCT_RANGE_ROWS 和 AVG_RANGE_ROWS的值將為估計值，因此它們不必都是整數。

密度是給定的一列或組合列上關於重複項數目的資訊，其計算公式為1/（唯一值數目）。當在一個等值判斷表示式中使用了某一列時，就會使用從直方圖匯出的密度資訊來估算滿足條件的行數。還可以使用直方圖來估算非等值選擇判斷、連線以及其它操作的選擇性。

除了包含用於顯示統計資訊收集時間的時間戳、表中的行數、為生成直方圖而抽樣的行數、密度資訊和平均的鍵長度、直方圖本身以外，在單列統計資訊中還包含了統計資訊列集中組成字首的每一組列的All density值。這些資訊顯示在DBCC SHOW_STATISTICS輸出的第二個結果集中。All density 1/（字首列集中唯一值得數目）。下一部分將給出相關的一個示例。

注意：dbcc show_statistics返回結果的首行中包含的密度值是所有抽樣值的密度而不是RANGE_HI_KEY的密度。RANGE_HI_KEY通常是資料分佈中出現頻率較高的值。因此該命令所顯示的密度值為那些出現頻率不高的列值提供了潛在的有價值的資訊。

在組合列上建立的多列統計資訊包括：統計資訊定義中首列的直方圖，首列的密度值，以及每個字首組合列（單獨包括首列）的密度值。每組多列統計資訊（一個直方圖加上兩個或多個密度值）都儲存在一個statblob中，再加上上次更新統計資訊的時間戳、用於生成統計資訊的抽樣行數、直方圖的步數和平均鍵長度。只包含首列的字串摘要，如果首列中含有字元型資料。

使用sp_helpindex和sp_helpstats來顯示特定表上所有可用的統計資訊。sp_helpindex列出表上所有的索引，而sp_helpstats列出表上所有的統計資訊。每個索引都含有索引列的統計資訊。使用CREATE STATISTICS命令建立的統計資訊與在同一列上使用CREATE INDEX命令建立的統計資訊是等價的。唯一不同的是CREATE STATISTICS命令預設使用抽樣而CREATE INDEX則使用fullscan收集統計資訊，因為建立索引時無論如何都必須處理所有的行。

建立和顯示統計資訊：示例

下面的例子說明如何自動和手動的建立統計資訊，以及如何列表和顯示關於統計資訊的相關資料。示例中給出了某些命令但不是所有的命令的執行結果；如果某個SQL Server 2005命令的輸出結果對於闡述統計資訊的行為很有用，那麼該結果就顯示出來。您可以自己執行這個示例來檢視完整的輸出。

USE tempdb

GO

-- 清理以前執行指令碼生成的物件

IF object_id(N'Person.Contact','U') IS NOT NULL

DROP TABLE Person.Contact

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')

DROP SCHEMA Person

GO

-- 建立示例的schema和表

CREATE SCHEMA Person

GO

CREATE TABLE Person.Contact(

FirstName nvarchar(60),

LastName nvarchar(60),

Phone nvarchar(15),

Title nvarchar(15)

)

GO

-- 加工表中的行

INSERT INTO Person.Contact

VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')

INSERT INTO Person.Contact

VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')

INSERT INTO Person.Contact

VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')

INSERT INTO Person.Contact

VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')

INSERT INTO Person.Contact

VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')

GO

-- 顯示在Person.Contact表上還不存在統計資訊

sp_helpstats N'Person.Contact', 'ALL'

GO

-- 在LastName列上隱式地建立統計資訊

SELECT * FROM Person.Contact WHERE LastName = N'Andersen'

GO

-- 顯示在LastName列上自動建立的統計資訊

sp_helpstats N'Person.Contact', 'ALL'

GO

結果：

-- 建立索引，這將同時建立統計資訊

CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)

GO

-- 顯示建立索引時會同時建立相關的統計資訊

sp_helpstats N'Person.Contact', 'ALL'

GO

結果：