執(zhí)行計劃統(tǒng)計信息索引天善智能一家之言

1、天善智能致全體 BI 同仁的各位 BI 同仁們：大家好！如果有一天，您有機會看到這封信，說明很有緣。天善智能是一個小團隊，是由幾位 BI 技術實戰(zhàn)者組織建立的草根組織，或者某一天會變得很強大，如果您支持和同意的話。天善智能專注于商業(yè)智能和數據庫性能優(yōu)化，2011 年 9 月由（Robay）組織成立，前期以接外包項目和顧問培訓服務為主，以及一些小范圍的獨家知識。在 2012 年 9 月，天善開始通過網絡來天善多年來在商業(yè)智能和數據庫方面的實戰(zhàn)經驗。目前天善智能總共已經制作了 23 份技術文檔，錄制了 25 部（陸續(xù)增加中.），開展了六期 BI 技術公開課，并且把這資料放在天善智能的博客，優(yōu)酷，各

2、種網絡媒介上免費讓大家，和學習。最難得的是，這些資料是網絡上最獨有，最全，最有含金量的，并整理的井井有序，免費開放了給大家。這樣做的目的，完全是因為天善體諒一些大學生、初學者想學習 BI 技術，但是又苦于學習無門的心情，因為天善的所有成員也經歷過一段這樣痛苦的時間，因此想通過這些小小的行為幫助到那些 BI 的初學者。俗話說：“授之以魚不如授之以漁”，天善智能雖然提供了這么多的資料供大家免費學習，但是天善認為僅僅做到這一點是不夠的，更好的方式應該是讓大家掌握一種無形的本領。這種本領可以描述成“如何思考、如何學習、如何解決問題、如果沉淀、如何成長”等。也許您會覺得有點虛無縹緲，但是請相信天善智能，

3、和天善一起前行成長，終有一天會實現各位心中的理想。最后，天善智能成立時間短，也還年輕，更急需成長，因此天善智能誠懇的希望您能提出具有建設性的建議，助天善團隊壯大，天善團隊，使天善邁向成階段。寫于 2012 年 12 月 22 日重生后的第一天如何找到天善？博客：(訂閱本博客隨時掌握天善動態(tài)，文檔工具。目前已經 600 人訂閱，還不訂閱更待何時？)QA： HYPERLINK http:/q/ http:/q(任何技術問題，只要您認真提，天善一定認真答。)5 群：236899666 群：237979203前 4 群基本滿員，多達 2000 人，加入也是必須的。（加入時請注明：天善智能）天善優(yōu)酷：/

4、tianshansoft天善智能博客：第 1 頁 共 21 頁且SQL 統(tǒng)計信息更新1統(tǒng)計信息統(tǒng)計信息基礎統(tǒng)計信息SQL Server 2005 允許創(chuàng)建有關列中值的分布情況的統(tǒng)計信息。查詢優(yōu)化器使用這些統(tǒng)計信息并通過估計使用索引評估查詢的開銷來確定最佳查詢計劃。按照默認設置，如果表中的某列沒有索引，則 SQL Server 會自動為該列創(chuàng)建統(tǒng)計。然后，查詢優(yōu)化器評估該列中數據分布范圍的統(tǒng)計信息，以選擇一個更為有效的查詢處理方案。創(chuàng)建統(tǒng)計信息后，數據庫引擎 對列值（根據這些值創(chuàng)建統(tǒng)計信息）進行排序，并根據這些值（最多 200 個，按間隔分隔開）創(chuàng)建一個“直方圖”。直方圖指定有多

5、少行精確匹配每個間隔值，有多少行在間隔范圍內，以及間隔中值的密度大小或重復值的發(fā)生率。1.1.2統(tǒng)計信息的作用1）index 建立后，優(yōu)化器是否使用該 index，優(yōu)化器需要借助一些統(tǒng)計信息來做判斷根據統(tǒng)計信息，預估采用嵌套循環(huán)連接，合并連接，哈希連接等哪根據統(tǒng)計信息判斷表的估計最佳的成本（最佳的執(zhí)行順序）接1.1.3統(tǒng)計信息的創(chuàng)建如果列上創(chuàng)建了索引，會自動生成一個跟索引同名的統(tǒng)計信息；沒有索引的列上，如果用它來關聯表或者查詢數據，這時，系統(tǒng)會在評估最佳查詢計劃前，生成一個該列的的統(tǒng)計信息，其前綴為_WA_Sys_。1.1.4統(tǒng)計信息內容顯示指定表上的指定目標的當前分發(fā)統(tǒng)計信息.(用戶必須是表

6、所有者，或者是 sysadmin 固定服務器角色、db_owner固定數據庫角色或 db_ddladmin 固定數據庫角色的成員。)DBCC SHOW_SISTICS ( table_name | view_name ,) WITH NO_INFOMSGS , n : =S_HEADER | DENSITY_VECTOR | HISTOGRAM參數：table_name | view_name是要顯示其統(tǒng)計信息的表或索引視圖的名稱。表名和視圖名稱必須符合標識符規(guī)則。是要顯示其統(tǒng)計信息的對象名稱（索引名稱、統(tǒng)計信稱或列名）。目標名稱必須符合標識符規(guī)則。如果是表的現有索引或統(tǒng)計信息的名稱，則返回有

7、關此目標的統(tǒng)計信息。如果容，則返回有關該自動創(chuàng)建統(tǒng)計內容的信息。NO_INFOMSGS取消嚴重級別從 0 到 10 的所有信息性消息。是現有列的名稱，且此列中存在自動創(chuàng)建的統(tǒng)計內S_HEADER | DENSITY_VECTOR | HISTOGRAM , n 如果指定以上一個或多個選項，可限制該語句返回的結果集。天善智能博客：第 2 頁 共 21 頁1.1.5統(tǒng)計信息創(chuàng)建1）可以使用以下語句創(chuàng)建統(tǒng)計信息：CREATE S參數：table_nameISTICS ON . WITH FULLSCAN ,PUTE 指定要創(chuàng)建的統(tǒng)計信息所基于的表的名稱。index_name要創(chuàng)建的統(tǒng)計信息所基于的索

8、引。如果未指定索引，則為表中的所有索引創(chuàng)建統(tǒng)計信息。FULLSCAN指定收集統(tǒng)計信息時應PUTE表或視圖中的所有行。指定應禁用統(tǒng)計信息的自動重新計算功能。如果指定此選項，即使數據發(fā)生更改，數據庫引擎也將仍然繼續(xù)使用舊的統(tǒng)計信息。數據庫引擎不自動更新和統(tǒng)計信息，這可能生成不理想的統(tǒng)計計劃。（不建議使用此參數）2）通過系統(tǒng)過程 sp_createss，可以為當前數據庫中所有用戶表的所有合格列和表創(chuàng)建單列統(tǒng)計信息。新的統(tǒng)計信息與創(chuàng)建該統(tǒng)計信息所在列具有相同的名稱。（需要 db_owner 固定數據庫角色成員資格。）語法如下：sp_creates , , 參數：s indexonly = indexo

9、nly fullscan = fullscan pute = pute indexonly = indexonly指定創(chuàng)建統(tǒng)計信息時只應考慮參與索引的列。indexonly 的數據類型為 char(9)。默認值為 NO。 fullscan = fullscan指定將 FULLSCAN 選項用于 CREATE SISTICS。如果忽略 fullscan，SQL Server 2005 Database Engine 將執(zhí)行默認示例掃描。fullscan 的數據類型為char(9)。默認值為 NO。pute = pute指定對新創(chuàng)建的統(tǒng)計信息禁用統(tǒng)計信息自動重新計算功能。pute 的數據類型為ch

10、ar(12)。默認值為NO。已經包含統(tǒng)計信息的列不會受影響，如索引的第一列或包含顯式創(chuàng)建的統(tǒng)計信息的列。對于每個滿足上述限制的列，將執(zhí)行CREATE SISTICS 語句。如果指定了 fullscan 則執(zhí)行 FULLSCAN。對于被禁用的索引的前導列，將不會對其創(chuàng)建統(tǒng)計信息。如果指定了 indexonly，那么，除非其他啟用的索引也使用了已禁用的非索引中的列，否則不會對該列創(chuàng)建統(tǒng)計信息。sp_createss 會忽略包含已禁用索引的表。1.1.6統(tǒng)計信息的更新1）為指定的表或索引視圖中的一個或多個統(tǒng)計信息組（集合）更新鍵值分布信息。UPDATE SISTICS ON . WITH FULLS

11、CAN ,PUTE 2）如果希望在當前數據庫中更新所有的統(tǒng)計信息，可以使用系統(tǒng)系統(tǒng)過程 sp_updatess。該過程在SQL Server2005 中進行了改善，只更新必要的（當數據發(fā)生改變時）統(tǒng)計信息。不會更新未改變數據的統(tǒng)計信息。示例：Exec sp_updatess;執(zhí)行后的結果：天善智能博客：第 3 頁 共 21 頁.（省略部分結果）正在更新dbo.AC_AR_WA_Sys_00000001_7E2D9D55已更新. _WA_Sys_00000002_7E2D9D55已更新._WA_Sys_00000005_7E2D9D55，不需要更新. _WA_Sys_00000007_7E2D9

12、D55已更新. _WA_Sys_00000003_7E2D9D55已更新. _WA_Sys_0000000E_7E2D9D55已更新.已更新 5 個索引/統(tǒng)計資料，個不需要更新。正在更新dbo.ESERVICE_DW_K_TBL_WA_Sys_00000001_7EECB764已更新. _WA_Sys_00000002_7EECB764已更新.已更新 2 個索引/統(tǒng)計資料，個不需要更新。正在更新dbo.JOB_TYPE PK_JOB_TYPE，不需要更新.已更新 0 個索引/統(tǒng)計資料，個不需要更新。正在更新dbo.ESERCICE_DW_M_DESIGN_RELATION_TBL已更新 0 個

13、索引/統(tǒng)計資料，個不需要更新。所有資料表的統(tǒng)計資料都已更新。1.1.7索引信息刪除刪除指定的表和索引的統(tǒng)計信息。Drop sistics on .1.1.8更新指定表索引統(tǒng)計索引，更新指定表單個索引信息，對表進行全表掃描，更新索引信息update supdate s update sistics 表名istics 表名 索引名istics 表名(列名) with fullscan2淺談 SQLSERVER 統(tǒng)計對于查詢的影響2.1簡介SQL Server 查詢分析器是基于開銷的。通常來講，查詢分析器會根據謂詞來確定該如何選擇高效的查詢路線,比如該選擇哪個索引。而每次查詢分析器尋找路徑時，并不會

14、每一次都去統(tǒng)計索引中包含的行數，值的范圍等，而是根據一定條件創(chuàng)建和更新這些信息后保存到數據庫中，這也就是所謂的統(tǒng)計信息。2.1.1如何查看統(tǒng)計信息查看 SQL Server 的統(tǒng)計信息非常簡單，使用如下指令：DBCC SHOW_SISTICS(表名,索引名)所得到的結果如圖 1 所示。天善智能博客：第 4 頁 共 21 頁2.1.2統(tǒng)計信息如何影響查詢下面通過一個簡單的例子來看統(tǒng)計信息是如何影響查詢分析器。我建立一個測試表，有兩個值的列，其中id為自增，ref 上建立非例數據的統(tǒng)計信息。索引，100 條數據，從 1 到 100，再9900 條等于 100 的數據。圖 1 中的統(tǒng)計信息就是示此時

15、，我 where 后使用 ref 值作為查詢條件，但是給定不同的值，的選擇，如圖 2 所示?？梢钥闯龈鶕y(tǒng)計信息，查詢分析器做出了不同天善智能博客：第 5 頁 共 21 頁圖 2.根據不同的謂詞，查詢優(yōu)化器做了不同的選擇其實，對于查詢分析器來說，柱狀圖對于直接可以確定的謂詞非常管用，這些謂詞比如:where date = getdate() where id= 12345where monthly_sales (select sum(qty) from sales) where a.id =b.ref_idwhere col1 =1 and col2=2這類在運行時才能知道值的查詢，采樣步長就

16、明顯不是那么好用了。另外，上面第四行如果謂詞是兩個查詢條件，使用采樣步長也并不好用。因為無論索引有多少列，采樣步長僅僅密度來確定最佳的查詢路線。索引的第一列。當柱狀圖不再好用時，SQL Server 使用密度的公式是：1/表中唯一值的 個數。當密度越小時，索引越容易被選中。比如圖 1 中的第二個表，下公式來計算一下密度:可以通過如天善智能博客：第 6 頁 共 21 頁圖 3.某一列的密度根據公式可以推斷，當表中的數據量逐漸增大時，密度會越來越小。對于那些不能根據采樣步長做出選擇的查詢，查詢分析器使用密度來估計行數,這個公式為:估計的行數=表中的行數*密度那么，根據這個公式，如果我做查詢時，估計

17、的行數就會為如圖 4 所示的數字。圖 4.估計的行數來驗證一下這個結論,如圖 5 所示。天善智能博客：第 7 頁 共 21 頁圖 5.估計的行數因此，可以看出，估計的行數是和實際的行數有出入的，當數據分布均勻時，或者數據量大時，這個誤差將會變的非常小。2.1.3統(tǒng)計信息更新由上面的例子可以看到，查詢分析器由于依賴于統(tǒng)計信息進行查詢，那么過時的統(tǒng)計信息則可能導致低效率的查詢。統(tǒng)計信息既可以由 SQL Server 來進行管理，也可以手動進行更新，也可以由 SQL Server 管理更新時手動更新。當開啟了自動更新后，SQL Server表中的數據更改，當達到臨界值時則會自動更新數據。這個標準是:

18、向空表數據時少于 500 行的表增加 500 行或者當表中行多于 500 行時，數據的變化量大于 20%時上述條件的滿足均會導致統(tǒng)計被更新。當然，也可以使用如下語句手動更新統(tǒng)計信息。UPDATE SISTICS 表名索引名天善智能博客：第 8 頁 共 21 頁2.1.4列級統(tǒng)計信息SQL Server 還可以針對不屬于任何索引的列創(chuàng)建統(tǒng)計信息來幫助查詢分析器獲取”估計的行數“.當級別的選項“自動創(chuàng)建統(tǒng)計信息”如圖 6 所示。開啟數據庫圖 6.自動創(chuàng)建統(tǒng)計信息當這個選項設置為 True 時，當下兩種情況例外:where 謂詞指定了不在任何索引上的列時，列的統(tǒng)計信息會被創(chuàng)建，但是會有以創(chuàng)建統(tǒng)計信息

19、的成本超過生成查詢計劃的成本當SQL Server 忙時不會自動生成統(tǒng)計信息可以通過系統(tǒng)視圖 sys.ss 來查看這些統(tǒng)計信息,如圖 7 所示。天善智能博客：第 9 頁 共 21 頁圖 7.通過系統(tǒng)視圖查看統(tǒng)計信息當然，也可以通過如下語句手動創(chuàng)建統(tǒng)計信息:CREATE S2.1.5總結ISTICS 統(tǒng)計名稱 ON 表名 (列名 ,.n)本文簡單談了統(tǒng)計信息對于查詢路徑選擇的影響。過時的統(tǒng)計信息很容易造成查詢性能的降低。因此，定期更新統(tǒng)計信息是 DBA 重要的工作之一。3非常重要附加查詢計劃(/fish-li/archive/2011/06/06/2073626.html)對于 SqlServe

20、r 的優(yōu)化來說，可能優(yōu)化查詢是很常見的事情。關于數據庫的優(yōu)化，本身也是一個涉及面比較的廣的話題，本文只談優(yōu)化查詢時如何看懂 SqlServer 查詢計劃。由于我對 SqlServer 的認識有限，時批評指正。錯誤，也懇請您在發(fā)現后及首先，打開【SQL Server Management Studio】，輸入一個查詢語句看看 SqlServer 是如何顯示查詢計劃的吧。說明：本文所演示的數據庫，是我寫的一個演示程序的數據庫， 可以在此網頁中。select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Fin

21、ished fromOrdersView as vwhere v.OrderDate = 2010-12-1 and v.OrderDate 2011-12-1;其中，OrdersView 是一個視圖，其定義如下：SELECTdbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,dbo.OrdermMoney, dbo.Orders.Finished,ISNULL(dbo.Customers.CustomerName, N) AS CustomerName dbo.Orders LEFT OUTER JOINdbo.Cu

22、stomers ON dbo.Orders.CustomerID = dbo.Customers.CustomerIDFROM對于前一句查詢，SqlServer 給出的查詢計劃如下（點擊上的【顯示估計的執(zhí)行計劃】按鈕）：天善智能博客：第 10 頁 共 21 頁從這個圖，至少可以得到 3 個有用的信息：哪些執(zhí)行步驟花費的成本比較高。顯然，最右邊的二個步驟的成本是比較高的。哪些執(zhí)行步驟產生的數據量比較多。對于每個步驟所產生的數據量， SqlServer 的執(zhí)行計劃是用【線條粗細】來表示的，因此也很容易地從分辨出來。每一步執(zhí)行了什么樣的動作。對于一個比較慢的查詢來說，通常首先要知道哪些步驟的成本比較

23、高，進而，可以嘗試一些改進的方法。一般來說，如果您不能通過：提高硬件性能或者調整 OS,SqlServer 的設置之類的方式來解決問題，那么剩下的可選方法通常也只有以下這些了：為【scan】這類操作增加相應字段的索引。有時重建索引或許也是有效的，具體情形請參考后文。調整語句結構，引導 SqlServer 采用其它的查詢方案去執(zhí)行。調整表結構（分表或者分區(qū)）。下面再來說說一些很重要的理論知識，這些內容對于執(zhí)行計劃的理解是很有幫助的。3.1Sql Server 查找的方法說到這里，不得不說 SqlServer 的索引了。SqlServer 有二種索引：索引和非?！痉撬饕?。二者的差別在于：【聚索引】

24、保存了二個信息：1.相集索引】直接決定了應索引字段的值，2.的存放位置，或者說：根據索引可以直接獲取到對應索引的位置（如果表沒有索引則保存指針）。因此，如果能通過【索引】來查找，顯然也是最快的。Sql Server 會有以下方法來查找您需要的數據1. 【Table Scan】：遍歷整個表，查找所匹配的：行。這個操作將會一行一行的檢查，當然，效率也是的。2. 【Index Scan】：根據索引，從表中過濾出來一部分因此比【Table Scan】要快。，再查找所匹配的行，顯示比第式的查找范圍要小，3. 【Index Seek】：根據索引，定位（獲取）的存放位置，然后取得，因此，比起前二種方式會更快

25、。4. 【Clustered Index Scan】：和【Table Scan】一樣。注意：不要以為這里有個 Index，就認為不一樣了。其實它的意思是說：按沒有索引來逐行掃描每一行，因為就是按索引來順序存放的。而【Table Scan】只是說：要掃描的表索引而已，因此這二個操作本質上也是一樣的。5. 【Clustered Index Seek】：直接根據索引獲取，最快！所以，當發(fā)現某個查詢比較慢時，可以首先檢查哪些操作的成本比較高，再看看那些操作是查找時，是不是【Table Scan】或者【Clustered Index Scan】，如果確實和這二種操作類型有關，則要考慮增加索引來解決了。不

26、過，增加索引后，也會影響數據表的修改動作，因為修改數據表時，要更新相應字段的索引。所以索引過多，也會影響性能。還有一種情況是不適天善智能博客：第 11 頁 共 21 頁合增加索引的：某個字段用 0 或 1 表示的狀態(tài)。例如可能有絕大多數是 1，那么此時加索引根本就沒有意義。這時只能考慮為 0 或者 1 這二種情況分開來保存了，分表或者分區(qū)都是不錯的選擇。如果不能通過增加索引和調整表來解決，那么可以試試調整語句結構，引導 SqlServer 采用其它的查詢方案去執(zhí)行。這種方法要求： 1.對語句所要完成的功能很清楚， 2.對要查詢的數據表結構很清楚， 3.對相關的業(yè)務背景知識很清楚。如果能通過這種

27、方法去解決，當然也是很好的解決方法了。不過，有時 SqlServer 比較智能，即使你調整語句結構，也不會影響它的執(zhí)行計劃。如何比較二個同樣功能的語句的性能好壞呢，我建議采用二種方法： 1. 直接把二個查詢語句放在【SQL Server ManagementStudio】，然后去看它們的【執(zhí)行計劃】，SqlServer 會以百分比的方式告訴你二個查詢的【查詢開銷】。這種方法簡單，通常也是可以參考的，不過，有時也會，具體原因請接著往下看(可能索引統(tǒng)計信息過舊)。2. 根據真實的程序調用，寫相應的測試代碼去調用：這種方法就麻煩一些，但是它更能代表現實調用情況，得到的結果也是更具有參考價值的，因此也

28、是值得的。3.2索引統(tǒng)計信息：查詢計劃的選擇依據前面一直說到【執(zhí)行計劃】，既然是計劃，就表示要在具體執(zhí)行前就能確定下來的操作方案。那么 SqlServer 是如何選擇一種執(zhí)行計劃的呢？ SqlServer 怎么知道什么時候該用索引或者用哪個索引？對于 SqlServer 來說，每當要執(zhí)行一個查詢時，都要首先檢查有沒有這個查詢的執(zhí)行計劃是否存在緩存中，如果沒有，則要生成一個執(zhí)行計劃，具體在產生執(zhí)行計劃時，并不是看有哪些索引可用（隨機選擇），而是會參考一種被稱為【索引統(tǒng)計信息】的數據。 如果您仔細地看一下前面的執(zhí)行計劃或者執(zhí)行過程表格，會發(fā)現 SqlServer 能預估每個步驟所產生的數據量，正是

29、因為 SqlServer 能預估這些數據量，SqlServer 才能選擇一個它認為最合適的方法去執(zhí)行查詢過程，此時【索引統(tǒng)計信息】就能告訴 SqlServer 這些數據。說到這里，您是不是有點好奇呢，為了對【索引統(tǒng)計信息】有個感性的認識，來看看【索引統(tǒng)計信息】是個什么樣子的。請在【SQL Server Management Studio】，輸入以下語句，然后執(zhí)行。3.3統(tǒng)計信息，指導條件選擇不同查詢計劃（重點）再來看看同一個查詢，但因為查詢參數值不同時，SqlServer 選擇的執(zhí)行計劃：天善智能博客：第 12 頁 共 21 頁從上圖可以看出，由于 CategoryId 的參數值不同，SqlS

30、erver 會選擇完全不同的執(zhí)行計劃。統(tǒng)計信息重要性在這里體現地很清楚吧。創(chuàng)建統(tǒng)計信息后，數據庫引擎對列值（根據這些值創(chuàng)建統(tǒng)計信息）進行排序，并根據這些值（最多 200 個，按間隔分隔開）創(chuàng)建一個“直方圖”。直方圖指定有多少行精確匹配每個間隔值，有多少行在間隔范圍內，以及間隔中值的密度大小或重復值的發(fā)生率。SQL Server 2005 引入了對 char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 和 ntext 列創(chuàng)建的統(tǒng)計信息收集的其他信息。這些信息稱為“字符串摘要”，可以幫助查詢優(yōu)化器估計字符串模式中查詢謂詞的選擇性。查

31、詢中有 LIKE 條件時，使用字符串摘要可以更準確地估計結果集大小，并不斷優(yōu)化查詢計劃。這些條件包括諸如 WHERE ProductName LIKE %Bike 和 WHERE Name LIKE CSheryl 之類的條件。既然【索引統(tǒng)計信息】這么重要，那么它會在什么時候生成或者更新呢？事實上，【索引統(tǒng)計信息】是不用手工去的， SqlServer 會自動去它們。而且在 SqlServer 中也有個參數來控制這個更新方式：天善智能博客：第 13 頁 共 21 頁創(chuàng)建索引時，查詢優(yōu)化器自動有關索引列的統(tǒng)計信息。另外，當 AUTO_CREATE_SISTICS 數據庫選項設置為 ON（默認值）時

32、，數據庫引擎自動為沒有用于謂詞的索引的列創(chuàng)建統(tǒng)計信息。隨著列中數據發(fā)生變化，索引和列的統(tǒng)計信息可能會過時，從而導致查詢優(yōu)化器選擇的查詢處理方法不是最佳的。例如，如果創(chuàng)建一個包含一個索引列和 1,000 行數據的表，每一行在索引列中的值都是唯一的，則查詢優(yōu)化器將把該索引列視為收集查詢數據的好方法。如果更新列中的數據后存在許多重復值，則該列不再是用于查詢的理想候選列。但是，查詢優(yōu)化器仍然根據索引的過時分布統(tǒng)計信息（基于更新前的數據），將其視為好的候選列。當 AUTO_UPDATE_SISTICS 數據庫選項設置為 ON（默認值）時，查詢優(yōu)化器會在表中的數據發(fā)生變化時自動定期更新這些統(tǒng)計信息。每當查

33、詢執(zhí)行計劃中使用的統(tǒng)計信息沒有通過針對當前統(tǒng)計信息的測試時就會啟動統(tǒng)計信息更新。采樣是在各個數據頁上隨機進行的，取自表或統(tǒng)計信息所需列的最小非索引。從磁盤一個數據頁后，該數據頁上的所有行都被用來更新統(tǒng)計信息。常規(guī)情況是：在大約有 20% 的數據行發(fā)生變化時更新統(tǒng)計信息。但是，查詢優(yōu)化器始終確保采樣的行數盡量少。對于小于 8 MB 的表，則始終進行完整掃描來收集統(tǒng)計信息。采樣數據（而不是分析所有數據）可以將統(tǒng)計信息自動更新的開銷降至最低。在某些情況下，統(tǒng)計采樣無法獲得表中數據的精確特征。可以使用 UPDATE SISTICS 語句的 SLE 子句和 FULLSCAN 子句，控制按逐個表的方式手動

34、更新統(tǒng)計信息時采樣的數據量。FULLSCAN 子句指定掃描表中的所有數據來收集統(tǒng)計信息，而 S比或采樣的行數LE 子句用來指定采樣的行數百分在 SQL Server 2005 中，數據庫選項 AUTO_UPDATE_SISTICS_ASYNC 提供了統(tǒng)計信息異步更新功能。當此選項設置為 ON 時，查詢不等待統(tǒng)計信息更新，即可進行編譯。而過期的統(tǒng)計信息置于隊列中，由進程中的工作線程來更新。查詢和任何其他并發(fā)查詢都通過使用現有的過期統(tǒng)計信息立即編譯。由于不存在等待更新后的統(tǒng)計信息的延遲，因此查詢響應時間可；但是過期的統(tǒng)計信息可能導致查詢優(yōu)化器選擇低效的查詢計劃。在更新后的統(tǒng)計信息就緒后啟動的查詢將

35、使用那些統(tǒng)計信息。這可能會導致重新編譯緩存的計劃（取決于較舊的統(tǒng)計信息版本）。如果在同一個顯式用戶事務中出現某些數據定義語言 (DDL) 語句（例如，CREATE、ALTER 和 DROP 語句），則無法更新異步統(tǒng)計信息。AUTO_UPDATE_SISTICS_ASYNC 選項設置于數據庫級別，并確定用于數據庫中所有統(tǒng)計信息的更新方法。它只適用于統(tǒng)計信息更新，而無法用于以異步方式創(chuàng)建統(tǒng)計信息。只有將 AUTO_UPDATE_SISTICS 設置為 ON 時，將此選項設置為 ON才有效。默認情況下，AUTO_UPDATE_SISTICS_ASYNC 選項設置為 OFF。天善智能博客：第 14 頁

36、 共 21 頁從以上說明中，器的判斷了。可以看出，對于大表，還是有可能存在統(tǒng)計信息更新不及時的時候，這時，就可能會影響查詢優(yōu)化有些人可能有個經驗：對于一些慢的查詢，他們會想到重建索引來嘗試解決。其實這樣做是有道理的。因為，在某些時候一個查詢突然變慢了，可能和統(tǒng)計信息更新不及時有關，進而會影響查詢優(yōu)化器的判斷。如果此時重建索引，就可以讓查詢優(yōu)化器知道的數據分布，自然就可以避開這個問題。 還記得我前面用【set sistics profile on】顯示的執(zhí)行過程表格嗎？注意哦，那個表格就顯示每個步驟的實際數據量和預估的數據量。要不要重建索引，其實可以用【setsistics profile on

37、】來看一下，如果實際數據量和預估的數據量的差值比較大，那么可以考慮手工去更新統(tǒng)計信息，然后再去試試。4高 CPU 數據庫問題如何和解決 CPU 高度消耗(100%)的數據庫問題高性能 CPU 測試總結很多時候的服務器可能會經歷 CPU 消耗 100%的性能問題.排除系統(tǒng)的異常，這類問題通常都是因為系統(tǒng)中存在性能低下甚至存在錯誤的 SQL 語句, 消耗了大量的 CPU 所致.本文通過一個案例就如何捕獲這樣的 SQL 給出一個通用的方法.問題描述:系統(tǒng) CPU 高度消耗,系統(tǒng)運行緩慢On Solaris8Oracle:Oracle9203天善智能博客：第 15 頁 共 21 頁天善

38、智能博客：第 16 頁 共 21 頁總結: 很多時候，高CPU 消耗都是由于問題 SQL 導致的,所以找到這些 SQL 通常也就找到了問題所在，通過優(yōu)化調整通常就可以解決問題。但是有時候你可能會發(fā)現，這些最消耗 CPU 的進程是導致的，需要具體問題具體分析了.進程，這一般是由于異常、BUG 或者恢復后的異常4.1.2一條 SELECT 語句導致瓶頸情況描述上周，公司一項目新上線，剛上線的第 2 天，在發(fā)現數據庫服務器與 IIS 服務器的網絡 IO 出現瓶頸，1GB 的網絡帶寬，天善智能博客：第 17 頁 共 21 頁占用了 70%-100%，也就是每秒傳輸數據 700MB-1GB，數據庫使用內

39、存高達 21GB。IIS 服務器 CPU 使用率時常爆至 80%-90%，導致頻頻出現連接超時。原因:晚上只好暫時關閉Select * From Table1，進行服務器，作全面的檢查，發(fā)現是一句 Select 語句導致：這條語句，語法是沒問題的，但在應用上出了問題。Table1的是 10 多萬行數據，表數據每天都會上萬的增長。為了統(tǒng)計總行數，頻頻調用這語句，每秒刷新不低于 1000 次。也因此導致網絡出現瓶頸。解決Select Count(*) from Table1即可解決問題，網絡 IO 數據馬上降至 10MB 以下，數據庫使用內存也保持在預計范圍 12GB。看似非常簡單，其實不然。解決

40、這問題，所花的時間周期是 6 小時，檢查問題使用 1 小時，修改代碼使用 5 小時。小結做事要細心，不要犯低級錯誤，有時候成功取決于細節(jié)。使用 SQL 語句查詢耗時最長，CPU 消耗最高，IO 阻塞查詢耗時最長的 SQL 語句/*功能說明：查詢耗時最長的SQL語句*/SELECT TOP 5 Sum(qs.total_worker_time) / 1000 AS total_cpu_time, Sum(qs.execution_count) / 1000AS total_execution_count,Count(*) qs.plan_handle, qs.sql_handlesys.dm_e

41、xec_query_s BY qs.plan_handle,qs.sql_handleAS number_of_sements,FROMGROUPs qsORDERBY Sum(qs.total_worker_time) DESCselect * fromsys.dm_exec_sql_text(0 x06002400D04F2410B8408B00000000)5.2/*查詢進程消耗 CPU 時間功能說明:線程ID、消耗CPU時間、等待資源類型*/SELECT ses_id,cpu_time, wait_TypeFROMsys.dm_exec_requestsORDER BY cpu_tim

42、e DESC-dbcc inputbuffer(62)-kill 641天善智能博客：第 18 頁 共 21 頁5.3/*查詢 SQL IO 阻塞查詢功能說明:SQL的IO阻塞者的查詢*/SELECT blocking_seswait_duration_ms,_id,ses_idFROMsys.dm_os_waiting_tasksWHERE blocking_ses-dbcc INPUTBUFFER(60)_id IS NOT NULL5.4/*查詢 SQL 當前執(zhí)行最多的語句功能說明:SQL當前執(zhí)行的最多的語句*/SELECT execution_count,( total_elapsed

43、_time / execution_count / 1000 ) avg_time, textFROMsys.dm_exec_query_ss qsCROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) AS stBY execution_count DESCORDER5.5/*功能說明：數據查詢數據庫各個表的過程空間空間的查詢邏輯說明：數據庫的大小，表空間的大小*/IF NOT EXISTS (SELECTFROMd WHERE id*ysobjects= Object_id(Ndbo.tablespaceinfo)AND Objectproperty(id,CREATE TABLE tablespaceinfo -創(chuàng)建結果 (NIsUserTable) = 1)表nameinforowsinfoVARCHAR(50), VARCHAR(20), VARCHAR(20),datainfoindex_size VARCHAR(20),unusedVARCH