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

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

-- 在firstname和lastname列上建立統計資訊

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

GO

-- 顯示錶上現在有三個統計資訊物件

sp_helpstats N'Person.Contact', 'ALL'

GO

結果：

統計資訊名稱	統計資訊鍵
_WA_Sys_00000002_1B29035F	LastName
FirstLast	FirstName, LastName
Phone	Phone

-- 顯示LastName列的統計資訊

DBCC SHOW_STATISTICS (N'Person.Contact', LastName)

GO

結果：

統計物件的標題資訊：

名稱	上次更新時間		行數		抽樣行數	步數	密度	平均鍵長度	字串索引
_WA_Sys_ 00000002_ 1B29035F	Mar 25 2005 11:21AM	5		5		4	0	13.6	YES

列集的字首以及相關的密度和長度：

所有密度	平均長度	列
0.25	13.6	LastName

直方圖步數：

RANGE_HI_ KEY	RANGE_ ROWS	EQ_ROWS	DISTINCT_ RANGE_ROWS	AVG_ RANGE_ROWS
Andersen	0	2	0	1
Smith	0	1	0	1
Williams	0	1	0	1
Zhang	0	1	0	1

-- If you take the name of the statistics object displayed by

-- the command above and subsitute it in as the second argument of

-- DBCC SHOW_STATISTICS you can form a command like the following one

--(the exact name of the automatically created statistics object

-- will typically be different for you).

DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_2D7CBDC4)

-- Executing the above command illustrates that you can show statistics by

-- column name or statistics object name.

GO

-- The following displays multi-column statistics. Notice the two

-- different density groups for the second rowset in the output.

DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)

結果（僅第二個結果集）

列集的字首以及相關的密度和長度：

所有密度	平均長度	列
0.3333333	11.6	FirstName
0.25	25.2	FirstName, LastName

如果希望看到一張大型表的完整生成的直方圖，執行下面的命令：

USE AdventureWorks

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

IF EXISTS (SELECT * FROM sys.stats

WHERE object_id = object_id('Sales.SalesOrderHeader')

AND name = 'TotalDue')

DROP STATISTICS Sales.SalesOrderHeader.TotalDue

GO

CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)

GO

DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)

使用SQL Server 2005建立統計資訊

您可以在SQL Server 2005中通過以下描述的幾種不同的方式建立統計資訊。

· 當AUTO_CREATE_STATISTICS開啟時（這是預設設定），查詢優化器對SELECT, INSERT, UPDATE和DELETE語句進行優化時自動建立單列的統計資訊。

· SQL Server 2005中有兩個基本語句可以顯式地生成上面描述的統計資訊：CREATE INDEX首先生成索引，然後再為構成索引鍵的那些複合列（但並不包含其它列）生成一組統計資訊。CREATE STATISTICS為指定的一列或複合列生成統計資訊。

· 此來還有幾種其它方法可以建立統計資訊或索引。但從根本上來說都是使用上面的兩個命令。使用sp_createstats為當前資料庫中所有使用者表的所有符合條件的列建立統計資訊（除了XML列）。如果列上幾經具有了統計資訊物件，則不再為該列建立新的統計資訊物件。

· 使用dbcc dbreindex重建指定資料庫中某張表上的一個或多個索引。

· 在“Management Studio”中展開Table物件下面的資料夾，右鍵單擊Statistics資料夾，選擇New Statistics。

· 使用資料庫優化嚮導（DTA）建立索引。

這裡是在 AdventureWorks.Person.Contact表上使用CREATE STATISTICS命令的示例：

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

WITH SAMPLE 50 PERCENT

通常，使用預設抽樣比率的統計資訊足以生成一個好的執行計劃。但是，也存在增加抽樣規模使統計資訊更利於查詢優化的情況，例如某個列上抽樣值並非隨機的。如果資料是排序的或者聚簇的，那麼就可能產生非隨機抽樣。建立索引或者將資料載入到已經排序或聚簇的堆中都可能導致資料排序或者聚簇。最常用的大規模抽樣就是fullscan，因為它能夠帶來最準確的統計資訊。使用更大規模抽樣的統計資訊其代價就是建立統計資訊所耗費的時間。

上面的例子建立了一個2列的統計資訊物件。在這個例子中，由於表太小了，因此忽略了SAMPLE 50 PERCENT而是執行一次完全掃描。抽樣主要是為了避免大量的資料掃描，只有包含了1,024或更多頁面（8 MB）的表或索引才會進行抽樣。

在SQL Server 2005中，當建立索引時會自動建立統計資訊。當編譯查詢時，SQL Server也會自動建立單列統計資訊。優化器為那些需要估算密度或資料分佈的列自動建立統計資訊。以下是該規則的幾個例外：當(1)資料庫是隻讀的(2) 太多的顯著的查詢編譯正在進行中(3) 列的資料型別不支援自動建立統計資訊，此時SQL Server 不會自動建立統計資訊。

通過執行下面的語句可以在資料庫級別禁用自動建立統計資訊功能

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

同樣地，執行下面的語句在資料庫級別啟用自動建立統計資訊功能

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

建議您將該選項設定為ON。只有當您需要解決某些效能問題，例如需要為某些表指定不同於預設值的抽樣比率時，才禁用該選項。

預設情況下，當執行CREATE STATISTICS命令或者自動建立統計資訊時，是通過對資料集抽樣的方式來建立統計資訊。CREATE INDEX總是掃描整個資料集，因此最初建立的索引統計資訊是沒有抽樣的（相當於fullscan）。CREATE STATISTICS命令允許您設定抽樣規模，在WITH子句中要麼指定fullscan，要麼指定掃描資料或行數的百分比。後者作為近似值來處理。也可以在UPDATE STATISTICS命令中指定WITH RESAMPLE來繼承原有的抽樣規模。這一點對於那些在某些列有索引（最初通過fullscan統計建立的）而其他一些列上只有統計資訊（最初通過SAMPLE統計建立的）的表來說是十分重要的。在UPDATE STATISTICS上使用resample選項將為索引維持fullscan統計，為其餘的列維持抽樣統計。

對於小型表至少需要抽樣8MB的資料。如果一張表開始時很小，您使用預設的抽樣比率進行抽樣，此後表的大小增長超過了8MB，那麼當您使用resample選項更新統計資訊時，您得到的同樣還是fullscan。如果您希望預設的抽樣比率隨著表的規模而變化，則應該避免使用resample。

resample抽樣比率是在前一次統計資訊計算時根據已抽樣行數和表中總行數的函式進行計算的。由於真實的抽樣比率可能因抽樣隨機性的特點而變化，因此對於非完全掃描抽樣來說，resample只是近似於上一次的抽樣比率。如果希望反覆一致地進行抽樣，在使用UPDATE STATISTICS之前顯式地指定相同的抽樣比率而不是使用resample。

dbcc show_statistics命令在Rows Sampled heading下面顯示抽樣規模。自動建立的統計資訊，或者自動更新的統計資訊（將在下一部分描述）總是進行預設的抽樣。預設的抽樣比率是一個表規模的慢速增長函式，這樣即使是十分大型的表也可以相對快速地收集統計資訊。

當建立和更新統計資訊時，查詢優化其必須選擇一種存取路徑來收集統計資訊。存取路徑可以包含堆、聚簇索引，或者非聚簇索引。對於抽樣的統計資訊，優化器儘量避免那些對統計資訊首列進行物理排序的存取路徑，這樣做有助於提供更隨機化的抽樣，也因此導致更加精確的統計資訊。對於那些沒有對統計資訊健值作排序的存取路徑（如果存在這樣的存取路徑），則選擇其中成本最低的一種，這就是最精確的索引或者堆。對於fullscan統計，由於存取路徑的排序順序對於統計資訊的準確性無關緊要，因此成本最低的存取路徑將被選中。

SQL Server Profiler可以監視自動的統計資訊建立。Auto Stats事件屬於Performance跟蹤事件組. 當定義跟蹤時，還要同時選中Auto Stats的IntegerData，Success和ObjectID列。一旦捕獲到AutoStats事件，Integer Data包含了在指定表上建立或者更新的統計資訊的數目， Object ID為表的ID，TextData（預設包含在跟蹤定義中）包含了建立或者更新統計資訊的列名，再加上Updated: 或者Created: 字首。Success包含了Auto Stats操作成功或者失敗的標記。 需要特別指出的是，Success有三個可能的值：

名稱	值	定義
FAILED	0	由於某種原因（除了THROTTLED，見下面），例如：資料庫是隻讀的，自動建立或者更新統計資訊失敗。
SUCCESS	1	自動建立或者更新統計資訊成功。
THROTTLED	2	由於當前有太多的優化正在進行中，自動建立或者更新統計資訊失敗。

有時您可能還會觀察到AutoStats事件但並沒有與之相關的統計資訊建立或更新操作發生。該事件的產生是由於auto update statistics選項被關閉，或者由於查詢引用的表上產生了大量的變更，由於查詢結構和外健約束的存在從而導致優化器從查詢計劃中移除所有對該表的引用。

DROP STATISTICS用於刪除統計資訊，但是不可以刪除作為建立索引的副產品而自動生成的統計資訊。這種統計資訊只有當刪除索引時才會被刪除。

在SQL Server 2005中維護統計資訊

當您在表中進行了一系列的插入，刪除和更新後，統計資訊可能無法反映出特定列或索引的真實資料分佈情況。如果SQL Server查詢優化器需要表中某一列的統計資訊，該表在上次建立或更新統計資訊後經歷了大量的更新活動，那麼SQL Server就會通過抽樣列值的方式自動更新統計資訊。（使用auto update statistics）。統計資訊的自動更新是由查詢優化器觸發的，或者通過執行一個編譯好的執行計劃而被觸發，並且只更新那些在查詢中被引用的列的子集。如果AUTO_UPDATE_STATISTCS_ASYNC為OFF，那麼在編譯查詢之前更新統計資訊，如果AUTO_UPDATE_STATISTCS_ASYNC為ON，則非同步更新統計資訊。

當一個查詢首次被編譯時，如果查詢優化器需要一個特定的統計資訊物件並且該物件存在，該統計資訊物件將被更新如果統計資訊物件已經過時。當執行一個查詢且該查詢的執行計劃在快取中，那麼執行計劃所依賴的統計資訊將被檢查已確認是否已經過時。如果是，執行計劃將從快取中移除，統計資訊在重新編譯查詢時被更新。如果執行計劃所依賴的統計資訊改變了，那麼執行計劃也將從快取中移除。

SQL Server 2005根據列更新計數器（colmodctrs）的變化決定是否更新統計資訊。

以下情況可以認定一個統計資訊物件已經過時：

· 如果在一張普通表上定義統計資訊，那麼該統計資訊將過時，如果：

1. 表的大小從0行增長到>0行。

2. 當收集統計資訊時，表中行數為500行或更少，此後統計資訊物件中首列的colmodctr的變更超過了500。

3. 當收集統計資訊時，表中含有500行以上的記錄，此後統計資訊物件中首列的colmodctr變更超過了500 + 收集統計資訊時表中行數的20%。

· 如果在一張臨時表上定義統計資訊，那麼在前面描述的情況下統計資訊也將過時。

表變數根本沒有統計資訊。

可以在不同的級別上關閉前面描述的auto update statistics特性。

· 在資料庫級別，使用下面的語句關閉自動更新統計資訊：

ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF

· 在表級別，使用UPDATE STATISTICS命令或者CREATE STATISTICS命令的NORECOMPUTE選項關閉自動更新統計資訊。

· 使用sp_autostats顯示和修改表、索引、或者統計資訊物件的自動更新設定。

同樣地，使用ALTER DATABASE，UPDATE STATISTICS或者sp_autostats可以重新啟用自動更新統計資訊。

SQL Server 2005

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