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

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

2、種網(wǎng)絡(luò)媒介上免費(fèi)讓大家，和學(xué)習(xí)。最難得的是，這些資料是網(wǎng)絡(luò)上最獨(dú)有，最全，最有含金量的，并整理的井井有序，免費(fèi)開(kāi)放了給大家。這樣做的目的，完全是因?yàn)樘焐企w諒一些大學(xué)生、初學(xué)者想學(xué)習(xí) BI 技術(shù)，但是又苦于學(xué)習(xí)無(wú)門(mén)的心情，因?yàn)樘焐频乃谐蓡T也經(jīng)歷過(guò)一段這樣痛苦的時(shí)間，因此想通過(guò)這些小小的行為幫助到那些 BI 的初學(xué)者。俗話說(shuō)：“授之以魚(yú)不如授之以漁”，天善智能雖然提供了這么多的資料供大家免費(fèi)學(xué)習(xí)，但是天善認(rèn)為僅僅做到這一點(diǎn)是不夠的，更好的方式應(yīng)該是讓大家掌握一種無(wú)形的本領(lǐng)。這種本領(lǐng)可以描述成“如何思考、如何學(xué)習(xí)、如何解決問(wèn)題、如果沉淀、如何成長(zhǎng)”等。也許您會(huì)覺(jué)得有點(diǎn)虛無(wú)縹緲，但是請(qǐng)相信天善智能，

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

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

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

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

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

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

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

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

11、CAN ,PUTE 2）如果希望在當(dāng)前數(shù)據(jù)庫(kù)中更新所有的統(tǒng)計(jì)信息，可以使用系統(tǒng)系統(tǒng)過(guò)程 sp_updatess。該過(guò)程在SQL Server2005 中進(jìn)行了改善，只更新必要的（當(dāng)數(shù)據(jù)發(fā)生改變時(shí)）統(tǒng)計(jì)信息。不會(huì)更新未改變數(shù)據(jù)的統(tǒng)計(jì)信息。示例：Exec sp_updatess;執(zhí)行后的結(jié)果：天善智能博客：第 3 頁(yè) 共 21 頁(yè).（省略部分結(jié)果）正在更新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 個(gè)索引/統(tǒng)計(jì)資料，個(gè)不需要更新。正在更新dbo.ESERVICE_DW_K_TBL_WA_Sys_00000001_7EECB764已更新. _WA_Sys_00000002_7EECB764已更新.已更新 2 個(gè)索引/統(tǒng)計(jì)資料，個(gè)不需要更新。正在更新dbo.JOB_TYPE PK_JOB_TYPE，不需要更新.已更新 0 個(gè)索引/統(tǒng)計(jì)資料，個(gè)不需要更新。正在更新dbo.ESERCICE_DW_M_DESIGN_RELATION_TBL已更新 0 個(gè)

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

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

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

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

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

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

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

20、r 的優(yōu)化來(lái)說(shuō)，可能優(yōu)化查詢是很常見(jiàn)的事情。關(guān)于數(shù)據(jù)庫(kù)的優(yōu)化，本身也是一個(gè)涉及面比較的廣的話題，本文只談優(yōu)化查詢時(shí)如何看懂 SqlServer 查詢計(jì)劃。由于我對(duì) SqlServer 的認(rèn)識(shí)有限，時(shí)批評(píng)指正。錯(cuò)誤，也懇請(qǐng)您在發(fā)現(xiàn)后及首先，打開(kāi)【SQL Server Management Studio】，輸入一個(gè)查詢語(yǔ)句看看 SqlServer 是如何顯示查詢計(jì)劃的吧。說(shuō)明：本文所演示的數(shù)據(jù)庫(kù)，是我寫(xiě)的一個(gè)演示程序的數(shù)據(jù)庫(kù)， 可以在此網(wǎng)頁(yè)中。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 是一個(gè)視圖，其定義如下：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對(duì)于前一句查詢，SqlServer 給出的查詢計(jì)劃如下（點(diǎn)擊上的【顯示估計(jì)的執(zhí)行計(jì)劃】按鈕）：天善智能博客：第 10 頁(yè) 共 21 頁(yè)從這個(gè)圖，至少可以得到 3 個(gè)有用的信息：哪些執(zhí)行步驟花費(fèi)的成本比較高。顯然，最右邊的二個(gè)步驟的成本是比較高的。哪些執(zhí)行步驟產(chǎn)生的數(shù)據(jù)量比較多。對(duì)于每個(gè)步驟所產(chǎn)生的數(shù)據(jù)量， SqlServer 的執(zhí)行計(jì)劃是用【線條粗細(xì)】來(lái)表示的，因此也很容易地從分辨出來(lái)。每一步執(zhí)行了什么樣的動(dòng)作。對(duì)于一個(gè)比較慢的查詢來(lái)說(shuō)，通常首先要知道哪些步驟的成本比較

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

24、保存了二個(gè)信息：1.相集索引】直接決定了應(yīng)索引字段的值，2.的存放位置，或者說(shuō)：根據(jù)索引可以直接獲取到對(duì)應(yīng)索引的位置（如果表沒(méi)有索引則保存指針）。因此，如果能通過(guò)【索引】來(lái)查找，顯然也是最快的。Sql Server 會(huì)有以下方法來(lái)查找您需要的數(shù)據(jù)1. 【Table Scan】：遍歷整個(gè)表，查找所匹配的：行。這個(gè)操作將會(huì)一行一行的檢查，當(dāng)然，效率也是的。2. 【Index Scan】：根據(jù)索引，從表中過(guò)濾出來(lái)一部分因此比【Table Scan】要快。，再查找所匹配的行，顯示比第式的查找范圍要小，3. 【Index Seek】：根據(jù)索引，定位（獲?。┑拇娣盼恢?，然后取得，因此，比起前二種方式會(huì)更快

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

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

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

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

29、因?yàn)?SqlServer 能預(yù)估這些數(shù)據(jù)量，SqlServer 才能選擇一個(gè)它認(rèn)為最合適的方法去執(zhí)行查詢過(guò)程，此時(shí)【索引統(tǒng)計(jì)信息】就能告訴 SqlServer 這些數(shù)據(jù)。說(shuō)到這里，您是不是有點(diǎn)好奇呢，為了對(duì)【索引統(tǒng)計(jì)信息】有個(gè)感性的認(rèn)識(shí)，來(lái)看看【索引統(tǒng)計(jì)信息】是個(gè)什么樣子的。請(qǐng)?jiān)凇維QL Server Management Studio】，輸入以下語(yǔ)句，然后執(zhí)行。3.3統(tǒng)計(jì)信息，指導(dǎo)條件選擇不同查詢計(jì)劃（重點(diǎn)）再來(lái)看看同一個(gè)查詢，但因?yàn)椴樵儏?shù)值不同時(shí)，SqlServer 選擇的執(zhí)行計(jì)劃：天善智能博客：第 12 頁(yè) 共 21 頁(yè)從上圖可以看出，由于 CategoryId 的參數(shù)值不同，SqlS

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

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

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

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

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

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

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

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

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

39、存高達(dá) 21GB。IIS 服務(wù)器 CPU 使用率時(shí)常爆至 80%-90%，導(dǎo)致頻頻出現(xiàn)連接超時(shí)。原因:晚上只好暫時(shí)關(guān)閉Select * From Table1，進(jìn)行服務(wù)器，作全面的檢查，發(fā)現(xiàn)是一句 Select 語(yǔ)句導(dǎo)致：這條語(yǔ)句，語(yǔ)法是沒(méi)問(wèn)題的，但在應(yīng)用上出了問(wèn)題。Table1的是 10 多萬(wàn)行數(shù)據(jù)，表數(shù)據(jù)每天都會(huì)上萬(wàn)的增長(zhǎng)。為了統(tǒng)計(jì)總行數(shù)，頻頻調(diào)用這語(yǔ)句，每秒刷新不低于 1000 次。也因此導(dǎo)致網(wǎng)絡(luò)出現(xiàn)瓶頸。解決Select Count(*) from Table1即可解決問(wèn)題，網(wǎng)絡(luò) IO 數(shù)據(jù)馬上降至 10MB 以下，數(shù)據(jù)庫(kù)使用內(nèi)存也保持在預(yù)計(jì)范圍 12GB?？此品浅：?jiǎn)單，其實(shí)不然。解決

40、這問(wèn)題，所花的時(shí)間周期是 6 小時(shí)，檢查問(wèn)題使用 1 小時(shí)，修改代碼使用 5 小時(shí)。小結(jié)做事要細(xì)心，不要犯低級(jí)錯(cuò)誤，有時(shí)候成功取決于細(xì)節(jié)。使用 SQL 語(yǔ)句查詢耗時(shí)最長(zhǎng)，CPU 消耗最高，IO 阻塞查詢耗時(shí)最長(zhǎng)的 SQL 語(yǔ)句/*功能說(shuō)明：查詢耗時(shí)最長(zhǎng)的SQL語(yǔ)句*/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/*查詢進(jìn)程消耗 CPU 時(shí)間功能說(shuō)明:線程ID、消耗CPU時(shí)間、等待資源類(lèi)型*/SELECT ses_id,cpu_time, wait_TypeFROMsys.dm_exec_requestsORDER BY cpu_tim

42、e DESC-dbcc inputbuffer(62)-kill 641天善智能博客：第 18 頁(yè) 共 21 頁(yè)5.3/*查詢 SQL IO 阻塞查詢功能說(shuō)明: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 當(dāng)前執(zhí)行最多的語(yǔ)句功能說(shuō)明:SQL當(dāng)前執(zhí)行的最多的語(yǔ)句*/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/*功能說(shuō)明：數(shù)據(jù)查詢數(shù)據(jù)庫(kù)各個(gè)表的過(guò)程空間空間的查詢邏輯說(shuō)明：數(shù)據(jù)庫(kù)的大小，表空間的大小*/IF NOT EXISTS (SELECTFROMd WHERE id*ysobjects= Object_id(Ndbo.tablespaceinfo)AND Objectproperty(id,CREATE TABLE tablespaceinfo -創(chuàng)建結(jié)果 (NIsUserTable) = 1)表nameinforowsinfoVARCHAR(50), VARCHAR(20), VARCHAR(20),datainfoindex_size VARCHAR(20),unusedVARCH