12理解執(zhí)行計(jì)劃與緩存

1、執(zhí)行計(jì)劃王輝內(nèi)容執(zhí)行計(jì)劃執(zhí)行計(jì)劃緩存編譯與重編譯和執(zhí)行有關(guān)的DMV為什么需要執(zhí)行計(jì)劃查詢執(zhí)行階段什么是執(zhí)行計(jì)劃?常見的執(zhí)行計(jì)劃元素實(shí)際的 vs. 評估的執(zhí)行計(jì)劃案例：分析執(zhí)行計(jì)劃執(zhí)行計(jì)劃常見問題為什么我的查詢要花這么長時(shí)間執(zhí)行?為什么這個(gè)查詢執(zhí)行的時(shí)間要長于類似查詢執(zhí)行的時(shí)間?為什么SQL Server忽略了我剛創(chuàng)建的很適合的索引?執(zhí)行計(jì)劃可以幫助你驗(yàn)證查詢是否按照預(yù)期執(zhí)行，分析執(zhí)行情況為什么需要執(zhí)行計(jì)劃SQL Server 關(guān)系數(shù)據(jù)庫服務(wù)器存儲(chǔ)引擎 (訪問方法, 數(shù)據(jù)庫頁緩存, 鎖定, 事務(wù), )SQL-OS (任務(wù)計(jì)劃, 緩存池, 內(nèi)存管理, 同步單元, )查詢優(yōu)化(生成執(zhí)行計(jì)劃, 選擇索

2、引, 聯(lián)接次序, 統(tǒng)計(jì)信息, 開銷)查詢執(zhí)行(查詢運(yùn)算符, 內(nèi)存授予, 并行處理)語言處理(分析/綁定/視圖解析)工具 (DBCC, Backup / Restore, BCP, )Metadata, Type system, Expression Services語句/批執(zhí)行計(jì)劃緩存管理SQL Server 關(guān)系數(shù)據(jù)庫服務(wù)器查詢優(yōu)化器查詢生命周期概述查找計(jì)劃緩存重用或生成執(zhí)行計(jì)劃內(nèi)存授予, 并行處理執(zhí)行找到執(zhí)行計(jì)劃找到已編譯的計(jì)劃未找到自動(dòng)參數(shù)化綁定， 視圖解析分析查詢優(yōu)化將計(jì)劃放入緩存新語句查詢優(yōu)化(生成執(zhí)行計(jì)劃, 選擇索引, 聯(lián)接次序, 統(tǒng)計(jì)信息, 開銷)查詢執(zhí)行(查詢運(yùn)算符, 內(nèi)存授予

3、, 并行處理,顯示執(zhí)行計(jì)劃)語言處理(分析/綁定,語句/批執(zhí)行,計(jì)劃緩存管理)Query Optimization deep dive in earlier Session查詢優(yōu)化SQL Server 查詢優(yōu)化器是基于開銷的優(yōu)化器而不是基于規(guī)則的 例如: Oracle 7 之前版本和部分并行數(shù)據(jù)倉庫輸入: 已做如下操作的查詢樹:對SQL語句進(jìn)行語法解析語義分析及檢查 (也稱為Algebrizing 包含類型推導(dǎo)，類型檢查，綁定變量，表名及字段名等）輸出: 一個(gè)（希望是）好的查詢計(jì)劃SQL Server的執(zhí)行計(jì)劃中包含：操作的類型操作的順序索引的選擇基于可用統(tǒng)計(jì)值評估的行計(jì)數(shù)SQL Server

4、 執(zhí)行基于開銷的優(yōu)化成本是指派給每一個(gè)元素的什么是執(zhí)行計(jì)劃?一個(gè)執(zhí)行計(jì)劃會(huì)顯示查詢時(shí)如何執(zhí)行的或者它將如何被執(zhí)行顯示查詢計(jì)劃3種形式: 文本, XML , 圖形化按內(nèi)容分: 僅運(yùn)算符, 包含開銷和基數(shù)估計(jì)，包含開銷和基數(shù)估計(jì)和運(yùn)行時(shí)信息SQL Trace/Profiler 在 Performance 事件類中有 8 個(gè) Showplan 事件文本XML圖形化運(yùn)算符SET SHOWPLAN_TEXT ONN/AN/A包含開銷和基數(shù)估計(jì)SET SHOWPLAN_ALL ONSETSHOWPLAN_XML ON單擊查詢編輯器工具欄上的“顯示估計(jì)的執(zhí)行計(jì)劃”包含運(yùn)行時(shí)信息SET STATISTICS

5、PROFILE ONSET STATISTICS XML ON 單擊查詢編輯器工具欄上的“包括實(shí)際的執(zhí)行計(jì)劃”數(shù)據(jù)訪問操作數(shù)據(jù)組織堆(Heap)B樹訪問數(shù)據(jù)掃描（Scan）查找（Seek）表和聚集索引掃描和查找嵌套循環(huán)和查找合并和哈希連接聚合篩選和排序數(shù)據(jù)修改常見的執(zhí)行計(jì)劃元素表掃描讀取整個(gè)堆聚集索引掃描讀取表的整個(gè)聚集索引聚集索引查找從表中聚集索引的的特定位置讀取表和聚集索引查找和掃描嵌套循環(huán)常用于內(nèi)連接操作對數(shù)據(jù)路徑中頂部每一行，在數(shù)據(jù)路徑的底部執(zhí)行一次查找RID 查找使用行編號查找堆鍵值查找查找一個(gè)聚集索引嵌套循環(huán)和查找合并連接常用于內(nèi)連接需要兩個(gè)輸入使用相同的排序順序哈希匹配效率較低的

6、連接，通過對一個(gè)輸入的每一行計(jì)算哈希值， 然后在內(nèi)存中創(chuàng)建哈希表保存哈希值其他輸入則對哈希表進(jìn)行查找合并和哈希連接流聚合高效數(shù)據(jù)已經(jīng)排好序用于處理聚合哈希匹配聚合通過哈希表來對不必須排序的數(shù)據(jù)進(jìn)行聚合聚合過濾低成本操作當(dāng)用 WHERE 或者用 HAVING 謂詞時(shí)用到僅傳輸滿足過濾條件的行排序當(dāng)必須要排序操作的時(shí)候經(jīng)常使用Order By可以被用于其他操作，例如合并連接操作的排序輸入或者執(zhí)行 DISTINCT 操作開銷非常大過濾和排序INSERT用于 INSERT 操作UPDATE用于 UPDATE 操作DELETE用于 DELETE 操作T-SQL MERGE 語句可以合并插入、刪除、更新。

7、數(shù)據(jù)修改案例：分析執(zhí)行計(jì)劃SELECT TOP 9 PlateForum.Title,Tag,idx as fidx,groupidx FROM PlateForum with(nolock) WHERE CONTAINS(Tag,朋友) and state2 order by NEWID()案例：分析執(zhí)行計(jì)劃(續(xù)）改寫：SELECT FT_TBL.Title,Tag,idx as fidx,groupidx FROM PlateForum AS FT_TBL INNER JOINCONTAINSTABLE (PlateForum, Tag, 朋友, 9) AS KEY_TBL ON FT_T

8、BL.IDX = KEY_TBL.KEY WHERE state2 and RANK100 ORDER BY NEWID()什么會(huì)影響查詢優(yōu)化器的選擇?查詢優(yōu)化統(tǒng)計(jì)信息索引和約束內(nèi)存CPU核數(shù)詢問SQL Server如何執(zhí)行這個(gè)查詢稱為評估的執(zhí)行計(jì)劃SQL Server 不會(huì)執(zhí)行這個(gè)查詢，只是估算該如何執(zhí)行并不總是能獲得評估的執(zhí)行計(jì)劃實(shí)際的計(jì)劃可能和評估的計(jì)劃不一樣過期的統(tǒng)計(jì)值丟失統(tǒng)計(jì)值實(shí)際的并行度評估的計(jì)劃基于統(tǒng)計(jì)值返回評估的計(jì)數(shù)根據(jù)步長也有可能根據(jù)數(shù)據(jù)密度算一個(gè)統(tǒng)計(jì)的平均值實(shí)際 vs. 評估的執(zhí)行計(jì)劃查詢優(yōu)化統(tǒng)計(jì)信息用于估計(jì)開銷直方圖包含列中值的分布情況最大梯級數(shù)200用來估計(jì)查詢謂詞的基

9、數(shù)直方圖只有第一列的值密度每個(gè)唯一值在列中出現(xiàn)的重復(fù)次數(shù)用來估計(jì)查詢謂詞、聯(lián)接、聚合的基數(shù)字符串索引中單個(gè)詞的統(tǒng)計(jì)信息 SQL Server 2005新功能查看統(tǒng)計(jì)信息DBCC SHOW_STATISTICS 統(tǒng)計(jì)信息如何取得讀取字段的記錄值建立一個(gè)排序過的數(shù)據(jù)分布信息利用完整掃描或是抽樣來取記錄由數(shù)據(jù)表內(nèi)的記錄數(shù)目動(dòng)態(tài)決定抽樣記錄所占的百分率選擇抽樣從數(shù)據(jù)表或是擁有讀取統(tǒng)計(jì)所需字段的最小非簇索引隨機(jī)選擇數(shù)據(jù)頁，選到的數(shù)據(jù)頁取其內(nèi)部所有的記錄來更新統(tǒng)計(jì)信息創(chuàng)建和更新統(tǒng)計(jì)信息CREATE / UPDATE STATISTICS可以執(zhí)行掃描所有行或者抽樣行數(shù)百分比或行數(shù)缺省抽樣行數(shù)與總行數(shù)呈對數(shù)關(guān)

10、系CREATE STATISTICS, sp_updatestats默認(rèn)情況下，SQL Server自動(dòng)創(chuàng)建和更新統(tǒng)計(jì)信息一般地，使用默認(rèn)設(shè)置即可自動(dòng)創(chuàng)建/更新統(tǒng)計(jì)信息數(shù)據(jù)庫級別數(shù)據(jù)庫選項(xiàng)AUTO_CREATE_STATISTICS 自動(dòng)創(chuàng)建謂詞所使用的列的統(tǒng)計(jì)信息AUTO_UPDATE_STATISTICS 何時(shí)? 普通表: 500; 500 + 20% 行數(shù)改變后臨時(shí)表: 6; 500; 500 + 20% 行數(shù)改變后當(dāng)心:自動(dòng)更新總是使用缺省抽樣行數(shù),即使創(chuàng)建統(tǒng)計(jì)信息時(shí)使用FULLSCAN指定全表掃描。創(chuàng)建索引時(shí)創(chuàng)建的統(tǒng)計(jì)信息會(huì)使用全表掃描，但以后該統(tǒng)計(jì)信息的自動(dòng)更新也是使用缺省抽樣行數(shù)

11、。自動(dòng)更新統(tǒng)計(jì)信息表級別可以對表/索引禁用自動(dòng)更新統(tǒng)計(jì)信息使用sp_autostats禁用后必須手動(dòng)維護(hù)統(tǒng)計(jì)信息例如：ETL加載后或批量更新后不會(huì)對表變量自動(dòng)創(chuàng)建和更新統(tǒng)計(jì)信息你也不能手動(dòng)對表變量創(chuàng)建統(tǒng)計(jì)信息查詢優(yōu)化器總是假定表變量只有一行記錄統(tǒng)計(jì)信息存在的問題統(tǒng)計(jì)信息用于給每個(gè)查詢操作提供基數(shù)估計(jì) (CE)過濾, 聯(lián)接, 分組聚合, 以圖形方式顯示查詢的執(zhí)行計(jì)劃時(shí)，缺少的統(tǒng)計(jì)信息將予以警告顯示（表名稱以紅色文本顯示）。局限在多次聯(lián)接/分組聚合等操作后，基數(shù)估計(jì)的準(zhǔn)確性可能會(huì)降低隱性的相關(guān)性很難處理數(shù)據(jù)的相關(guān)性: 兩列被關(guān)聯(lián)謂詞的相關(guān)性: 多個(gè)相關(guān)的篩選條件數(shù)據(jù)的不均衡性會(huì)影響基數(shù)估計(jì), 尤其

12、是采用抽樣處理時(shí)有時(shí)很難從抽樣中正確猜斷數(shù)據(jù)密度更新統(tǒng)計(jì)信息會(huì)導(dǎo)致查詢重新編譯。統(tǒng)計(jì)信息最佳實(shí)踐關(guān)閉自動(dòng)更新的理由 減少不必要的重編譯 如果你確定數(shù)據(jù)更新不會(huì)影響數(shù)據(jù)的分布密度不希望影響忙時(shí)的數(shù)據(jù)庫性能 在閑時(shí)定期更新有時(shí)可以使用全表掃描來更新統(tǒng)計(jì)信息不均衡的數(shù)據(jù)分布 在閑時(shí)定期更新（有足夠的時(shí)間和I/O）查詢的編譯/重編譯會(huì)等待統(tǒng)計(jì)信息的自動(dòng)更新考慮 AUTO_UPDATE_STATISTICS_ASYNC 數(shù)據(jù)庫選項(xiàng)對于大型表考慮采用篩選統(tǒng)計(jì)信息（SQL 2008)對大型分區(qū)表考慮采用分區(qū)統(tǒng)計(jì)信息(SQL 2014)統(tǒng)計(jì)信息 升級要考慮的事從 SQL Server 2000 升級所有SQL

13、 Server 2000 的統(tǒng)計(jì)信息都被認(rèn)為是過期的，自動(dòng)更新程序（如果未禁用）會(huì)處理這些統(tǒng)計(jì)信息最佳實(shí)踐：在升級后手動(dòng)更新全部統(tǒng)計(jì)信息如果統(tǒng)計(jì)信息不是自動(dòng)更新的，那么升級后需要手動(dòng)更新sp_updatestats resample = resample更細(xì)節(jié)的描述請參見 從 SQL Server 2005 升級不需要升級統(tǒng)計(jì)信息演示：基數(shù)估計(jì)的不準(zhǔn)確性執(zhí)行計(jì)劃優(yōu)化最佳實(shí)踐收集“實(shí)際的執(zhí)行計(jì)劃”比較“估計(jì)的”和“實(shí)際的” 行數(shù)較大的差異意味著這里可能出現(xiàn)問題留意執(zhí)行警告 聯(lián)機(jī)叢書描述了以下幾種情況的補(bǔ)救方法Sort Warning 排序操作的內(nèi)存不足Exchange Spill 并行查詢計(jì)劃中

14、的通信緩沖區(qū)已暫時(shí)寫入 tempdb 數(shù)據(jù)庫Hash Warning 哈希操作過程中發(fā)生哈希遞歸或哈希援助Bitmap Warning已在查詢中禁用位圖篩選內(nèi)容執(zhí)行計(jì)劃編譯與重編譯執(zhí)行計(jì)劃緩存和執(zhí)行有關(guān)的DMV查詢編譯語法檢查規(guī)范化編譯優(yōu)化參數(shù)化這是什么?在 SQL Server 2000 “自動(dòng)參數(shù)化”只能用于非常簡單的查詢在SQL 2005 中被稱為簡單參數(shù)化研究查詢計(jì)劃時(shí)，會(huì)發(fā)現(xiàn)“參數(shù)”突然出現(xiàn)了。如下：查詢: select * from T where c=5計(jì)劃: |-Table Scan(OBJECT:(tst1.dbo.T), WHERE:(T.C=Convert(1)好處: 當(dāng)

15、同樣的查詢，參數(shù)不再是“5”時(shí)，不需要重編譯查詢執(zhí)行計(jì)劃緩存依靠查詢語句本身來判別緩存，所以只有使得執(zhí)行計(jì)劃緩存中的查詢語句一模一樣，才能重用執(zhí)行計(jì)劃方法：參數(shù)化強(qiáng)制參數(shù)化SP_EXECUTESQL存儲(chǔ)過程強(qiáng)制參數(shù)化 (示例)查詢: select * from T where c=1 or c=2計(jì)劃: |-Table Scan(OBJECT:(tt.dbo.T), WHERE:(tt.dbo.T.c=(1) OR tt.dbo.T.c=(2)ALTER DATABASE SET PARAMETERIZATION FORCED計(jì)劃: |-Table Scan(OBJECT:(tt.dbo.T)

16、, WHERE:(tt.dbo.T.c=0 OR tt.dbo.T.c=1)ALTER DATABASE SET PARAMETERIZATION SIMPLE放入存儲(chǔ)過程后帶來的矛盾當(dāng)把查詢放到存儲(chǔ)過程中后，參數(shù)無法被嗅探到由于執(zhí)行計(jì)劃緩存重用的特性，而導(dǎo)致不同選擇性的查詢使用了同種執(zhí)行計(jì)劃高選擇性-非簇索引查找低選擇性-簇索引查找這導(dǎo)致了低選擇性的查詢執(zhí)行性能較差平衡參數(shù)化和非參數(shù)化對語句可以使用查詢提示：OPTION( PILE)OPTION(Optimize For參數(shù))對存儲(chǔ)過程可以使用：SP_ PILEWITH PILE 創(chuàng)建時(shí)使用WITH PILE 執(zhí)行是使用使用局部變量代替存儲(chǔ)

17、過程參數(shù)使用計(jì)劃指南使用決策樹存儲(chǔ)過程優(yōu)化器查詢提示分類SELECT ProductName, Products.ProductIDFROM dbo.Order Details WITH (INDEX(0) INNER LOOP JOIN dbo.ProductsON Order Details.ProductID = Products.ProductIDWHERE Products.UnitPrice 100 OPTION ( PILE, LOOP JOIN)表提示聯(lián)接提示查詢提示優(yōu)化器提示示例表提示: INDEX (n) | (name)SQL 2008開始可以強(qiáng)制使用索引查找操作聯(lián)接順序

18、，方法FORCE ORDER 提示但是不能跨子查詢指定順序，或把聚合放到聯(lián)接之下 FORCE ORDER 總是查詢級別的LOOP | MERGE | HASH JOIN 可以指定整個(gè)查詢級別，也可指定單個(gè)聯(lián)接注意:如果在為單個(gè)聯(lián)接指定了聯(lián)接提示，則盡管仍須遵守查詢提示，但該聯(lián)接提示將優(yōu)先聯(lián)接這兩個(gè)表。聚合HASH GROUP = 使用哈希聚合ORDER GROUP = 使用流聚合更多信息請參閱聯(lián)機(jī)叢書SQL 2005及以上版本新增的提示 PILE 提示，可對單個(gè)查詢，而不是存儲(chǔ)過程下面的提示可以更多的來控制查詢計(jì)劃 OPTIMIZE FOR 可在優(yōu)化查詢時(shí)指示查詢優(yōu)化器對局部變量使用特定值US

19、E PLAN 強(qiáng)制使用特定查詢計(jì)劃PARAMETERIZATION 提示 可在數(shù)據(jù)庫級別設(shè)置也可在計(jì)劃指南中指定計(jì)劃指南FORCESEEK SQL 2008 新增MAXDOP SQL 2008 新增 PILE 提示SQL Server 2000: CREATE PROCEDURE WITH PILESQL Server 2005 及更高版本, 單個(gè)語句可以使用OPTION ( PILE)SQL 2000 如果需要為了變量的當(dāng)前值重編譯執(zhí)行計(jì)劃，只能使用動(dòng)態(tài) SQL 語句或重編譯整個(gè)存儲(chǔ)過程更簡單的語法No need to grant access to all referenced objec

20、ts; execute on the sp sufficesOPTIMIZE FOR 提示OPTION ( OPTIMIZE FOR ( variable_name = literal_constant ,n ) )僅在查詢優(yōu)化期間使用該變量值，在查詢執(zhí)行期間不使用該值可以抵消優(yōu)化器的默認(rèn)參數(shù)檢測行為 可以更好的實(shí)現(xiàn)行為可預(yù)測性使用時(shí)應(yīng)對數(shù)據(jù)的情況有足夠的了解USE PLAN 提示可以使執(zhí)行計(jì)劃固定下來用法: 在USE PLAN后面使用捕獲的 XML 執(zhí)行計(jì)劃，使優(yōu)化器總是使用相同的計(jì)劃示例:SELECT * FROM t OPTION (USE PLAN N )43USE PLAN 提示指定

21、了什么計(jì)劃的結(jié)構(gòu)和計(jì)算的順序執(zhí)行算法索引方案查詢中用到的對象限制SQL 2005: 不能用于 INSERT/UPDATE/DELETESQL 2008: 可以!不能用于分布式查詢不能用于全文查詢只支持支持靜態(tài)游標(biāo)和快速只進(jìn)游標(biāo)計(jì)劃指南 概念查詢之外的提示查詢文本無法或不希望被直接更改時(shí)，可以使用計(jì)劃指南來優(yōu)化查詢性能查詢被編碼在應(yīng)用程序中當(dāng)執(zhí)行查詢時(shí)，SQL Server 將 Transact-SQL 語句與計(jì)劃指南進(jìn)行匹配，然后在運(yùn)行時(shí)將此 OPTION 子句附加到查詢，或使用指定的查詢計(jì)劃。計(jì)劃指南功能概述創(chuàng)建: sp_create_plan_guide刪除、啟用或禁用: sp_contr

22、ol_plan_guide匹配: 在模塊編譯時(shí)(存儲(chǔ)過程, DML 觸發(fā)器,表值用戶定義函數(shù), 標(biāo)量用戶自定義函數(shù))在批處理編譯時(shí)目標(biāo)原始用戶查詢簡單或強(qiáng)制參數(shù)化查詢計(jì)劃指南使用說明 應(yīng)用程序很難修改在用戶提交的一個(gè)批處理中,強(qiáng)制一個(gè)查詢使用MERGE JOIN強(qiáng)制優(yōu)化器總是使用“最壞情況”的參數(shù)值進(jìn)行優(yōu)化 想避免所有語法上與前一個(gè)查詢相同而只是常量文字值不同的查詢的編譯開銷在計(jì)劃指南中指定FORCED PARAMETERIZATION為手動(dòng)參數(shù)化的查詢指定特定的計(jì)劃SQL 2008 中的計(jì)劃指南固定查詢計(jì)劃 可以直接從計(jì)劃緩存中的查詢計(jì)劃創(chuàng)建計(jì)劃指南sp_create_plan_guide_

23、from_handlesys.fn_validate_plan_guide 函數(shù)可以驗(yàn)證查詢計(jì)劃是否仍然有效 (比如在表結(jié)構(gòu)變化后)SQL Profiler 事件：Plan_guide_successful 和 Plan_guide_unsuccessful在 Performance 分類中性能計(jì)數(shù)器： Guided Plan Executions/sec 和 Misguided Plan Executions/sec 提供與SQL Profiler 事件類似的功能存儲(chǔ)過程自動(dòng)重編譯的原因架構(gòu)變化統(tǒng)計(jì)變化延期編譯Set屬性改變臨時(shí)表改變存儲(chǔ)過程創(chuàng)建時(shí)使用了 PLIE查詢提示或使用了OPTION （ PILE）監(jiān)視重編譯性能計(jì)數(shù)器SQLServer: SQL Statistics: Batch Requests/secSQLServer: SQL Statistics: SQL Compilations/secSQLServer: SQL Statistics: SQL pilations/secSQL Profiler：SP: pile，Cursor pile SQL:Stmt pile 內(nèi)容執(zhí)行計(jì)劃編譯