SQL Server ->> SQL Server 2016新特性之 --- Query Store

標籤：div   mode   rom   cleanup   類型   handle   解決辦法   迴歸   admin   

前言

SQL Server 2016引入新的查詢語句效能監控、調試和最佳化工具/功能 -- Query Store。以前我們發現一條查詢語句效能突然下降，我們要去找出問題的所在往往需要通過調用一些DMV（比如sys.dm_exec_query_stats, sys.dm_exec_sql_text和sys.dm_exec_query_plan）來擷取查詢計劃的一些資訊，比如XML格式的執行計畫，查詢語句的代碼，執行了多少次以及一些資源和時間的的使用消耗情況。然後根據這些資訊來判斷這條語句是否存在效能問題。問題在於但一條語句出現了效能下降，我們可能需要和過去的資訊進行一個比較，才能知道效能是否下降了。比如一條語句如果過去花了1秒，現在3-5秒，我們可能第一眼會認定它是沒有問題的，但是其實它效能是下降了的。所以我還需要一個任務去定時收集這些資料。比如下面這條語句就是用來收集這些資訊的。

SELECT s2.dbid,     (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1          THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,    s3.query_plan,    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,     max_worker_time,    total_physical_reads,     last_physical_reads,     min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writesFROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS s3

 

SQL Server 2016引入了Query Store來自動收集資料庫的查詢計劃和相關的一些效能資訊，還提供了4種不同類型的報表可以來查看收集的資料。不通過報表，我們也可以通過一些新增的DMV來查詢收集好的資料。

那麼究竟Query Store可以在哪些方面幫到我們？

1）快速定位查詢語句的查詢計劃效能回退，找出哪些查詢語句最近因為查詢計劃的改變出現效能回退？這當中可能是因為刪除了某條索引、過去某個時間點有大量的資料湧入資料表中而統計資料沒有及時得以更新、或者最近代碼發現了改變等原因。

2）獲知查詢在某段時間內的資源使用/佔用情況以及執行次數。有時語句本身可能執行計畫沒有回退的情況出現，但是由於語句本身寫法問題或者缺乏合理的索引，導致語句的執行非常消耗資源，對於SQL Server整體的效能或者伺服器的效能造成很大的印象。作為DBA本身也需要及時去定位這些類型的語句，最後得出一些解決辦法。

3）獲知資料庫過去某段時間內的整體查詢工作負載，包括承受的並發查詢壓力（量級），資源消耗情況。

 

架構

Query Store儲存分兩部分，一部分是用於儲存編譯好的執行計畫的Plan Store，另一部分用於儲存語句執行執行過程中的一些統計資料。這些資料先是駐留在記憶體，隨後會根據你設定好的時間間隔寫入到資料庫的主檔案組中。

因為Query Store也需要背面的線程來定時寫入資料，所以啟用Query Store大概會付出3-5%的效能代價。間隔越短，效能的代價越大。間隔大了，由於SQL Server重啟導致丟失收集好在記憶體中但是還沒有寫入磁碟的那些資料的損失就越大。 

Query Store在訪問收集好的資料時是會先查看資料是否是否已經在記憶體中，這樣就必須去磁碟找了。只有不在記憶體中才去磁碟找。它會先調用一個叫QUERY_STORE_RUNTIME_STATS_IN_MEM的表函數去訪問記憶體中的資料，同時訪問plan_persist_runtime_stats這張表去訪問磁碟的資料。

 

啟用Query Store功能

 

通過代碼啟用Query Store

ALTER DATABASE XXXXX SET  QUERY_STORE = ON;ALTER DATABASE XXXXXSET QUERY_STORE  (  MAX_STORAGE_SIZE_MB = 250,  SIZE_BASED_CLEANUP_MODE = AUTO,  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)  );

 

上面括弧中的選項對應的GUI上選項列表

 

內建Query Store報表

這大概是Query Store最有用的功能。通過報表的形式和已經定製好的效能調優類型的報表來讓使用者知道過去一段時間內的資料情況。

目前一共提供了4種類型的報表，分別的：迴歸的查詢，總體資源的使用，前幾個資源使用的查詢，跟蹤的查詢。

 

Query Store相關的DMV

• sys.database_query_store_options (Transact-SQL) -- query store的一些資料庫配置的選項和值
• sys.query_context_settings (Transact-SQL)
• sys.query_store_plan (Transact-SQL)
• sys.query_store_query (Transact-SQL)
• sys.query_store_query_text (Transact-SQL)
• sys.query_store_runtime_stats (Transact-SQL)
• sys.query_store_runtime_stats_interval (Transact-SQL)

 

他們的關係

select *from sys.query_store_query qsq join        sys.query_context_settings qcs on qsq.context_settings_id = qcs.context_settings_id join        sys.query_store_query_text qst on qst.query_text_id = qsq.query_text_id join        sys.query_store_plan qsp on qsp.query_id = qsq.query_id join        sys.query_store_runtime_stats qsrs on qsrs.plan_id = qsp.plan_id join        sys.query_store_runtime_stats_interval qsrsi on qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id

 

 

參考：

SQL Server 2016：通過Query Store定位迴歸缺陷

The SQL Server 2016 Query Store: Overview and Architecture

SQL Server ->> SQL Server 2016新特性之 --- Query Store