IT工程師數位筆記本

前言

經過前幾篇的分析，其實大體已經初窺到SQL Server統計信息的重要性了，所以本篇就要祭出這個神器了。

該篇內容會很長，坐好板凳，瓜子零食之類...

不廢話，進正題

技術準備

數據庫版本為SQL Server2008R2，利用微軟的以前的案例庫（Northwind）進行分析，部分內容也會應用微軟的另一個案例庫AdventureWorks

相信了解SQL Server的朋友，對這兩個庫都不會太陌生。

概念理解

關于SQL Server中的統計信息，在聯機叢書中是這樣解釋的

查詢優化的統計信息是一些對象，這些對象包含與值在表或索引視圖的一列或多列中的分布有關的統計信息。查詢優化器使用這些統計信息來估計查詢結果中的基數或行數。通過這些基數估計，查詢優化器可以創建高質量的查詢計劃。例如，查詢優化器可以使用基數估計選擇索引查找運算符而不是耗費更多資源的索引掃描運算符，從而提高查詢性能。

其實關于統計信息的作用通俗點將就是：SQL Server通過統計信息理解庫中每張表的數據內容項分布，知道里面數據“長得啥德行，做到心中有數”，這樣每次查詢語句的時候就可以根據表中的數據分布，基本能定位到要查找數據的內容位置。

比如，我記得我以前有篇文章寫過一個相同的查詢語句，但是產生了完全不同的查詢計劃，這里回顧下，基本如下：

SELECT * FROM Person.Contact
WHERE FirstName LIKE 'K%'

SELECT * FROM Person.Contact
WHERE FirstName LIKE 'Y%'

完全相同的查詢語句，只是查詢條件不同，一個查找以K開頭的顧客，一個查找以Y開頭的顧客，卻產生了完全不同的查詢計劃。

其實，這里的原因就是統計信息在作祟。

我們知道，在這張表的FirstName字段存在一個非聚集索引，目標就是為了提升如上面的查詢語句的性能。

但是這張表里面FirstName字段中的數據內容以K開頭的顧客存在1255行，也就是如果利用非聚集索引查找的方式，需要產生1225次IO操作，這可能不是最糟的，糟的還在后面，因為我們獲取的數據字段并不全部在FirstName字段中，而需要額外的書簽查找來獲取，而這個書簽查找會產生的大量的隨機IO操作。記住：這里是隨機IO。關于這里的查找方式在我們第一篇文章中就有介紹。

所以相比利用非聚集索引所帶來的消耗相比，全部的所以索引掃描來的更劃算，因為它依次掃描就可以獲取想要的數據。

而以Y開頭的就只有37行，37行數據完全通過非聚集索引獲取，再加一部分的書簽查找很顯然是一個很劃算的方式。因為它數據量少，產生的隨機IO量相對也會少。

所以，這里的問題來了：

SQL Server是如何知道這張表里FirstName字段中以K開頭的顧客會比較多，而以Y開頭反而少呢？。

這里就是統計信息在作祟了，它不但知道FirstName字段中各行數據的內容“長啥樣”，并且還是知道每行數據的分布情況。

其實，這就好比在圖書庫中，每個書架就是一張表，而每本書就是一行數據，索引就好像圖書館書籍列表，比如按類區分，而統計信息就好像是每類書籍的多少以及存放書架位置。所以你借一本書的時候，需要借助索引來查看，然后利用統計信息指導位置，這樣才能獲取書本。

希望這樣解釋，看官已經明白了統計信息的作用了。

這里多談點，有很多童鞋沒有深入了解索引和統計信息的作用前提下，在看過很多調優的文章之后，只深諳了一句話：調優嘛，創建索引就行了。

我不否認創建索引這種方式調優方式的作用性，但是很多時候關于建索引的技巧卻不了解。更巧的是大部分情況下屬于誤打誤撞創建完索引后，性能果真提升了，而有時候創建的索引卻毫無用處，只會影響表的其它操作的性能（尤其是Insert），更有甚者會產生死鎖情況。

而且，關于索引項的作用，其實很多的情況下，并不想你想象的那么美好，后續文章我們會分析那些索引失效的原因。

所以遇到問題，其實還要通過表象理解其本質，這樣才能做到真正的有的放矢，有把握的解決問題。

解析統計信息

我們來詳細分析一下統計信息中的內容項，我們知道在上面的語句中，在表Customers中ContactName列中存在一個非聚集索引項，所以在該列存在統計信息，我們可以通過如下腳本查看該表的統計信息列表

sp_helpstats Customers

然后通過以下命令來查看該統計信息的詳細內容，代碼如下

DBCC SHOW_STATISTICS(Customers,ContactName)

每一個統計信息的內容都包含以上三部分的內容。

我們依次來分析下，通過這三部分內容SQL Server如何了解該列數據的內容分布的。

a、統計信息的總體屬性項

該部分包含以下幾列：

• Name:統計信息的名稱。
• Updated:統計信息的最近一次更新時間，這個時間信息很重要，根據它我們能知道該統計信息什么時候更新的，是不是最新的，是不是存在統計信息更新不及時造成統計的當前數據分布不準確等問題。
• Rows:描述當前表中的總行數。
• Rows Sampled:統計信息的抽樣數據。當數據量比較多的時候，統計信息的獲取是采用的抽樣的方式統計的，如果數據量比較就會通過掃描全部獲取比較精確的統計值。比如，上面的例子中抽樣數據就為91行。
• Steps:步長值。也就是SQL Server統計信息的根據數據行的分組的個數。這個步長值也是有SQL Server自己確定的，因為步長越小，描述的數據越詳細，但是消耗也越多，所以SQL Server會自己平衡這個值。
• Density:密度值，也就是列值前綴的大小。
• Average Key length:所有列的平均長度。
• String Index:表示統計值是否為字符串的統計信息。這里字符串的評估目的是為了支持LIKE關鍵字的搜索。
• Filter Expression：過濾表達式，這個是SQL Server2008以后版本的新特性，支持添加過濾表達式，更加細粒度進行統計分析。
• Unfiltered Rows：沒有經過表達式過濾的行，也是新特性。

經過上面部分的數據，統計信息已經分析出該列數據的最近更新時間、數據量、數據長度、數據類型等信息值。

b、統計信息的覆蓋索引項

All density：反映索引列的稠密度值。這是一個非常重要的值，SQL Server會根據這個評分項來決定該索引的有效程度。

該分值的計算公式為：density=1/表中非重復的行數。所以該稠密度值取值范圍為：0-1。

該值越小說明該列的索引項選擇性更強，也就說該索引更有效。理想的情況是全部為非重復值，也就是說都是唯一值，這樣它的數最小。

舉個例子：比如上面的例子該列存在91行，假如顧客不存在重名的情況下，那么該密度值就為1/91=0.010989，該列為性別列，那么它只存在兩個值：男、女，那么該列的密度值就為0.5，所以相比而言SQL Server在索引選擇的時候很顯然就會選擇ContactName（顧客名字）列。

簡單點講：就是當前索引的選擇性高，它的稠密度值就小，那么它就重復值少，這樣篩選的時候更容易找到結果值。相反，重復值多選擇性就差，比如性別，一次過濾只能過濾掉一半的記錄。

Average Length:索引的平均長度。

Columns:索引列的名稱。這里因為我們是非聚集索引，所以會存在兩行，一行為ContactName索引列，一行為ContactName索引列和聚集索引的列值CustomerID組合列。希望能明白這里，索引基礎知識。

通過以上部分信息，SQL Server會知道該部分的數據獲取方式那個更快，更有效。

c、統計信息的直方圖信息

我們接著分析第三部分，該列直方圖信息，通過這塊SQL Server能直觀“掌控”該列的數據分布內容，我們來看

• RANGE_HI_KEY：直方圖中每一組數據的最大值。這個好理解，如果數據量大的話，經過分組，這個值就是當前組的最大值。上面例子的統計信息總共分了90組，總共才91行，也就是說，SQL Server為了準確的描述該列的值，大部分每個組只取了一個值，只有一個組取了倆值。
• RANGE_ROWS：直方圖的沒組數據的區間行數（不包括最大值）。這里我們說了總共就91行，它分了90組，所以有一組會存在兩個值，我們找到它：
• EQ_ROWS：這里表示和上面最大值相等的行數目。因為我們不包含一樣的，所以這里值都為 1
• DISTINCT_RANGE_ROWS：直方圖每組數據區間的非重復值的數目。上限值除外。
• AVG_RANGE_ROWS：每個直方圖平均的行數。

經過最后一部分的描述，SQL Server已經完全掌控了該表中該字段的數據內容分布了。想獲取那些數據根據它就可以從容獲取到，并且統計信息是排序了的。

所以當我們每次寫的T-SQL語句，它都能根據統計信息評估出要獲取的數據量多少，并且找到最合適的執行計劃來執行。

我也相信經過上面三部分的分析，關于文章開篇我們提到的那個關于‘K’和‘Y’的問題會找到答案了，這里不解釋了。

當然，如果數據量特別大，統計信息的維護也會有小小的失誤，而這時候就需要我們來站出來及時的彌補。

創建統計信息

通過上面的介紹，其實我們已經看到了統計信息的強大作用了，所以對于數據庫來說它的重要性就不言而喻了，因此，SQL Server會自動的創建統計信息，適時的更新統計信息，當然我們可以關閉掉，但是我非常不建議這么做，原因很簡單：No Do  No Die...

這兩項功能默認是開啟的，也就是說SQL Server會自己維護統計信息的準確性。

在日常維護中，我們大可不必要去更改這兩項，當然也有比較極端的情況，因為我們知道更新統計信息也是一個消耗，在非常的大的并發的系統中需要關掉自動更新功能，這種情況非常的少之又少，所以基本采用默認值就可以。

在以下情況下，SQL Server會自動的創建統計信息：

1、在索引創建時，SQL Server會自動的在索引列上創建統計信息。

2、當SQL Server想要使用某些列上的統計信息，發現沒有的時候，這時候會自動創建統計信息。

3、當然，我們也可以手動創建。

比如，自動創建的例子

select * into CustomersStats from Customers
sp_helpstats CustomersStats

來添加一個查詢語句，然后再查看統計信息

select * from CustomersStats
where ContactName='Hanna Moos'
go
sp_helpstats CustomersStats
go

當然，我們也可以根據自己的情況來手動創建，創建腳本如下

USE [Northwind]
GO
CREATE STATISTICS [CoustomersOne] ON [dbo].[CustomersStats]([CompanyName])
GO

SQL Server也提供了GUI的圖像化操作窗口，方便操作

在以下情況下，SQL Server會自動的更新統計信息：

 1、如果統計信息是定義在普通的表格上，那么當發生以下任一種的變化后，統計信息就會被觸發更新動作。

• 表格從沒有數據變成大于等于1條數據。
• 對于數據量小于500行的表格，當統計信息的第一個字段數據累計變化大于500以后。
• 對于數據量大于500行的表格，當統計信息的第一個字段數據累計變化大于500+（20%*表格總的數據量）以后。所以對于較大的表，只有1/5以上的數據發生變化后，SQL Server才會重新計算統計信息。

2、臨時表上也可以有統計信息。這也是很多情況下采用臨時表優化的原因之一。其維護策略基本和普通表格一樣，但是表變量不能創建統計信息。

當然，我們也可以手動的更新統計信息，更新腳本如下：

UPDATE STATISTICS Customers WITH FULLSCAN

文章寫的有點糙....但篇幅已經稍長了....先到此吧...后續我再補充一部分關于統計信息的內容。

關于調優內容太廣泛，我們放在以后的篇幅中介紹，有興趣的可以提前關注。

參考文獻

• 參照書籍《Microsoft SQL Server企業級平臺管理實踐》
• 參照書籍《SQL.Server.2005.技術內幕》系列

有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。

文章最后給出前面幾篇的連接，以下內容基本涵蓋我們日常中所寫的查詢運算的分解，看來有必要整理一篇目錄了.....

SQL Server調優系列基礎篇

SQL Server調優系列基礎篇（常用運算符總結）

SQL Server調優系列基礎篇（聯合運算符總結）

SQL Server調優系列基礎篇（并行運算總結）

SQL Server調優系列基礎篇（并行運算總結篇二）

SQL Server調優系列基礎篇（索引運算總結）

SQL Server調優系列基礎篇（子查詢運算總結）

-----------------以下進階篇-------------------

SQL Server調優系列進階篇（查詢優化器的運行方式）

SQL Server調優系列進階篇（查詢語句運行幾個指標值監測）

如果您看了本篇博客,覺得對您有所收獲，請不要吝嗇您的“推薦”。