德瑞克：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

與 EX3. 比較起來，因為「統計資料」是正確的，系統可以選用耗用成本最低的索引來查詢。

參考資料：
Auto update statistics and auto create statistics - should you leave them on and/or turn them on??
http://www.sqlskills.com/blogs/Kimberly/post/Auto-update-statistics-and-auto-create-statistics-should-you-leave-them-on-andor-turn-them-on.aspx

Filtered indexes and filtered stats might become seriously out-of-date
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date.aspx

Auto-create and Auto-update Statistics
http://blogs.msdn.com/ianjo/archive/2005/11/10/491549.aspx

在 SQL Server 的統計的維護功能 (autostats)
http://support.microsoft.com/kb/195565

分析執行緩慢之查詢的檢查清單
http://technet.microsoft.com/zh-tw/library/ms177500.aspx

使用統計資料來改善查詢效能
http://technet.microsoft.com/zh-tw/library/ms190397.aspx

系統基底資料表
http://technet.microsoft.com/zh-tw/library/ms179503.aspx

Q. The ability of SQL Server 2000 to automatically update statistics for my tables and indexes is enabled by default. How often does SQL Server perform this update, and does the action have a negative effect on performance?
http://www.microsoft.com/technet/abouttn/flash/tips/tips_070604.mspx

SQL Server Statistics
http://blogs.technet.com/rob/archive/2008/05/16/sql-server-statistics.aspx

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://technet.microsoft.com/zh-tw/library/cc966419%28en-us%29.aspx

Index Metadata and Statistics Update Date for SQL Server
http://www.mssqltips.com/tip.asp?tip=1550

The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server database instance that hosts the BizTalk Server BizTalkMsgBoxDB database
http://support.microsoft.com/?scid=kb%3Ben-us%3B912262&x=12&y=7

The Parallelism setting for the instance of SQL Server when you configure BizTalk Server
http://support.microsoft.com/?scid=kb%3Ben-us%3B899000&x=18&y=17

德瑞克：SQL Server 學習筆記

2010-03-05

淺談與統計資料(Statistics) 相關的資料庫屬性：自動建立統計資料、自動更新統計資料與自動非同步更新統計資料。AUTO_CREATE_STATISTICS、AUTO_UPDATE_STATISTICS、AUTO_UPDATE_STATISTICS_ASYNC

沒有留言:

張貼留言