程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 淺析SQL Server中的執行計劃緩存(上),

淺析SQL Server中的執行計劃緩存(上),

編輯:更多數據庫知識

淺析SQL Server中的執行計劃緩存(上),


簡介

我們平時所寫的SQL語句本質只是獲取數據的邏輯,而不是獲取數據的物理路徑。當我們寫的SQL語句傳到SQL Server的時候,查詢分析器會將語句依次進行解析(Parse)、綁定(Bind)、查詢優化(Optimization,有時候也被稱為簡化)、執行(Execution)。除去執行步驟外,前三個步驟之後就生成了執行計劃,也就是SQL Server按照該計劃獲取物理數據方式,最後執行步驟按照執行計劃執行查詢從而獲得結果。但查詢優化器不是本篇的重點,本篇文章主要講述查詢優化器在生成執行計劃之後,緩存執行計劃的相關機制以及常見問題。

為什麼需要執行計劃緩存

從簡介中我們知道,生成執行計劃的過程步驟所占的比例眾多,會消耗掉各CPU和內存資源。而實際上,查詢優化器生成執行計劃要做更多的工作,大概分為3部分:

首先,根據傳入的查詢語句文本,解析表名稱、存儲過程名稱、視圖名稱等。然後基於邏輯數據操作生成代表查詢文本的樹。

第二步是優化和簡化,比如說將子查詢轉換成對等的連接、優先應用過濾條件、刪除不必要的連接(比如說有索引,可能不需要引用原表)等。

第三步根據數據庫中的統計信息,進行基於成本(Cost-based)的評估。

上面三個步驟完成之後,才會生成多個候選執行計劃。雖然我們的SQL語句邏輯上只有一個,但是符合這個邏輯順序的物理獲取數據的順序卻可以有多條,打個比方,你希望從北京到上海,即可以做高鐵,也可以做飛機,但從北京到上海這個描述是邏輯描述,具體怎麼實現路徑有多條。那讓我們再看一個SQL Server中的舉例,比如代碼清單1中的查詢。

SELECT * 
FROM A INNER JOIN B ON a.a=b.b
INNER JOIN C ON c.c=a.a

代碼清單1.

對於該查詢來說,無論A先Inner join B還是B先Inner Join C,結果都是一樣的,因此可以生成多個執行計劃,但一個基本原則是SQL Server不一定會選擇最好的執行計劃,而是選擇足夠好的計劃,這是由於評估所有的執行計劃的成本所消耗的成本不應該過大。最終,SQL Server會根據數據的基數和每一步所消耗的CPU和IO的成本來評估執行計劃的成本,所以執行計劃的選擇重度依賴於統計信息,關於統計信息的相關內容,我就不細說了。

對於前面查詢分析器生成執行計劃的過程不難看出,該步驟消耗的資源成本也是驚人的。因此當同樣的查詢執行一次以後,將其緩存起來將會大大減少執行計劃的編譯,從而提高效率,這就是執行計劃緩存存在的初衷。

執行計劃所緩存的對象

執行計劃所緩存的對象分為4類,分別是:

編譯後的計劃:編譯的執行計劃和執行計劃的關系就和MSIL和C#的關系一樣。

執行上下文:在執行編譯的計劃時,會有上下文環境。因為編譯的計劃可以被多個用戶共享,但查詢需要存儲SET信息以及本地變量的值等,因此上下文環境需要對應執行計劃進行關聯。執行上下文也被稱為Executable Plan。

游標:存儲的游標狀態類似於執行上下文和編譯的計劃的關系。游標本身只能被某個連接使用,但游標關聯的執行計劃可以被多個用戶共享。

代數樹:代數樹(也被稱為解析樹)代表著查詢文本。正如我們之前所說,查詢分析器不會直接引用查詢文本,而是代數樹。這裡或許你會有疑問,代數樹用於生成執行計劃,這裡還緩存代數樹干毛啊?這是因為視圖、Default、約束可能會被不同查詢重復使用,將這些對象的代數樹緩存起來省去了解析的過程。

比如說我們可以通過dm_exec_cached_plans這個DMV找到被緩存的執行計劃,如圖1所示。

圖1.被緩存的執行計劃

那究竟這幾類對象緩存所占用的內存相關信息該怎麼看呢?我們可以通過dm_os_memory_cache_counters這個DMV看到,上述幾類被緩存的對象如圖2所示。

圖2.在內存中這幾類對象緩存所占用的內存

另外,執行計劃緩存是一種緩存。而緩存中的對象會根據算法被替換掉。對於執行計劃緩存來說,被替換的算法主要是基於內存壓力。而內存壓力會被分為兩種,既內部壓力和外部壓力。外部壓力是由於Buffer Pool的可用空間降到某一臨界值(該臨界值會根據物理內存的大小而不同,如果設置了最大內存則根據最大內存來)。內部壓力是由於執行計劃緩存中的對象超過某一個阈值,比如說32位的SQL Server該阈值為40000,而64位中該值被提升到了160000。

這裡重點說一下,緩存的標識符是查詢語句本身,因此select * from SchemaName.TableName和Select * from TableName雖然效果一致,但需要緩存兩份執行計劃,所以一個Best Practice是在引用表名稱和以及其他對象的名稱時,請帶上架構名稱。
基於被緩存的執行計劃對語句進行調優

被緩存的執行計劃所存儲的內容非常豐富,不僅僅包括被緩存的執行計劃、語句,還包括被緩存執行計劃的統計信息,比如說CPU的使用、等待時間等。但這裡值得注意的是,這裡的統計只算執行時間,而不算編譯時間。比如說我們可以利用代碼清單2中的代碼根據被緩存的執行計劃找到數據庫中耗時最長的20個查詢語句。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20 
 CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) 
                   AS [Total Duration (s)] 
 , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 
                AS DECIMAL(28, 2)) AS [% CPU] 
 , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 
    qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 
 , qs.execution_count 
 , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count 
        AS DECIMAL(28, 2)) AS [Average Duration (s)] 
 , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,   
  ((CASE WHEN qs.statement_end_offset = -1 
   THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
   ELSE qs.statement_end_offset 
   END - qs.statement_start_offset)/2) + 1) AS [Individual Query 
 , qt.text AS [Parent Query] 
 , DB_NAME(qt.dbid) AS DatabaseName 
 , qp.query_plan 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
WHERE qs.total_elapsed_time > 0 
ORDER BY qs.total_elapsed_time DESC

代碼清單2.通過執行計劃緩存找到數據庫總耗時最長的20個查詢語句

上面的語句您可以修改Order By來根據不同的條件找到你希望找到的語句,這裡就不再細說了。

相比較於無論是服務端Trace還是客戶端的Profiler,該方法有一定優勢,如果通過捕捉Trace再分析的話,不僅費時費力,還會給服務器帶來額外的開銷,通過該方法找到耗時的查詢語句就會簡單很多。但是該統計僅僅基於上次實例重啟或者沒有運行DBCC FreeProcCache之後。但該方法也有一些弊端,比如說:

類似索引重建、更新統計信息這類語句是不緩存的,而這些語句成本會非常高。
緩存可能隨時會被替換掉,因此該方法無法看到不再緩存中的語句。
該統計信息只能看到執行成本,無法看到編譯成本。
沒有參數化的緩存可能同一個語句呈現不同的執行計劃,因此出現不同的緩存,在這種情況下統計信息無法累計,可能造成不是很准確。

執行計劃緩存和查詢優化器的矛盾

還記得我們之前所說的嗎,執行計劃的編譯和選擇分為三步,其中前兩步僅僅根據查詢語句和表等對象的metadata,在執行計劃選擇的階段要重度依賴於統計信息,因此同一個語句僅僅是參數的不同,查詢優化器就會產生不同的執行計劃,比如說我們來看一個簡單的例子,如圖3所示。

圖3.僅僅是由於不同的參數,查詢優化器選擇不同的執行計劃

大家可能會覺得,這不是挺好的嘛,根據參數產生不同的執行計劃。那讓我們再考慮一個問題,如果將上面的查詢放到一個存儲過程中,參數不能被直接嗅探到,當第一個執行計劃被緩存後,第二次執行會復用第一次的執行計劃!雖然免去了編譯時間,但不好的執行計劃所消耗的成本會更高!讓我們來看這個例子,如圖4所示。

圖4.不同的參數,卻是完全一樣的執行計劃! 

再讓我們看同一個例子,把執行順序顛倒後,如圖5所示。

圖5.執行計劃完全變了

我們看到,第二次執行的語句,完全復用了第一次的執行計劃。那總會有一個查詢犧牲。比如說當參數為4時會有5000多條,此時索引掃描應該最高效,但圖4卻復用了上一個執行計劃,使用了5000多次查找!!!這無疑是低效率的。而且這種情況出現會非常讓DBA迷茫,因為在緩存中的執行計劃不可控,緩存中的對象隨時可能被刪除,誰先執行誰後執行產生的性能問題往往也讓DBA頭疼。

由這個例子我們看出,查詢優化器希望盡可能選擇高效的執行計劃,而執行計劃緩存卻希望盡可能的重用緩存,這兩種機制在某些情況會產生沖突。

在下篇文章中,我們將會繼續來看由於執行計劃緩存和查詢分析器的沖突,以及編譯執行計劃所帶來的常見問題和解決方案。

小結

本篇文章中,我們簡單講述了查詢優化器生成執行計劃的過程,以及執行計劃緩存的機制。當查詢優化器和執行計劃緩存以某種不好的情況交匯時,將產生一些問題。在下篇文章中,我們會繼續探索SQL Server中的執行計劃緩存。

以上內容是小編給大家介紹的SQL Server中的執行計劃緩存(上)的全部敘述,希望大家喜歡。

您可能感興趣的文章:

  • sqlserver 中一些常看的指標和清除緩存的方法
  • SQL Server 高速緩存依賴分析
  • 開啟SQLSERVER數據庫緩存依賴優化網站性能
  • SqlServer 執行計劃及Sql查詢優化初探
  • 優化SQL Server的內存占用之執行緩存
  • SQLSERVER中得到執行計劃的兩種方式
  • 強制SQL Server執行計劃使用並行提升在復雜查詢語句下的性能
  • 淺析SQL Server中的執行計劃緩存(下)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved