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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類