SQL Server 2000 中查詢優化器使用的統計

Microsoft SQL Server 2000 中查詢優化器使用的統計
Microsoft? SQL Server? 2000 收集關於儲存在資料庫中的索引和列資料的統計資訊。SQL Server 查詢優化器使用這些統計資訊來選擇用於執行 INSERT、SELECT、DELETE 或 UPDATE 查詢的最有效方案。本文說明了收集的資料型別、資料的儲存位置以及用於建立、更新與刪除統計的命令。預設情況下，SQL Server 2000 會自動建立和更新統計（當此類操作有用時）。本文也概括介紹瞭如何在不同的級別（索引、表和資料庫）上更改這些預設值。

SQL Server 2000 中的統計資料
SQL Server 2000 既收集關於單個列的統計資訊（單列統計），也收集關於成組的列的統計資訊（多列統計）。關於一個統計物件的所有資訊儲存在 SYSINDEXES 表中一行的多個列中。計算列以及資料型別為 ntext、text 或 image 的列不能被指定為統計列。組成一個統計集的所有列的總寬度不能超過 900 位元組。

SQL Server 2000 收集的統計資訊
上次收集統計資訊的時間（在 STATBLOB 中）。


表或索引中的行數（SYSINDEXES 中的 rows 列）。


表或索引所佔用的頁數（SYSINDEXES 中的 dpages 列）。


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


平均鍵長（在 STATBLOB 中）。


單列直方圖，包括步數（在 STATBLOB 中）。
注意： 直方圖是給定列的最多 200 個值的集合。給定列中的所有值（如果通過抽樣來收集統計資訊，則為選定的值）會被排序；排序後序列最多會劃分為 199 個間隔，以便得到最有效的統計。一般，這些間距的大小並不相等。以下數值與直方圖的每個步長儲存在一起。

表 1：直方圖的值

RANGE_HI_KEY 鍵值
EQ_ROWS 指定與 RANGE_HI_KEY 精確相等的行數。
RANGE_ROWS 指定區間中的行數。（這些行小於這個 RANGE_HI_KEY，但大於上一個較小的區間鍵值）。
DENSITY 指定 1/n，其中 n 表示區間中互不相同的值的數目。


使用 dbcc show_statistics 命令時顯示的是兩個匯出值而不是 DENSITY 資訊。

表 2：用兩個匯出值顯示 dbcc show_statistics 的直方圖

DISTINCT_RANGE_ROWS 指定此區間中互不相同的行的數目（不算 RANGE_HI_KEY 值本身）；DISTINCT_RANGE_ROWS = 1 / DENSITY。
AVG_RANGE_ROWS 區間中每個特定值的平均行數；AVG_RANGE_ROWS = DENSITY * RANGE_ROWS。


SQL Server 2000 中的直方圖只用於單個列、多列統計中的第一列或者索引。

SQL Server 2000 按照三個步驟從排序後的列值集合生成直方圖。第一步，最多收集 200 個 RANGE_HI_KEY、EQ_ROWS、RANGE_ROWS 和 DISTINC_RANGE_ROWS 的值。第二步，對每個其他的列值進行處理：該值或者被新增到上一個區間中（對值進行排序），或者建立一個新區間。如果是建立一個新區間，則兩個現有的相鄰區間會合併到一個區間中。SQL Server 2000 通過密度資訊來選擇要合併的區間，使密度最接近的兩個相鄰區間被合併，從而將資訊的損失降到最低程度。第三步，合併更多的密度接近的區間。因此，即使列中的唯一值個數超過 200，直方圖的步數也可能會小於 200。

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

密度是關於給定列或列的組合中重複項數目的資訊，其計算公式為 1/(互不相同的值的數目)。如果在相等條件判斷表示式中使用了某個列，則會使用從直方圖匯出的密度來估算符合條件的行數。直方圖通常用於估算不等條件判斷表示式。

注意： dbcc show_statistics 的第一行中會顯示一個單獨的密度值，但 SQL Server 2000 中的優化器不使用該值。

一個列集合的多列統計包括以下資訊：統計定義中第一個列的直方圖，第一個列的密度值，以及每個列的字首組合（單獨包括第一個列）的密度值。每個統計集（一個直方圖加上兩個或多個密度值）都儲存在 SYSINDEXES 表的一行中，同一行中還包括上次更新統計的時間戳、用於生成統計資訊的抽樣行數、直方圖的步數和平均鍵數。SQL Server 2000 只為編號為 0 或 1 的索引（堆索引或群集索引）維護行數的值（rowcnt 列），並在表中的所有索引中複製該值。同樣，SQL Server 2000 也為每個表和索引維護 dpages。如果收集統計資訊時表中沒有任何行，則該表的統計資訊為空。

使用 sp_helpindex 和 sp_helpstats 可以顯示給定表中所有可用統計的列表，sp_helpindex 列出了表中的所有索引，而 sp_helpstats 則列出了表中的所有統計。每個索引都帶有其中列的統計資訊。在相同的列中，使用 CREATE STATISTICS 命令建立的統計資訊與使用 CREATE INDEX 命令生成的統計資訊等價。唯一的差別在於，CREATE STATISTICS 命令預設採用抽樣方式，而 CREATE INDEX 命令則由於必須處理索引的所有行，因而使用 fullscan 收集統計資訊。

下面是羅斯文資料庫中 Order Details 表的所有索引和統計的示例。因為最初無索引的列上沒有任何統計資訊，所以在連線到羅斯文資料庫後，請先執行 sp_createstats。

表 3：羅斯文資料庫中的 Order Details 表

sp_helpindex [Order Details]

index_name index_description index_keys
OrderID PRIMARY 上的非群集索引 OrderID
PK_Order_Details PRIMARY 上群集的唯一主鍵 OrderID、ProductID
ProductID PRIMARY 上的非群集索引 ProductID


sp_helpstats [Order Details]

statistics_name statistics_keys
Discount Discount
Quantity Quantity
UnitPrice UnitPrice


也可以象下面這樣使用 dbcc show_statistics 命令來顯示統計資訊：

dbcc show_statistics ([Order Details],PK_Order_Details)

如果沒有返回任何資訊，則表明上次已經更新了統計，或在表中沒有任何行時就建立了索引。要更新 Order Details 表的統計，請執行 UPDATE STATISTICS [Order Details]；要更新羅斯文資料庫中所有表的統計，請執行 sp_updatestats。

注意： 該命令的輸出已經增強了可讀性。  

 

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