后面的練習中需要下載 Demo 數據庫, 有很多不同的版本, 可以根據個人需要下載.? 下載地址 - http://msftdbprodsamples.codeplex.com/

1. 什么是執行計劃

• 查詢優化器對輸入的 T-SQL 查詢語句通過"計算"而選擇出效率最高的一種執行方案，這個執行方案就是執行計劃.
• 執行計劃可以告訴你這個查詢將會被如何執行或者已經被如何執行過，可以通過執行計劃看到 SQL 代碼中那些效率比較低的地方.
• 查看執行計劃的方式我們可以通過圖形化的界面，或者文本，或者XML格式查看，這樣會比較方便理解執行計劃要表達出來的意思.

? 2. 當一個 Query 被提交后到底發生了什么? 　　實際上，當一個查詢被提交到 SQL Server 后，服務器端很多進程實際上要做很多事情來確保數據出入的完整性. 　　 　　對于 T-SQL 來說, 處理的主要有兩個階段: 關系引擎階段和存儲引擎階段. 　　關系引擎主要要做的事情就是首先確保 Query 語句能夠正確的解析, 然后交給查詢優化器并產生執行計劃. 然后執行計劃就以二進制格式發到存儲引擎來更新或者讀取數據.? 　　存儲引擎主要處理的比如像鎖, 索引的維護和事務等. 所以對于執行計劃，重點的是關注關系引擎.? ? 3. Query Parsing - 查詢解析 　　首先這里的查詢解析發生在關系引擎上, 關系引擎專門有一項任務就是先檢查下這個 SQL 語句寫的對不對，如果能夠順利解析那么就會解析出一個"解析樹"出來. 這個解析樹能夠呈現要執行這個 SQL 語句的邏輯步驟.? ?　? 當 SQL 語句不是 DML 類型的話, 那么就不需要被優化, 因為比如像創建表這類語句目標性都比較明確就是創建表，非常的直接，所以就這類語句就不需要被調優.? ? 4. 查詢優化器 　　查詢優化器實際上是一個在關系型引擎上的小軟件模塊，它根據上面的解析樹和一些有關數據的統計信息來進行一些分析，然后就算出它認為一種效率比較高的能夠執行這個 SQL 的執行計劃. ? 　　具體的比如決定是否可以通過索引來直接獲取數據，或者選擇哪種 JOIN 方式會更好等等. 實際上除非這里有緩存的執行計劃, 那么它就會計算幾種不同的執行計劃，但是根據 CPU 運行還有I/O的一些估算，看看那種候選的執行計劃使用的資源可能會最少，這樣最終通過比較就選擇一種成本最低的執行計劃. ? 　　但是有時，當一個 SQL 語句很簡單的時候，執行這個 SQL 語句可能花費的時間比分析不同的候選計劃的時間可能還要少，那么查詢優化器可能會選擇一種相對簡單的執行計劃就可以了. ?如果 SQL 語句它認為比較重要，那么就花時間去排列不同的執行計劃最后選一個成本最低的計劃. 　　能做到這一點，實際上要依賴于被 SQL Server 維護的一些統計數據. 這些統計數據比如像目標對象上的數據列，索引，唯一性等等，這些內容都是查詢優化器分析執行計劃的依據. 比如有的列有索引，有的沒有，有的列在 JOIN 條件或者 WHERE 條件上, 查詢優化器都會對這些統計信息做分析來嘗試不同的比如 JOIN 順序, 嘗試不同的索引等等，然后選擇一個執行最快的執行計劃. 在這種執行計劃當中實際上都有一個一個執行的步驟，查詢優化器對執行這些步驟耗費的時間有一個大概的估算，那么這樣一步一步到整個執行計劃就有了一個“成本估算”Estimated Cost. ?　　一旦這個執行計劃被查詢優化器計算出來, 那么它就會被在一個叫做 "緩存計劃" 的內存空間里被創建, 創建的時候也會檢查下"緩存計劃中" 是不是已經存在這個計劃，如果沒有就直接創建，如果有就用已經存在的計劃. ? 5. 查詢執行 　　一旦執行計劃被創建, 那么處理的程序就移動到存儲引擎, 在存儲引擎中這個查詢才會被真正的執行, 執行的方式就是按照之前產生出來的"執行計劃". ? 6. 估算執行計劃和實際執行計劃 Estimated and Actual Execution Plans? 　　它們之間的區別就是- 估算的執行計劃是從查詢優化器來的, 是輸入關系引擎的, 它的執行步驟包括一些運算符等等都是通過一系列的邏輯分析出來的，是一種通過邏輯推算出來的計劃，只能代表查詢優化器的觀點. 　　實際執行計劃是真實的執行了"估算執行計劃"后的一種真實的結果，是實實在在真實的執行反饋, 是屬于存儲引擎.? ? 7. 查詢計劃重用? 　　SQL Server 上維護和生產這些查詢計劃的成本比較大, 所以 Server 會盡量的考慮重用這些計劃. 一旦創建在"緩存計劃"里，每次從查詢優化器出來的"估算執行計劃"就會和"緩存計劃"中的計劃進行對比, 如果有的話就直接使用"緩存計劃"中已經存在的計劃，這樣就可以重用. 　　特別是一個系統有大批大批的 SQL 語句需要被分析被創建執行計劃，通過這種方式可以避免反復的創建，減少不必要的開銷. 比如有 1000 組 SQL 語句, 有一部分還非常復雜，并且在1分鐘內可能被反復調用了成千上萬次, 如果每次進來都由查詢優化器估算并在緩存里創建一遍, 這個開銷還是非常大的.? ?　? 但是有時查詢優化器也會去考慮性能的問題，如果它認為這個計劃在某種條件下比如CPU，內存等等足夠足夠，并且如果并行執行計劃時性能更好的話，那么也有可能會為同一個 SQL 創建兩個執行計劃.? ? 8. 執行計劃的清除 　　執行計劃并不是一直會保存在內存中, Server 會定期的對執行計劃進行清理. 執行計劃一般都會有一個類似于倒數計數器的一個記錄, 比如有一個初始值 50, 每用一次減一個數,減到0的時候系統就會釋放這一塊內存, 總結起來有這么幾種情況:

• 系統需要更多內存的時候
• 倒數計數器減到 0 了.
• 當前的鏈接沒有引用到這個執行計劃.?

9.?執行計劃的重新編譯，在這些情況下執行計劃會被重新編譯:

• Query 所引用的表或者架構等結構發生改變
• Query 所引用的索引發生改變，比如修改或者刪除了
• 調用了 sp_recompile 重新編譯的時候.

10. 可以通過? DBCC FREEPROCCACHE 來清除緩存

11. 查看系統中已有的執行計劃

SELECT [cp].[refcounts] ,[cp].[usecounts] ,[cp].[objtype] ,[st].[dbid] ,[st].[objectid] ,[st].[text] ,[qp].[query_plan] FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

執行結果

可以打開一個 Query Plan 來看

?

12. 為什么 Estimated Execution Plan 和 Actual Execution Plan 不一樣

• Estimated Execution Plan 所統計的數據和實際執行的時候數據有變化, 比如Estimated Execution Plan 創建的時候, 數據庫只有1000條數據 , 但是在實際執行的時候可能數據在這期間已經發生改變, 　　因此產生出來的 Actual Execution Plan 可能就不同.
• Estimated Execution Plan 實際上在關系型引擎中創建產生, 因為并不會真正執行SQL 語句. 那么類似于這樣的代碼在得到 Estimated Execution Plan 的時候會發生錯誤, 原因就是這個對象不可能在 Estimated Execution Plan 階段創建因此就會發生錯誤, 這和 Actual Execution Plan也不同.

CREATE TABLE TempTable ( Id INT IDENTITY(1, 1) ,Dsc NVARCHAR(50) );INSERT INTO TempTable ( Dsc ) SELECT Value FROM dbo.BrandSELECT * FROM TempTable; DROP TABLE TempTable;

如果點擊 "Display Estimated Execution Plan" 將會產生以下錯誤信息:

Msg 208, Level 16, State 1, Line 6

Invalid object name 'TempTable'.

原因就是 TempTable 是不可能在 Estimated Execution Plan 階段創建的, 因為還處于在關系引擎控制階段, 還沒有到存儲引擎.

13. Execution Plan Formats 執行計劃的3種格式 (個人比較喜歡圖形執行計劃, 但有時也會使用文本執行計劃查看具體的數據)

• 圖形執行計劃 – 非常容易查看和閱讀, 但是有一些細節數據被隱藏了
• 文本執行計劃 – 讀起來有些困難, 但是很多信息可以直接查看. 文本執行計劃有3種格式:
  • SHOWPLAN_ALL – 顯示”估算執行計劃”的所有內容
  • SHOWPLAN_TEXT – 顯示一部分”估算執行計劃”的內容
  • STATISTICS PROFILE – 和SHOWPLAN_ALL 一樣, 不同的是它顯示的是”實際執行計劃”??
• XML 執行計劃 - XML 執行計劃能夠以XML格式反映大多數數據內容.
  • SHOWPLAN_XML – 可以理解為”預估執行計劃”
  • STATISTICS_XML – 顯示實際執行計劃

?注意 - 加密的存儲過程不會在執行計劃中呈現 ? ?14. 查看 Execution Plan 的權限 　　用戶如果不是 sysadmin, dbcreator 和 db_owner. 就需要 SHOWPLAN? 權限 - GRANT SHOWPLAN TO [username] 15. 測試語句 SELECT * FROM [dbo].[DatabaseLog]

Table scan – 出現在當存儲引擎強制性的一行一行的遍歷整張表或者返回所有內容, 因為我們并沒有使用任何的 WHERE? 條件并且我們也沒有使用到任何的索引(索引上應該包含它所在表上的所有列).

16．對于 Estimated Execution Plan 執行計劃

?

? ? ? ? ? ? ? ? ? ? ? ?

查看查詢優化器給出 SELECT 操作符的執行成本情況

• Cached plan size – 在存儲過程緩存中由這個查詢語句產生的計劃所占用內存的大小, 這個數字比較有用, 可以用來調查緩存的性能問題, 特別是某個計劃所占的內存非常大的情況下.

• Estimated Operator Cost – 就應該是上圖中? SELECT 的耗費

• Estimated Sub tree Cost – 告訴我們這個步驟和之前所有的步驟所累加起來的一個耗費值, 但是看它之前的步驟要注意要從右往左讀, 目前 SELECT 步驟的左邊沒有其他的步驟. 這個值表示的是查詢優化器認為這個運算符可能要花費的時間.

• Estimated number of rows – 查詢優化器基于一些統計數據得到的, 這里指的是返回的行數.

查看 Table Scan

?

• Logical Operations – 查詢優化器認為對在 Query 語句執行時應該發生的操作 .

• Physical Operations – 實實在在發生的操作. 這兩個值大多數情況相同, 但也有不同的時候.

• Estimated number of rows – 再次看到這個值, 實際上每一個步驟的操作可能不相同, 每一個步驟要處理和傳遞的數據量大小都不一樣, 所以從每個步驟中就能看到數據量大小的變化, 有哪些增加了, 有哪些被過濾掉了, 這樣也會幫助我們理解整個流程發生了什么.

• Ordered – 告訴我們這個步驟的操作符是否對數據進行了排序操作.

17. 預估的文本執行計劃 Getting the Estimated Text Plan

　　在啟用文本的預估執行計劃時, 先要打開這個計劃.

　　SET SHOWPLAN_ALL ON; 并且要注意在使用完了之后也一定要 SET SHOWPLAN_ALL OFF.

18. 實際文本執行計劃 Getting the Actual Text Plan

　　SET STATISTICS PROFILE ON??? SET STATISTICS PROFILE OFF

19. XML 執行計劃

　　Estimated Plan -? SET SHOWPLAN_XML? ON? / SET SHOWPLAN_XML OFF

20. XML 實際執行計劃

　　Actual Plan – SET STATISTICS XML ON? / SET STATISTICS XML OFF

21. 如何將XML? 計劃保存為圖形執行計劃

　　可以將XML 格式的執行計劃保存為后綴為 “.sqlplan” 的文件, 這個文件顯示的就是圖形執行計劃. ?因為有的時候需要把某個執行計劃的結果發給其他人來調整我們的SQL 語句.

22. 在 SQL Profiler 中自動捕獲執行計劃

在實際開發過程中, 可能隨時需要查看不同SQL 語句的執行計劃, 并且最好是能夠自動捕獲 SQL 的執行計劃以便隨時查看有哪些查詢耗費的成本比較高, 或者像這種有 Table Scan 的情況都需要被截獲并查看他們的執行計劃.

在SQL Server 2005可以通過 SQL Profiler 來幫助捕獲這些計劃, 后期的版本也支持這個功能. 捕獲后的計劃可以直接在SQL Profiler 中瀏覽或者保存到文件, 再或者存放到數據庫.

在 SQL Profiler 中選中以下幾種事件

• • Showplan XML

• • RPC:Completed

• • SQL:BatchStarting

• • SQL:BatchCompleted

選中 Showplan XML 事件后會出現一個新的Tab – Events Extraction Settings. 在這里可以配置 XML Execution plan 保存的地點以便以后查看, 還可以選擇將Execution Plan 保存在一個文件還是每個單獨放在一個文件里.

配置完成后點擊 ”Run”, 然后就可以在 SQL Profiler 中查看SQL 的執行計劃, 并且還可以單獨保存成一個文件作為以后分析的依據.

轉載于:https://www.cnblogs.com/biwork/archive/2013/04/11/3015655.html

總結

以上是生活随笔為你收集整理的Execution Plan 执行计划介绍的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。