統(tǒng)計(jì)信息簡介

? ? ? ?SQL Server統(tǒng)計(jì)信息是系統(tǒng)對(duì)象，其中包含有關(guān)索引鍵值中的數(shù)據(jù)分布的信息，有時(shí)還包含常規(guī)列值。 可以在支持比較操作的任何數(shù)據(jù)類型上創(chuàng)建統(tǒng)計(jì)信息，例如>，<，=等。

? ? ? ?讓我們從上一章清單2-15中創(chuàng)建的dbo.Books表中檢查IDX_BOOKS_ISBN索引統(tǒng)計(jì)信息。 您可以使用DBCC SHOW_STATISTICS（'dbo.Books'，IDX_BOOKS_ISBN）命令來完成此操作。 結(jié)果如圖3-1所示。

??如您所見，DBCC SHOW_STATISTICS命令返回三個(gè)結(jié)果集。第一個(gè)包含有關(guān)統(tǒng)計(jì)信息的常規(guī)元數(shù)據(jù)信息，例如名稱，更新日期，更新統(tǒng)計(jì)信息時(shí)索引中的行數(shù)等。第一個(gè)結(jié)果集中的“步驟”列指示直方圖中的步數(shù)/值的數(shù)量（稍后將詳細(xì)介紹）。查詢優(yōu)化程序不使用“密度”值，僅出于向后兼容性目的而顯示。

? ? ? ?第二個(gè)結(jié)果集稱為密度向量，它包含有關(guān)統(tǒng)計(jì)（索引）中鍵值組合的密度的信息。它是基于1 /不同值公式計(jì)算的，它表示每個(gè)鍵值組合平均有多少行。即便如此 ?IDX_Books_ISBN索引只有一個(gè)鍵列ISBN定義，它還包括一個(gè)聚簇索引鍵作為索引行的一部分。我們的表有1,252,500個(gè)唯一的ISBN值，ISBN列的密度為1.0 / 1,252,500 = 7.984032E-07。 （ISBN，BookId）列的所有組合也是唯一的并且具有相同的密度。

? ? ? ?最后的結(jié)果集稱為直方圖。直方圖中的每個(gè)記錄（稱為直方圖步驟）都包括統(tǒng)計(jì)信息（索引）最左列中的樣本鍵值以及有關(guān)從前一個(gè)值到當(dāng)前RANGE_HI_KEY值的值范圍內(nèi)的數(shù)據(jù)分布的信息。讓我們更深入地檢查直方圖列。

? ? ? ? ? ? ? ? ? ? ?RANGE_HI_KEY列存儲(chǔ)密鑰的樣本值。 此值是直方圖步驟定義的范圍的上限鍵值。 例如，圖3-1中直方圖中的記錄（步驟）＃3與RANGE_HI_KEY ='104- ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0100002488'存儲(chǔ)有關(guān)從ISBN>'101-0100001796'到ISBN <='104-0100002488'的間隔的信息。

? ? ? ? ? ? ? ? ? ? ?RANGE_ROWS列估計(jì)間隔內(nèi)的行數(shù)。 在我們的例子中，記錄（步驟）＃3定義的間隔有8,191行。

? ? ? ? ? ? ? ? ? ? ?EQ_ROWS表示有多少行的鍵值等于RANGE_HI_KEY上限值。 在我們的例子中，只有一行ISBN ='104-0100002488'。

? ? ? ? ? ? ? ? ? ? ?DISTINCT_RANGE_ROWS表示密鑰在區(qū)間內(nèi)有多少個(gè)不同的值。 在我們的示例中，鍵的所有值都是唯一的，因此DISTINCT_RANGE_ROWS = RANGE_ROWS。 ? ? ? ? ? ? ? ? ? ? ?AVG_RANGE_ROWS表示間隔中每個(gè)不同鍵值的平均行數(shù)。 在我們的例子中，鍵的所有值都是唯一的，因此AVG_RANGE_ROWS = 1。

? ? ? ?讓我們?cè)谒饕胁迦胍唤M重復(fù)的ISBN值，代碼如清單3-1所示。

?

;with Prefix(Prefix) 
 as ( select Num from (values(104),(104),(104),(104),(104)) Num(Num) ) 
 ,Postfix(Postfix) 
 as 
 ( 
     select 100000001 
     union all 
     select Postfix   1 from Postfix where Postfix < 100002500 
 ) 
 insert into dbo.Books(ISBN, Title) 
     select 
         convert(char(3), Prefix)   '-0'   convert(char(9),Postfix) 
         ,'Title for ISBN'   convert(char(3), Prefix)   '-0'   convert(char(9),Postfix) 
     from Prefix cross join Postfix 
 option (maxrecursion 0); 

-- Updating the statistics 
 update statistics dbo.Books IDX_Books_ISBN with fullscan; 

現(xiàn)在，如果再次運(yùn)行DBCC SHOW_STATISTICS（'dbo.Books'，IDX_BOOKS_ISBN），您將看到如圖3-2所示的結(jié)果。

?????具有前綴104的ISBN值現(xiàn)在具有重復(fù)項(xiàng)，這會(huì)影響直方圖。 還值得一提的是，第二結(jié)果集中的密度信息也發(fā)生了變化。 具有重復(fù)值的ISBN的密度高于（ISBN，BookId）列的組合，這仍然是唯一的。

? ? ? ?讓我們運(yùn)行SELECT BookId，Title FROM dbo.Books WHERE ISBN LIKE'114％'語句并檢查執(zhí)行計(jì)劃，如圖3-3所示。

?

統(tǒng)計(jì)和執(zhí)行計(jì)劃

? ? ? ?SQL Server默認(rèn)自動(dòng)創(chuàng)建和更新統(tǒng)計(jì)信息。 數(shù)據(jù)庫級(jí)別有兩個(gè)控制此類行為的選項(xiàng)：

? ? ? ? ? ? ? ?1.“自動(dòng)創(chuàng)建統(tǒng)計(jì)信息”控制優(yōu)化程序是否自動(dòng)創(chuàng)建列級(jí)統(tǒng)計(jì)信息。 此選項(xiàng)不會(huì)影響始終創(chuàng)建的索引級(jí)統(tǒng)計(jì)信息。默認(rèn)情況下啟用“自動(dòng)創(chuàng)建統(tǒng)計(jì)數(shù)據(jù)庫”選項(xiàng)。

? ? ? ? ? ? ? ?2.啟用“自動(dòng)更新統(tǒng)計(jì)數(shù)據(jù)庫”選項(xiàng)后，SQL Server會(huì)在每次編譯或執(zhí)行查詢時(shí)檢查統(tǒng)計(jì)信息是否過時(shí)，并在需要時(shí)更新它們。 默認(rèn)情況下也會(huì)啟用“自動(dòng)更新統(tǒng)計(jì)數(shù)據(jù)庫”選項(xiàng)。

■ 提示：您可以使用STATISTICS_ NORECOMPUTE索引選項(xiàng)控制索引級(jí)別上統(tǒng)計(jì)信息的自動(dòng)更新行為。 默認(rèn)情況下，此選項(xiàng)設(shè)置為OFF，這意味著統(tǒng)計(jì)信息會(huì)自動(dòng)更新。 在索引或表級(jí)別更改自動(dòng)更新行為的另一種方法是使用sp_autostats系統(tǒng)存儲(chǔ)過程。

? ? ? ?SQL Server根據(jù)執(zhí)行的更改數(shù)量確定統(tǒng)計(jì)信息是否已過時(shí)影響統(tǒng)計(jì)信息列的INSERT，UPDATE，DELETE和MERGE語句。 SQL Server計(jì)算統(tǒng)計(jì)信息列的更改次數(shù)，而不是更改的行數(shù)。 例如，如果您將同一行更改100次，則將其計(jì)為100次更改而不是1次更改。

? ? ? ?有三種不同的方案，稱為統(tǒng)計(jì)信息更新閾值，有時(shí)也稱為統(tǒng)計(jì)信息重新編譯閾值，其中SQL Server將統(tǒng)計(jì)信息標(biāo)記為過時(shí)。

? ? ? ? ? ? ? ? 1 .當(dāng)表為空時(shí)，SQL Server在向表中添加數(shù)據(jù)時(shí)會(huì)過時(shí)。

? ? ? ? ? ? ? ? 2. 當(dāng)表的行少于500行時(shí)，SQL Server會(huì)在統(tǒng)計(jì)列每500次更改后過期統(tǒng)計(jì)信息。

? ? ? ? ? ? ? ? 3. 在SQL Server 2016和SQL Server 2016之前，數(shù)據(jù)庫兼容級(jí)別<130：當(dāng)一個(gè)表有500行或更多行時(shí)，SQL Server會(huì)在每500 （表中總行數(shù)的20％）變化后過期統(tǒng)計(jì)信息統(tǒng)計(jì)列。在SQL Server 2016中，數(shù)據(jù)庫兼容級(jí)別為130：大型表上的統(tǒng)計(jì)信息更新閾值將變?yōu)閯?dòng)態(tài)，并取決于表的大小。 表具有的行越多，閾值越低。 在具有數(shù)百萬甚至數(shù)十億?行的大型表上，統(tǒng)計(jì)信息更新閾值可能只是表中總行數(shù)的一小部分。 SQL Server 2008R2 SP1及更高版本中的跟蹤標(biāo)志T2371也可以啟用此行為。

??這導(dǎo)致我們得出一個(gè)非常重要的結(jié)論。使用靜態(tài)統(tǒng)計(jì)信息更新閾值，觸發(fā)統(tǒng)計(jì)信息更新所需的統(tǒng)計(jì)信息列的更改次數(shù)與表大小成比例。表越大，統(tǒng)計(jì)信息自動(dòng)更新的次數(shù)就越少。例如，對(duì)于包含10億行的表，您需要對(duì)統(tǒng)計(jì)信息列執(zhí)行大約2億次更改，以使統(tǒng)計(jì)信息過期。建議盡可能使用動(dòng)態(tài)更新閾值。

? ? ? ?讓我們來看看這種行為如何影響我們的系統(tǒng)和執(zhí)行計(jì)劃。此時(shí)，表dbo。書籍有1,265,000行。讓我們?cè)诒碇刑砑?50,000行，前綴為999，如清單3-5所示。在此示例中，我使用的是未啟用T2371的SQL Server 2012。如果在啟用動(dòng)態(tài)統(tǒng)計(jì)信息更新閾值的情況下運(yùn)行它，則可以看到不同的結(jié)果。此外，SQL Server 2014中引入的新基數(shù)估計(jì)器也可以改變行為。我們將在本章后面討論它。

 ;with Postfix(Postfix) 
 as 
 ( 
     select 100000001 
     union all 
     select Postfix   1 
     from Postfix 
     where Postfix < 100250000 
 ) 
 insert into dbo.Books(ISBN, Title) 
     select 
         '999-0'   convert(char(9),Postfix) 
         ,'Title for ISBN 999-0'   convert(char(9),Postfix) 
     from Postfix 
 option (maxrecursion 0); 

??現(xiàn)在，讓我們運(yùn)行SELECT * FROM dbo.Books WHERE ISBN LIKE'999％'查詢，選擇具有這種前綴的所有行。

??如果檢查查詢的執(zhí)行計(jì)劃（如圖3-7所示），您將看到非聚簇索引查找和鍵查找操作，即使它們?cè)谀枰獜谋碇羞x擇近20％的行時(shí)效率低下。

??您還將在圖3-7中注意到，Index Seek運(yùn)算符的估計(jì)行數(shù)和實(shí)際行數(shù)之間存在巨大差異。 SQL Server估計(jì)表中只有31.4行，前綴為999，即使有250,000行具有這樣的前綴。 結(jié)果，產(chǎn)生了非常低效的計(jì)劃。

? ? ? ?讓我們通過運(yùn)行DBCC SHOW_STATISTICS（'dbo.Books'，IDX_ BOOKS_ISBN）命令來查看IDX_BOOKS_ISBN統(tǒng)計(jì)信息。 輸出如圖3-8所示。 正如您所看到的，即使我們?cè)诒碇胁迦肓?50,000行，統(tǒng)計(jì)信息也沒有更新，并且直方圖中沒有前綴999的數(shù)據(jù)。第一個(gè)結(jié)果集中的行數(shù)對(duì)應(yīng)于行中的行數(shù)。 上次統(tǒng)計(jì)信息更新期間的表。 它不包括剛剛插入的250,000行。

??現(xiàn)在讓我們使用UPDATE STATISTICS dbo.Books IDX_Books_ISBN WITH FULLSCAN命令更新統(tǒng)計(jì)信息，然后再次運(yùn)行SELECT * FROM dbo.Books WHERE ISBN LIKE'990％'查詢。 查詢的執(zhí)行計(jì)劃如圖3-9所示。 估計(jì)的行數(shù)現(xiàn)在是正確的，并且SQL Server最終得到了一個(gè)更有效的執(zhí)行計(jì)劃，該計(jì)劃使用聚簇索引掃描，I / O讀取比以前少了大約17倍。

??如您所見，不正確的基數(shù)估計(jì)可能導(dǎo)致執(zhí)行計(jì)劃效率極低。 過時(shí)的統(tǒng)計(jì)數(shù)據(jù)可能是不正確基數(shù)估計(jì)的最常見原因之一。 您可以通過檢查執(zhí)行計(jì)劃中的估計(jì)和實(shí)際行數(shù)來查明其中一些情況。 這兩個(gè)值之間的巨大差異通常表明統(tǒng)計(jì)數(shù)據(jù)不正確。 更新統(tǒng)計(jì)信息可以解決此問題并生成更高效的執(zhí)行計(jì)劃。

?

統(tǒng)計(jì)維護(hù)?

正如我已經(jīng)提到的，SQL Server默認(rèn)自動(dòng)更新統(tǒng)計(jì)信息。對(duì)于小表，這種行為通常是可以接受的;但是，對(duì)于具有數(shù)百萬或數(shù)十億行的大型表，您不應(yīng)該依賴自動(dòng)統(tǒng)計(jì)信息更新，除非您使用的SQL Server 2016的數(shù)據(jù)庫兼容級(jí)別為130或啟用了跟蹤標(biāo)志T2371。按20％統(tǒng)計(jì)信息更新閾值觸發(fā)統(tǒng)計(jì)信息更新所需的更改次數(shù)將非常高，因此，不會(huì)經(jīng)常觸發(fā)更新。

? ? ? ?在這種情況下，建議您手動(dòng)更新統(tǒng)計(jì)信息。在選擇最佳統(tǒng)計(jì)維護(hù)策略時(shí)，您必須分析表的大小，數(shù)據(jù)修改模式和系統(tǒng)可用性。例如，如果系統(tǒng)在工作時(shí)間之外沒有負(fù)載，您可以決定每晚更新關(guān)鍵表的統(tǒng)計(jì)信息。不要忘記統(tǒng)計(jì)信息和/或索引維護(hù)會(huì)為SQL Server增加額外的負(fù)載。您必須分析它如何影響同一服務(wù)器和/或磁盤陣列上的其他數(shù)據(jù)庫。

? ? ? ?設(shè)計(jì)統(tǒng)計(jì)維護(hù)策略時(shí)需要考慮的另一個(gè)重要因素是如何修改數(shù)據(jù)。對(duì)于具有不斷增加或減少的鍵值的索引，您需要更頻繁地更新統(tǒng)計(jì)信息，例如當(dāng)索引中最左側(cè)的列定義為標(biāo)識(shí)或填充序列對(duì)象時(shí)。如您所見，如果特定鍵值超出直方圖，SQL Server會(huì)大大低估行數(shù)。在SQL Server 2014到2016中，此行為可能會(huì)有所不同，我們將在本章后面部分中看到。

? ? ? ?您可以使用UPDATE STATISTICS命令更新統(tǒng)計(jì)信息。當(dāng)SQL Server更新統(tǒng)計(jì)信息時(shí)，它會(huì)讀取數(shù)據(jù)樣本而不是掃描整個(gè)索引。您可以使用FULLSCAN選項(xiàng)更改該行為，該選項(xiàng)強(qiáng)制SQL Server讀取和分析索引中的所有數(shù)據(jù)。正如您可能猜到的那樣，該選項(xiàng)提供了最準(zhǔn)確的結(jié)果，盡管在大型表的情況下它可能會(huì)引入大量的I / O活動(dòng)。

■ 注意：重建索引時(shí)，SQL Server會(huì)更新統(tǒng)計(jì)信息。 我們將在第6章“索引碎片化”中更詳細(xì)地討論索引維護(hù)。

? ? ? ?您可以使用sp_updatestats系統(tǒng)存儲(chǔ)過程更新數(shù)據(jù)庫中的所有統(tǒng)計(jì)信息。 建議您使用此存儲(chǔ)過程并在將其升級(jí)到新版本的SQL Server后更新數(shù)據(jù)庫中的所有統(tǒng)計(jì)信息。 您應(yīng)該與DBCC UPDATEUSAGE存儲(chǔ)過程一起運(yùn)行它，它會(huì)更正目錄視圖中不正確的頁面和行計(jì)數(shù)信息。

? ? ? ?有一個(gè)sys.dm_db_stats_properties DMV，它顯示自上次統(tǒng)計(jì)信息更新以來對(duì)統(tǒng)計(jì)信息列所做的修改次數(shù)。 利用該DMV的代碼如清單3-9所示。

?

select 
     s.stats_id as [Stat ID], sc.name   '.'   t.name as [Table], s.name as [Statistics] 
     ,p.last_updated, p.rows, p.rows_sampled, p.modification_counter as [Mod Count] 
 from 
     sys.stats s join sys.tables t on 
         s.object_id = t.object_id 
     join sys.schemas sc on 
         t.schema_id = sc.schema_id 
     outer apply 
         sys.dm_db_stats_properties(t.object_id,s.stats_id) p 
 where 
     sc.name = 'dbo' and t.name = 'Books'; 

?查詢結(jié)果如圖3-11所示，表明自上次統(tǒng)計(jì)信息更新以來，對(duì)統(tǒng)計(jì)信息列進(jìn)行了250,000次修改。 您可以構(gòu)建統(tǒng)計(jì)信息維護(hù)例程，定期檢查sys.dm_db_stats_properties DMV并使用大的modification_ counter值重建統(tǒng)計(jì)信息。

?其他與統(tǒng)計(jì)相關(guān)的數(shù)據(jù)庫選項(xiàng)是異步自動(dòng)更新統(tǒng)計(jì)信息。 默認(rèn)情況下，當(dāng)SQL Server檢測(cè)到統(tǒng)計(jì)信息已過時(shí)時(shí)，它會(huì)暫停查詢執(zhí)行，同步更新統(tǒng)計(jì)信息，并在統(tǒng)計(jì)信息更新完成后生成新的執(zhí)行計(jì)劃。 通過異步統(tǒng)計(jì)信息更新，SQL Server使用舊的執(zhí)行計(jì)劃執(zhí)行查詢，該計(jì)劃基于過時(shí)的統(tǒng)計(jì)信息，同時(shí)異步更新后臺(tái)統(tǒng)計(jì)信息。 除非系統(tǒng)的查詢超時(shí)非常短，否則建議您保持同步統(tǒng)計(jì)信息更新，在這種情況下，同步統(tǒng)計(jì)信息更新可能會(huì)使查詢超時(shí)。

? ? ? ?最后，SQL Server不會(huì)在您創(chuàng)建新索引時(shí)自動(dòng)刪除列級(jí)統(tǒng)計(jì)信息。 您應(yīng)該手動(dòng)刪除冗余的列級(jí)統(tǒng)計(jì)信息對(duì)象。

《Pro SQL Server Internals, 2nd edition》的CHAPTER 3 Statistics中的Introduction to SQLSever Statistics SQL Server(P55~58)、Statistics and Execution Plans(P62~65)、?Statistics Maintenance(P68~69)三小節(jié)