SQL Server 統計資訊理解，sql統計資訊

SQL Server 統計資訊理解，sql統計資訊

前言

Sqlserver 查詢是基於開銷查詢的，在首次產生執行計畫時，是基於多階段的分析最佳化才確定出較好的執行計畫。而這些開銷的基數估計，是根據統計資訊來確定的。統計資訊其實就是對錶的各個欄位的總體資料進行分段分布，資料庫預設都會自動維護。

 

表和視圖都有統計資訊，統計資訊對象是根據索引或表列的列表建立的。當某列第一次最為條件查詢時，將建立單列的統計資訊。當建立索引時，將建立同名的統計資訊。索引中，統計資訊只統計首列，因此索引除了按首列排序儲存資料外，其統計資訊也是按首列計算統計的，所以索引設定時定義的第一列非常重要。每個統計資訊對象都在包含一個或多個表列的列表上建立，並且包括顯示值在第一列中的分布的長條圖。

 

接下來瞭解統計資訊吧~~ ^ ^

統計資訊的查看：

可以看到，統計資訊也是表的一種對象。

--列出表中的所有統計資訊select * from sys.stats where object_id=OBJECT_ID(N'[Sales].[SalesOrderDetail]')

--查看統計資訊及其列SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_idFROM sys.stats AS sINNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_idINNER JOIN sys.columns AS c  ON sc.object_id = c.object_id AND c.column_id = sc.column_idWHERE s.object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]');--查看所有統計資訊更新時間exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL'

統計資訊的屬性：

右鍵統計資訊，選擇“屬性”，可看到統計資訊的設定和分布。

還可以使用命令DBCC SHOW_STATISTICS查看，以下為列。DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','IX_SalesOrderDetail_ProductID')

主要分為三部分，分別為“統計資訊頭部”，“密度向量”，“長條圖”

1 統計資訊頭資訊

列名	說明
Name	統計資訊對象的名稱（IX_SalesOrderDetail_ProductID）
Updated	上一次更新統計資料的日期和時間（Mar 14 2012  1:14PM）
Rows	上次更新統計資料時表或索引檢視表中的總行數（121317）。如果篩選統計資訊或者統計資訊與篩選索引對應，該行數可能小於表中的行數
Rows Sampled	用於統計資訊計算的抽樣總行數（121317）。如果 Rows Sampled < Rows，顯示的長條圖和密度結果則是根據抽樣行估計的。
Steps	長條圖中的梯級數(200)。 每個梯級都跨越一個列值範圍，後跟上限列值。 長條圖梯級是根據統計資訊中的第一個鍵列定義的。最大梯級數為 200。
Density	計算公式為：1/統計資訊對象第一個鍵列中的所有值（不包括長條圖邊界值）的 distinct values。(0.0078125) 查詢最佳化工具不使用此 Density 值，顯示此值的目的是為了與 SQL Server 2008 之前的版本實現向後相容。
Average Key Length	統計資訊對象中所有鍵列的每個值的平均位元組數 (12 :3個int類型。 ProductID, SalesOrderID, SalesOrderDetailID)
String Index	（NO）Yes 指示統計資訊對象包含字串摘要統計資訊，以改進對使用 LIKE 運算子的查詢謂詞的基數估計；例如 WHERE ProductName LIKE '%Bike'。 字串摘要統計資訊與長條圖分開儲存，如果統計資訊對象為char、varchar、nchar、nvarchar、varchar(max)、nvarchar(max)、text 或 ntext. 類型，則基於其第一個鍵列建立字串摘要統計資訊。
Filter Expression	包含在統計資訊對象中的表行子集的謂詞。 NULL = 未篩選的統計資訊。
Unfiltered Rows	應用篩選條件運算式前表中的總行數（121317）。 如果 Filter Expression 為 NULL，則 Unfiltered Rows 等於 Rows。

2 密度資訊

列名	說明
All Density	Density 為 1/distinct values。 結果顯示統計資訊對象中各列的每個首碼的密度，每個密度顯示一行。 非重複值是每個行首碼和列首碼的列值的非重複列表。 反映索引列的選擇性（selectivity）  "選擇性"反映資料集裡重複的資料量是多少，或者反過來說，值唯一的資料量有多少。如果一個欄位的資料很少有重複，那麼他的可選擇性就比較高。比如社會安全號碼，是不可重複的。哪怕對整個中國的身份記錄做查詢，代入一個社會安全號碼碼最多隻會有一條記錄返回，在這樣的欄位上的過濾條件，能夠有效地過濾掉大量資料返回的結果集會比較小舉個相反的例子：性別。所有人只有兩種，非男即女。這個欄位上的重複性就很高選擇性就很低。一個過濾條件，最多隻能過濾掉一半的記錄SQL通過計算“選擇性”，使得自己能夠預測一個過濾條件做完後，大概能有多少記錄返回 Density的定義是： density = 1/cardinality of index keys如果這個值小於0.1，一般講這個索引的選擇性比較高，如果大於0.1，他的選擇性就不高了。 （參考《Microsoft sqlserver 企業級平台管理實踐》）
Average Length	儲存列首碼的列值列表的平均長度（以位元組為單位）。
Columns	為其顯示 All density 和 Average length 的首碼中的列的名稱

這裡至於為什麼會有3行，是因為【ProductID】為非叢集索引，【SalesOrderID,SalesOrderDetailID】為叢集索引，而每個非叢集索引中都包含有叢集索引的鍵值，所以這裡的統計資訊也出現了3個可選項。

當前統計資訊 [All Density] 計算方法：

select count(*) from (select count(*) a from [Sales].[SalesOrderDetail]group by ProductID ) as Tselect count(*) from (select count(*) a from [Sales].[SalesOrderDetail] group by ProductID,SalesOrderID) as Tselect count(*) from (select count(*) a from [Sales].[SalesOrderDetail] group by ProductID,SalesOrderID,SalesOrderDetailID) as T--按不同組統計如下：group by ProductID  --266行group by ProductID, SalesOrderID  --121317行group by ProductID, SalesOrderID, SalesOrderDetailID  --121317行select 1.0/266 as [all density]union allselect 1.0/121317 as [all density]union allselect 1.0/121317 as [all density]

2 長條圖

列名	說明
RANGE_HI_KEY	長條圖梯級的上限列值。 列值也稱為鍵值。（按ProductID 的範圍分布）
RANGE_ROWS	其列值位於長條圖梯級內（不包括上限）的行的估算數目。（2個ProductID 值之間有多少行）
EQ_ROWS	其列值等於長條圖梯級的上限的行的估算數目。（等於當前行ProductID值的有多少行）
DISTINCT_RANGE_ROWS	非重複列值位於長條圖梯級內（不包括上限）的行的估算數目。 （2個ProductID 值之間有多少不重複的鍵值ProductID）
AVG_RANGE_ROWS	重複列值位於長條圖梯級內（不包括上限）的平均行數（如果 DISTINCT_RANGE_ROWS > 0，則為 RANGE_ROWS / DISTINCT_RANGE_ROWS）。

統計資訊的重要性：

SQLServer中，在執行一個批處理語句時，關聯式引擎中的查詢最佳化工具會先估計產生較優的執行計畫，執行執行器才安照此執行計畫請求資料。即在產生執行計畫期間，sqlserver是根據表中的統計資訊進行行數估計，按照指令碼語義來確定物理操作步驟產生執行計畫，再按照該執行計畫訪問資料。而對於資料較大的表，按照統計資訊估計的行數也常常不準確，這就是使查詢使用了不準確的執行計畫而比較慢。類似如：“參數嗅探”因傳遞參數值無法確定而估算錯誤；使用表變數不會有統計資訊也不會估算行數。

--現在以這個表的列統計為例[Sales].[SalesOrderDetail](SpecialOfferID)DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','_WA_Sys_0000000B_44CA3770')

如果查詢日期範圍在'2005-07-01'<ModifiedDate<='2005-08-01' ,看，查詢返回的估計行數應該為896.7728(190.2021+706.5707)

SELECT COUNT( ModifiedDate )FROM [Sales].[SalesOrderDetail] WHERE ModifiedDate >'2005-07-01 00:00:00'AND ModifiedDate<='2005-08-01 00:00:00'

估計行數為896.773，與統計資訊的長條圖的資訊一致。其實就是根據長條圖統計出來的，如果估計行數不準確，一定是統計資訊沒有正確的長條圖資訊，因此需要更新統計資料。有時候即使更新了統計資訊，結果還是一樣，因為資料量太大，估計資料不完全，看Rows Sampled可知道，因此也可以在更新統計資料時採用全表行數統計，但是這樣掃描表資料也耗效能。即便如此，還是有些可能不一樣，因為長條圖的步長最多200，資料列中相同的和不同的差距太大，200段分布也有參差不齊的資料，不能使用更多步更詳細的資料長條圖。

統計資訊的更新設定：

Sqlserver 預設自動維護統計資訊，在資料庫層級可以設定自動建立和更新統計資料的選項。

用指令碼設定如下：ALTER DATABASE [databaseName] SET AUTO_CREATE_STATISTICS ON WITH NO_WAITALTER DATABASE [databaseName] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAITALTER DATABASE [databaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

AUTO_CREATE_STATISTICS：

AUTO_CREATE_STATISTICS = ON 時，當將某列作為條件查詢時，系統自動為每個條件列建立單列的統計資訊。建立索引時也會自動建立相應的統計資訊. 查詢最佳化工具通過使用 AUTO_CREATE_STATISTICS 選項建立統計資訊時，統計資訊名稱以_WA 開頭。

 

AUTO_UPDATE_STATISTICS：

AUTO_UPDATE_STATISTICS = ON 時，查詢最佳化工具將確定統計資訊何時可能到期，然後在查詢使用這些統計資訊時更新它們。 統計資訊將在插入、更新、刪除或合併作業更改表或索引檢視表中的資料分布後到期。 查詢最佳化工具通過計算自最後統計資訊更新後資料修改的次數並且將這一修改次數與某一閾值進行比較，確定統計資訊何時可能到期。 該閾值基於表中或索引檢視表中的行數。查詢最佳化工具在編譯查詢和執行緩衝查詢計劃前，檢查是否存在到期的統計資訊。 在編譯某一查詢前，查詢最佳化工具使用查詢謂詞中的列、表和索引檢視表確定哪些統計資訊可能到期。 在執行緩衝查詢計劃前，資料庫引擎 確認該查詢計劃引用最新的統計資訊。

 

AUTO_UPDATE_STATISTICS_ASYNC：

非同步統計資訊更新選項AUTO_UPDATE_STATISTICS_ASYNC 將確定查詢最佳化工具是使用同步統計資訊更新還是非同步統計資訊更新。 預設情況下，非同步統計資訊更新選項被關閉，並且查詢最佳化工具以同步方式更新統計資料。 AUTO_UPDATE_STATISTICS_ASYNC 選項適用於為索引建立的統計資訊對象、查詢謂詞中的單列以及使用 CREATE STATISTICS 語句建立的統計資訊。統計資訊更新可以是同步（預設設定）或非同步。 對於同步統計資訊更新，查詢將始終用最新的統計資訊編譯和執行；在統計資訊到期時，查詢最佳化工具將在編譯和執行查詢前等待更新的統計資訊。 對於非同步統計資訊更新，查詢將用現有的統計資訊編譯，即使現有統計資訊已到期。如果在查詢編譯時間統計資訊到期，查詢最佳化工具可以選擇非最優查詢計劃。 在非同步更新完成後編譯的查詢將從使用更新的統計資訊中受益。

統計資訊自動維護更新：

Sqlserver之所以自動維護統計資訊，首先設定AUTO_UPDATE_STATISTICS=ON，sqlserver會在符合某條件時自動更新表中的統計資訊。其中我們可以看到的，系統資料表sysindexes的列rowmodctr，它記錄自上次更新統計資料後插入、刪除、更新行的累計總次數。對於滿足統計資訊更新的條件，系統會自動更新。

SELECT name,rows,rowmodctrFROMsys.sysindexes

 

自動更新統計規則：

•表中行範圍rows=0行增長 rows>0行；

•表中行範圍 0<rows<500行，只要變化的次數rowmodctr>500；

•表中行範圍rows>500行，只要變化的次數rowmodctr>500+20%rows；

•暫存資料表行數rows<6，只要變化的次數rowmodctr>6；

 

需要手動更新統計資料：

查詢執行時間很長。

在升序或降序鍵列上發生插入操作。

在維護操作後。

--建立測試表create table test(id int identity(1,1),name char(20),value numeric(18,4),meno varchar(50))create clustered index IX_test on test(name)alter table test add constraint PK_test primary key nonclustered(id)--以[dbo].[test]表為例,先查看select i.name,rows,rowmodctr,stats_date(s.object_id,s.stats_id) AS update_datefrom sys.sysindexes i inner join sys.stats s on i.name=s.namewhere s.object_id = OBJECT_ID('[dbo].[test]')--此時觀看兩個索引的長條圖,什麼都沒有DBCC SHOW_STATISTICS('[dbo].[test]','IX_test')DBCC SHOW_STATISTICS('[dbo].[test]','PK_test')--插入1行資料,統計資訊沒有更新?insert into test(name,value,meno)select 'name',0,'meno'

--最終增刪了506*2行,統計資訊都沒有產生insert into test(name,value,meno)select 'name',0,'meno'go 500delete from test

--重新測試：重新刪除建立表。在插入資料前,每個欄位搜尋一次，停用字詞段會自動產生統計資訊.select * from test where id=1select * from test where name=''select * from test where value=0select * from test where meno=''

--插入一行資料,否則操作任何列統計資訊都沒有更新insert into test(name,value,meno)select 'name',0,'meno'go --上面的查詢並沒有自動更新統計資訊,只有作為where條件的更改或刪除了才更新統計資料update test set name='name' where name='name'update test set value=0 where value=0update test set meno='meno' where id=1delete from test where meno='meno' 

--再重新插入資料,準備測試用insert into test(name,value,meno)select 'name',0,'meno'go --查看統計情況select i.name,rows,rowmodctr,stats_date(s.object_id,s.stats_id) AS update_datefrom sys.sysindexes i inner join sys.stats s on i.name=s.namewhere s.object_id = OBJECT_ID('[dbo].[test]')

--當我更新索引的統計資訊到 rowmodctr = 500 行的時候，統計資訊並沒有更新update test set name='name' where name='name'go 497

--z再更新一次，使索引更新累計rowmodctr = 501行update test set name='name' where name='name'go

結果發現：索引的統計資訊更新了，rowmodctr重新設定為1行。按相同的方法更新value為501次，非鍵列是沒有更新的！update test set value=0 where value=0 

也就是這個條件是符合的：表中行範圍 0<rows<500 行，只要變化的次數 rowmodctr>500 ；--插入資料到501行insert into test(name,value,meno)select 'name',0,'meno'go 500

當資料大於500行達到501行時，rowmodctr此時大於500行並沒有更新索引的統計資訊。--現在更新501行資料的20%，統計資訊並沒有更新。with tab as(select top 20 percent * from test)update tab set name='name'

現在行數602行，理論上超過601.2(501+501*0.2)行會更新，現在在更新一次，如果統計資訊自動更新就對了

看到，真的更新了！所以這個條件是符合的：表中行範圍rows>500行，只要變化的次數rowmodctr>500+20%rows；

統計資訊更新總結如下：

•表中行範圍rows=0行增長 rows>0行（插入時不更新，更新刪除行才更新）；

•表中行範圍 0<rows<500行，只要變化的次數rowmodctr>500；

•表中行範圍rows>500行，只要變化的次數rowmodctr>500+20%rows；

•暫存資料表行數rows<6，只要變化的次數rowmodctr>6（未測試）；

建立索引時自動產生同名統計資訊

非索引列在表有資料時首次作為條件查詢時自動產生統計資訊

對錶首次插入多少資料都不自動更新統計資訊

非鍵列的rows總是不更新（因為不儲存資料）

統計資訊相關操作：

--查看只索引的統計資訊更新時間SELECT name AS index_name,STATS_DATE(object_id, index_id) AS update_dateFROM sys.indexes WHERE object_id = OBJECT_ID('[Sales].[SalesOrderDetail]');--查看所有統計資訊更新時間select s.name,STATS_DATE(s.object_id, stats_id) AS update_datefrom sys.stats s WHERE s.object_id = OBJECT_ID('[Sales].[SalesOrderDetail]');--查看所有統計資訊更新時間exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL'GO--建立統計資訊CREATE STATISTICS [_WA_user_00000001_00000001] ON [Sales].[SalesOrderDetail](ProductID, SalesOrderDetailID)  --查看某個統計資訊DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','_WA_user_00000001_00000001')--更新1個統計資訊UPDATE STATISTICS [Sales].[SalesOrderDetail] [_WA_user_00000001_00000001] WITH FULLSCAN--更新表的所有統計資訊UPDATE STATISTICS [Sales].[SalesOrderDetail] --更新資料庫中所有可用的統計資訊 EXEC sys.sp_updatestats --刪除統計資訊DROP STATISTICS [Sales].[SalesOrderDetail].[_WA_user_00000001_00000001] 

參考：

統計資訊（SQL Server 2014）：

http://msdn.microsoft.com/zh-cn/library/ms190397(v=sql.120).aspx

 

DBCC SHOW_STATISTICS(Transact-SQL) （SQL Server 2014）：

http://msdn.microsoft.com/zh-cn/library/ms174384.aspx

 

UPDATE STATISTICS(Transact-SQL) （SQL Server 2014）：

http://msdn.microsoft.com/zh-cn/library/ms187348.aspx

《Microsoft sqlserver 企業級平台管理實踐》