德瑞克：SQL Server 學習筆記: 淺談與統計資料(Statistics) 相關的資料庫屬性：自動建立統計資料、自動更新統計資料與自動非同步更新統計資料。AUTO_CREATE_STATISTICS、AUTO_UPDATE_STATISTICS、AUTO_UPDATE_STATISTICS

與「統計資料」相關的資料庫屬性有：

(1)自動建立統計資料(AUTO_CREATE_STATISTICS)

(2)自動更新統計資料(AUTO_UPDATE_STATISTICS)

(3)自動非同步更新統計資料(AUTO_UPDATE_STATISTICS_ASYNC) -- SQL Server 2005 版本新增加

在 SQL Server 2008 線上叢書 (2009 年 7 月) 寫到：
自動建立統計資料選項 AUTO_CREATE_STATISTICS 和自動更新統計資料選項 AUTO_UPDATE_STATISTICS 預設已開啟，而且我們建議您針對大部分的使用者資料庫使用此預設值。

查詢最佳化工具會使用「統計資料」來建立可改善查詢效能的查詢計畫。

但在 SQL Server 2000 版本，此項可能會產生不必要的額外負荷，例如：
大型資料表在初始化產生「統計資料」時。

有巨量的資料的異動作業，導致需要同步更新「統計資料」。

在 SQL Server 2000 版本使用的是系統資料表 sysindexes 的 rowmodctr 資料行內容值作為「統計資料」的依據，需要即時更新，這可能會耗用過的資源。

在某些特定情形下，或許可以考慮停用「統計資料」，手動排程於離峰時間執行更新「統計資料」。

在 SQL Server 2005 版本，已不再使用系統資料表 sysindexes 的 rowmodctr 資料行內容值。而是使用系統基底資料表 sys.sysrowsetcolumns 的 rcmodified 資料行。

並且增加了自動非同步更新統計資料(AUTO_UPDATE_STATISTICS_ASYNC)選項。

在 SQL Server 2008 版本使用系統基底資料表 sys.sysrscols 的 rcmodified 資料行。
並且增加了「篩選統計資料(filtered statistics)」與「篩選索引(Filtered indexes)」等功能。

認識「自動非同步更新統計資料(AUTO_UPDATE_STATISTICS_ASYNC) 」

若為 [True]，初始化自動更新過期統計資料的查詢，將不會在編譯之前等待統計資料更新。當有可用的更新統計資料時，後續的查詢會使用這些統計資料。

若為 [False]，初始化自動更新過期統計資料的查詢，則會等到可在查詢最佳化計劃中使用更新的統計資料。

除非 [自動更新統計資料] 也設定為 [True]，否則將這個選項設定為 [True] 時並不會有任何影響。

以下範例將討論若是關閉「統計資料」相關功能，將導致系統使用不恰當的索引來查詢資料。

--00 建立範例資料庫 AutoStat 與資料表 TH01
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AutoStat')
DROP DATABASE [AutoStat]
GO
CREATE DATABASE AutoStat
GO
USE AutoStat
GO
-- 建立資料表 TH01
SELECT  TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate
INTO TH01
FROM    AdventureWorks2008.Production.TransactionHistory
-- 新增資料列
INSERT TH01(ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)
SELECT  ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate
FROM    AdventureWorks2008.Production.TransactionHistory
-- 建立 CLUSTERED INDEX：TransactionID
CREATE CLUSTERED INDEX [CL_TID] ON [dbo].[TH01]
(
[TransactionID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
-- 建立 NONCLUSTERED INDEX：ProductID
CREATE NONCLUSTERED INDEX [NCL_PID] ON [dbo].[TH01]
(
[ProductID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
--
UPDATE TH01
SET ProductID=9999999
WHERE TransactionID=100000

以下為開始執行查詢資料表與分析其索引使用方式：

USE AutoStat
GO
-- 總計資料列有：226886
SELECT COUNT(*) FROM TH01
-- 檢視資料
SELECT * FROM TH01

-- EX1. 檢視執行計畫與磁碟活動量的相關資訊
/*
「索引搜尋(NonClustered Index Seek)」+「索引鍵查閱(Clustered Key Lookup)」+「巢狀迴圈(Nested Loops)」
估計的子樹成本：0.0065704
預估回傳資料列：1

資料表 'TH01'。掃描計數 1，邏輯讀取 5，實體讀取 1，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
*/
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT TransactionDate
FROM TH01
WHERE ProductID=9999999

-- EX2.
/*
「叢集索引掃描(Clustered Index Scan)」
估計的子樹成本：1.421
預估回傳資料列：13140

資料表 'TH01'。掃描計數 1，邏輯讀取 1584，實體讀取 5，讀取前讀取 1308，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
*/
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT TransactionDate
FROM TH01
WHERE ProductID BETWEEN 880 AND 900

/*=========================================================*/
--01 關閉 AUTO_CREATE_STATISTICS 與 AUTO_UPDATE_STATISTICS
USE [master]
GO
ALTER DATABASE [AutoStat]
SET AUTO_CREATE_STATISTICS OFF
WITH NO_WAIT
GO
ALTER DATABASE [AutoStat]
SET AUTO_UPDATE_STATISTICS OFF
WITH NO_WAIT
GO
--02 將 ProductID 資料行內容值都改為 9999999
/*
總計資料列有：226886
但 ProductID 資料行內容值都是 9999999
*/
USE AutoStat
GO
UPDATE TH01
SET ProductID=9999999

/*=========================================================*/
-- EX3. 再度先前 EX1. 陳述式，檢視其執行計畫，仍然與關閉 AUTO_UPDATE_STATISTICS 選項之前一樣
/*
「索引搜尋(NonClustered Index Seek)」+「索引鍵查閱(Clustered Key Lookup)」+「巢狀迴圈(Nested Loops)」
估計的子樹成本：0.0065704
預估回傳資料列：1，但實際上則是會回傳資料列：226886 筆

資料表 'TH01'。掃描計數 1，邏輯讀取 680971，實體讀取 34，讀取前讀取 56，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
*/
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT TransactionDate
FROM TH01
WHERE ProductID=9999999

因為「統計資料」沒有更新，導致系統使用未更新的「統計資料」，使用不恰當的索引來查詢資料。

/*=========================================================*/
--01 重新啟用 AUTO_CREATE_STATISTICS 與 AUTO_UPDATE_STATISTICS 選項
USE [master]
GO
ALTER DATABASE [AutoStat]
SET AUTO_CREATE_STATISTICS ON
WITH NO_WAIT
GO
ALTER DATABASE [AutoStat]
SET AUTO_UPDATE_STATISTICS ON
WITH NO_WAIT
GO
--02 手動立即更新統計資料
EXEC sp_updatestats
GO

/*=========================================================*/
-- EX4. 再度檢視其執行計畫，系統已經重新產生，並且選用耗用成本最低的索引來查詢
/*
「叢集索引掃描(Clustered Index Scan)」
估計的子樹成本：1.421
預估回傳資料列：226886

資料表 'TH01'。掃描計數 1，邏輯讀取 1584，實體讀取 5，讀取前讀取 1594，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
*/
USE AutoStat
GO
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT TransactionDate
FROM TH01
WHERE ProductID=9999999