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

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

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

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

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

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

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

7、數(shù)據(jù)修改案例：分析執(zhí)行計劃SELECT TOP 9 PlateForum.Title,Tag,idx as fidx,groupidx FROM PlateForum with(nolock) WHERE CONTAINS(Tag,朋友) and state2 order by NEWID()案例：分析執(zhí)行計劃(續(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()什么會影響查詢優(yōu)化器的選擇?查詢優(yōu)化統(tǒng)計信息索引和約束內(nèi)存CPU核數(shù)詢問SQL Server如何執(zhí)行這個查詢稱為評估的執(zhí)行計劃SQL Server 不會執(zhí)行這個查詢，只是估算該如何執(zhí)行并不總是能獲得評估的執(zhí)行計劃實際的計劃可能和評估的計劃不一樣過期的統(tǒng)計值丟失統(tǒng)計值實際的并行度評估的計劃基于統(tǒng)計值返回評估的計數(shù)根據(jù)步長也有可能根據(jù)數(shù)據(jù)密度算一個統(tǒng)計的平均值實際 vs. 評估的執(zhí)行計劃查詢優(yōu)化統(tǒng)計信息用于估計開銷直方圖包含列中值的分布情況最大梯級數(shù)200用來估計查詢謂詞的基

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

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

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

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

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

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

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

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

17、過程參數(shù)使用計劃指南使用決策樹存儲過程優(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開始可以強制使用索引查找操作聯(lián)接順序

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

19、E PLAN 強制使用特定查詢計劃PARAMETERIZATION 提示 可在數(shù)據(jù)庫級別設置也可在計劃指南中指定計劃指南FORCESEEK SQL 2008 新增MAXDOP SQL 2008 新增 PILE 提示SQL Server 2000: CREATE PROCEDURE WITH PILESQL Server 2005 及更高版本, 單個語句可以使用OPTION ( PILE)SQL 2000 如果需要為了變量的當前值重編譯執(zhí)行計劃，只能使用動態(tài) SQL 語句或重編譯整個存儲過程更簡單的語法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)化器的默認參數(shù)檢測行為 可以更好的實現(xiàn)行為可預測性使用時應對數(shù)據(jù)的情況有足夠的了解USE PLAN 提示可以使執(zhí)行計劃固定下來用法: 在USE PLAN后面使用捕獲的 XML 執(zhí)行計劃，使優(yōu)化器總是使用相同的計劃示例:SELECT * FROM t OPTION (USE PLAN N )43USE PLAN 提示指定

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

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

23、from_handlesys.fn_validate_plan_guide 函數(shù)可以驗證查詢計劃是否仍然有效 (比如在表結(jié)構(gòu)變化后)SQL Profiler 事件：Plan_guide_successful 和 Plan_guide_unsuccessful在 Performance 分類中性能計數(shù)器： Guided Plan Executions/sec 和 Misguided Plan Executions/sec 提供與SQL Profiler 事件類似的功能存儲過程自動重編譯的原因架構(gòu)變化統(tǒng)計變化延期編譯Set屬性改變臨時表改變存儲過程創(chuàng)建時使用了 PLIE查詢提示或使用了OPTION （ PILE）監(jiān)視重編譯性能計數(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í)行計劃編譯