SQL Server如何固定執行計劃

来源:http://www.cnblogs.com/kerrycode/archive/2017/01/18/6296015.html
-Advertisement-
Play Games

SQL Server 其實從SQL Server 2005開始，也提供了類似ORACLE中固定執行計劃的功能，只是好像很少人使用這個功能。當然在SQL Server中不叫"固定執行計劃"這個概念，而是叫"執行計劃指南"（Plan Guide 很多翻譯是計劃指南，個人覺得執行計劃指南稍好一些）。當然兩... ...


   SQL Server 其實從SQL Server 2005開始，也提供了類似ORACLE中固定執行計劃的功能，只是好像很少人使用這個功能。當然在SQL Server中不叫"固定執行計劃"這個概念，而是叫"執行計劃指南"（Plan Guide 很多翻譯是計劃指南，個人覺得執行計劃指南稍好一些）。當然兩者雖然概念與命名不同，實質上它們所說的是相同的事情，當然商業包裝是很常見的事情。個人還是覺得“固定執行計劃”這個概念叫起來順口,通俗易懂，執行計劃指南（Plan Guide）叫起來老感覺非常拗口，不知所云（後面會在這兩個概念切換，你知道我所說的是一件事情就好）。其實我以前也很少使用這些功能，直到最近在SQL Server 2014資料庫中使用固定執行計劃解決了幾個SQL的性能問題，所以覺得還是有必要總結、歸納一下。

 

為什麼要固定執行計劃？

 

為什麼要使用固定執行計劃（Plan Guid）呢？ 個人簡單的從下麵幾個方面介紹一下，如有不足，敬請指正。個人也是在探索當中。

 

由於一些特殊原因（例如Parameter Sniffing、統計信息的變化或採樣比例低造成的統計信息出現偏差、或其他像SQL Server 2014新的基數評估（Cardinality Estimator）特性引起優化器選擇不合適的JOIN操作等等），導致某個SQL的執行計划出現很大偏差，當資料庫優化器為SQL選擇了一個糟糕的執行計劃時，就可能出現嚴重性能問題，我就碰到過這樣一個例子，在SQL Server 2014中，有一個SQL的執行頻率較頻繁，有時候優化器突然選擇了一個較差的執行計劃時，這時就會出現嚴重的性能問題。所以，這個時候，我們就必須使用Plan Guide固定這個執行計劃，從而讓優化器使用正確的執行計劃，從而解決這樣的性能問題。

 

另外一方面，因為優化器生成執行計劃本身是很複雜的過程，我們所能幹涉的不多，最多使用HINT提示來改變執行計劃。而且優化器基於一些演算法和開銷考慮，也有可能生成的執行計劃不是最優執行計劃，而Plan Guid是DBA管理資料庫的一件利器，如果你發現了一個比當前更好的執行計劃，也能使用執行計劃指南固定這個SQL的執行計劃。當然這種情況非常、非常少，至少我在生產環境使用得不多。

 

有時候,某個系統是購買供應商的，你發現資料庫裡面有大量幾乎相同的SQL解析，然後緩存了，其實你發現這些SQL完全可以只解析一次，完全可以參數化，沒有必要大量解析。但是現在供應商沒有提供技術支持了，不可能去優化代碼裡面的SQL語句，那麼你也可以使用執行計劃指南來幫你解決這個問題。

 

還有就是使用Plan Guide來調優，對比不同的執行計劃的優劣。當然應該還有一些其它應用場景，只是我沒有碰到過而已。

 

如何固定執行計劃？

 

Plan Guide主要用到下麵幾個存儲，關於這些系統存儲過程的使用方法、功能介紹，官方文檔有詳細的介紹。在此就不畫蛇添足了。

sys.sp_create_plan_guide,

sys.sp_create_plan_guide_from_handle,

sys.sp_control_plan_guide

下麵我們還是看看一些應用場景案例吧！構造一個合適、貼切的例子實在是太花精力和時間，生產環境案例又不能搬出來，我們先來看看官方文檔提供的例子吧，如下SQL所示，在測試資料庫AdventureWorks2014,該SQL使用Nested Loop關聯兩個表

SELECT COUNT(*) AS c
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';

clipboard

 

假如（註意這裡是假設）發現如果這個SQL中，兩個表使用MERGE JOIN的方式，效率更高，那麼我們可以使用sp_create_plan_guide來創建執行計劃指南（固定執行計劃），如下所示

EXEC sp_create_plan_guide 
    @name = N'my_table_jon_guid',
    @stmt = N'SELECT COUNT(*) AS c
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate >= ''20000101'' AND h.OrderDate <=''20050101'';',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (MERGE JOIN)';

 

那麼此時再執行這個SQL時，你就會發現執行計劃就會變成Merge Join方式了。 這樣好過在SQL Server中使用HINT，為什麼呢？ 有可能這個SQL是寫死在應用程式裡面，如果以後這個執行計劃變成了一個糟糕的執行計劃，維護的成本非常高（一方面如果沒有記錄，需要耗費精力去定位、查找這段SQL，另外一方面，DBA是沒有許可權接觸這些應用程式代碼的，可能需要你溝通、協調開發人員、運維人員。耗費無數的時間、精力.....,還有可能其他接手維護的人不瞭解情況等等），而使用執行計劃指南，那麼你查找、禁用、刪除這個執行計劃指南即可。非常方便、高效，也許你一分鐘就能搞定，如果是Hint，說不定處理完，需要幾天，想必這樣的耗費精力溝通、協調的事情很多人都遇到過。

SELECT COUNT(*) AS c
FROM Sales.SalesOrderHeader AS h
INNER MERGE JOIN Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';

clipboard

 

另外，我們再來構造一個例子，模擬系統裡面出現大量解析的SQL語句的案例，如下所示

USE AdventureWorks2014;
GO
SET NOCOUNT ON;
GO
DROP TABLE TEST
GO
CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(8));
GO
CREATE INDEX PK_TEST ON TEST(OBJECT_ID);
GO
 
DECLARE @Index INT =1;
 
WHILE @Index <= 10000
BEGIN
    INSERT INTO TEST
    SELECT @Index, 'kerry';
   
    SET @Index = @Index +1;
END
GO
UPDATE STATISTICS  TEST WITH FULLSCAN;
GO

 

構造了上面案例後，我們清空該資料庫所有緩存的執行計劃（僅僅是為了乾凈的測試環境，避免以前緩存的執行計劃影響實驗結果）,生產環境你不能使用DBCC FREEPROCCACHE清空所有緩存的執行計劃，但是可以用DBCC FREEPROCCACHE刪除特定的執行計劃。

DBCC FREEPROCCACHE;

GO

然後我們開始測試我們的例子，假設系統裡面有大量類似的SQL語句，數量驚人（我們僅僅測試四個）。如果這個系統是從供應商那裡購買的，現在又沒有技術支持和Support的人（或者及時有人Support，但是不嚴重影響使用的情況，人家不想花費精力去優化），沒有人協助你優化這些SQL，你又不能將資料庫參數“參數化”從簡單設置為強制（因為影響太大，而且沒有測試，不確定是否帶來潛在的性能問題）.....

SELECT * FROM TEST WHERE OBJECT_ID=1;
GO
SELECT * FROM TEST WHERE OBJECT_ID=2;
GO
SELECT * FROM TEST WHERE OBJECT_ID=3;
GO
SELECT * FROM TEST WHERE OBJECT_ID=4;
GO
....................................................................

 

此時查看執行計劃,發現緩存了4個執行計劃

SELECT qs.sql_handle,
       qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       st.text,
       qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'%SELECT * FROM TEST WHERE OBJECT_ID%' AND text NOT LIKE 'SELECT qs.sql_handle%';

clipboard

 

那麼此時，執行計劃指南就能發揮其作用了,使用sp_create_plan_guide創建執行計劃指南，強制SELECT * FROM TEST WHERE OBJECT_ID=xxx這樣的SQL參數化

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT * FROM TEST WHERE OBJECT_ID=1',
@stmt OUTPUT, 
@params OUTPUT;
 
EXEC sp_create_plan_guide N'my_sql_parameter_test', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';

 

 

然後我們執行下麵命令，清空該資料庫所有緩存的執行計劃，然後執行上面四個SQL語句

DBCC FREEPROCCACHE;
 
GO
 
SELECT * FROM TEST WHERE OBJECT_ID=1;
 
SELECT * FROM TEST WHERE OBJECT_ID=2;
 
SELECT * FROM TEST WHERE OBJECT_ID=3;
 
SELECT * FROM TEST WHERE OBJECT_ID=4;

 

 

你會發現他們全部使用執行計劃指南裡面的執行計划了。不用多次解析了。

clipboard

 

還是使用上面的例子，我們來解決一個Parameter Sniffing（參數嗅探）的問題，在實驗前，我們先刪除前面創建的Plan Guide，以免這個影響測試結果，

EXEC sp_control_plan_guide @operation=N'DROP', @name=N'my_sql_parameter_test';

 

我們構造一個數據傾斜的案例，這樣方便我們演示

 
UPDATE
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • java基礎MySQL
    ###11數據表中的數據類型 * A:MySQL中的我們常使用的數據類型如下 詳細的數據類型如下(不建議詳細閱讀！) 分類 類型名稱 說明 整數類型 tinyInt 很小的整數 smallint 小的整數 mediumint 中等大小的整數 int(integer) 普通大小的整數 小數類型 flo ...
  • 資料庫優化案例——————某市中心醫院HIS系統
    記得在自己學習資料庫知識的時候特別喜歡看案例，因為優化的手段是容易掌握的，但是整體的優化思想是很難學會的。這也是為什麼自己特別喜歡看案例，今天也開始分享自己做的優化案例。 最近一直很忙，博客產出也少的可憐，今天整理了一下自己做過優化或各種方案的客戶已經超過100家了，今天分享的案例算是在這些客戶中比 ...
  • Flink架構、原理與部署測試
    Apache Flink是一個面向分散式數據流處理和批量數據處理的開源計算平臺，它能夠基於同一個Flink運行時，提供支持流處理和批處理兩種類型應用的功能。 現有的開源計算方案，會把流處理和批處理作為兩種不同的應用類型，因為它們所提供的SLA（Service Level Aggreement）是完全 ...
  • 資料庫實例: STOREBOOK > 用戶 > 編輯 用戶: SYSTEM
    編輯 用戶: SYSTEM 1.1, 1.2, 2.1, 2.2, 3.1, 3.2, 4.1, 4.2, 5.1, 5.2, 6.1, 6.2, 7.1, 7.2, 8.1, ...
  • SQL Server 2012 - SQL查詢
    執行計劃顯示SQL執行的開銷 工具→ SQL Server Profiler : SQL Server 分析器，監視系統調用的SQL Server查詢 Top查詢 -- Top Percent 選擇百分比 SELECT top 30 percent * FROM [SchoolDB].[dbo].[... ...
  • 性能優化實戰案例——助力某移動OA系統
    前言 最近連續接觸了4個OA系統，均存在著不同的性能問題，本文記述對某移動OA系統的優化全過程，讓看官們對資料庫優化流程有一個瞭解，並揭開隱式轉換這無情殺手的神秘面紗。 本文使用的工具：SQL專家雲平臺專業體檢工具 ：www.zhuancloud.com 系統情況 硬體配置 軟體情況 資料庫情況 系 ...
  • 緩存遇到的數據過濾與分頁問題
    遇到的問題 1、最初階段 系統中做了一個監控功能，用於記錄所有的請求數據，數據插入頻繁，量非常大，比如一天1000萬條。考慮到數據插入的效率，就使用記憶體KV緩存來保存。寫入過程是在接收到請求後放入到線程池中，然後線程池非同步處理後寫入。到這問題基本上沒什麼事情。 2、新的需求 後面數據保存了，就需要在 ...
  • LAMP創建
    httpd yum install httpd -y systemctl status httpd systemctl start httpd systemctl stop firewalld Mariadb yum install mariadb-server mariadb-client -y ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類