Parameter sniffing and Query Store
Parameter sniffing (參數探測)
The parameter sniffing problem does not occur frequently. It only occurs when the data distribution in some tables is very uneven, or the parameter values brought in by the user are very uneven.
1.一些資料表中的資料分布不平均 或 資料異動頻繁導致資料分佈變動頻繁 (原本執行計畫使用seek，因為資料變動應該要用scan，因為執行計畫重用導致執行很久)
2.查詢的參數不平均(時大時小，例如 Where 過濾資料的參數，有時傳入取小範圍的資料(10 rows)，有時傳入取超大範圍的資料10,000,000 rows)
傳入取超大範圍資料，卻重用了取小範圍的資料用seek執行計畫，導致執行時seek很久(如果重新compile應該會改用scan的執行計畫)
Why:
SQL Server uses a process called parameter sniffing when executing queries or stored procedures that use parameters. During compilation, the value passed into the parameter is evaluated and used to create an execution plan. That value is also stored with the execution plan in the plan cache. Future executions of the plan will re-use the plan that was compiled with that reference value.
This is how we want SQL Server to work – compiling execution plans is expensive and time consuming. Good execution plan re-use is key to SQL Server performance.
Problems arise when the values queried in a table are not evenly distributed. If one parameter value returns 10 rows and another parameter value returns 10,000,000 rows, it would be best if SQL Server used different execution plans for the different values of the parameter.
The first time the parameterized query is run, SQL server will compile the query with whichever value is passed in. SQL Server will keep using that value until the query is recompiled (or until SQL Server is restarted).
To Fix Parameter Sniffing
解決方法幾乎都需要修改SQL語法或程式
Option 1: Indexes (Good Idea)
Often, inadequate covering indexes can be the root cause of parameter sniffing
Option 2: Recompiling (Not Always Great Idea)
CREATE PROCEDURE dbo.sample @ProductID INT
WITH RECOMPILE
AS
/* do something */
-- single statement version
SELECT * FROM Sales.SalesOrderHeader
OPTION (RECOMPILE);
Option 3: Optimize For Value (Rarely A Good Idea)
ALTER PROCEDURE Get_OrderID_OrderQty
@ProductID INT
AS
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION (OPTIMIZE FOR (@ProductID=945));
Option 4: Optimize For Unknown (Basically Never A Good Idea)
Option 5: Exceptions (Second Best Idea) 在SQL語法裡面判斷，執行不同的SP
CREATE PROCEDURE dbo.GetStackOverflowPosts
@UserID INT
AS
IF @UserID IN (SELECT UserID FROM dbo.ProlificUsers)
BEGIN
EXEC dbo.GetProlificPosts @UserID
END
ELSE
BEGIN
EXEC dbo.GetNormalPosts @UserID
END
Query Store
1.new feature in SQL Server 2016
2.to find all the queries with parameterization problems in a SQL Server
Enable Query Store
ALTER DATABASE [AdventureWorks2016] SET QUERY_STORE = ON
GO
Best practices with Query Store
The default value in SQL Server 2016 (13.x) and SQL Server 2017 (14.x) is 100 MB. This size might not be sufficient if your workload generates a large number of different queries and plans or if you want to keep query history for a longer period of time. Starting with SQL Server 2019 (15.x), the default value is 1 GB. Keep track of current space usage and increase the Max Size (MB) value to prevent Query Store from transitioning to read-only mode.
1.Max Size (MB) 預設 100MB (SQL 2016, SQL 2017)、1GB (SQL 2019) ，須注意占用資料庫空間
2.Size Based Cleanup Mode is enabled，基於Query Store佔用的空間大小控制是否啟動清理程式，清理程式會自動刪除Query Store中過時的資料，以釋放Query Store的空間
3.所有收集的資訊保持在記憶體中並且採用非同步寫入磁碟，故不會影響效能
Query Store collects information from two different phases of the query processing: The compilation and the execution. From the compilation phase, Query Store collects the query text and query plan, while from the execution phase it collects the execution statistics.
All this information is kept in memory and written asynchronously to the disk, so that the query execution performance isn’t affected by the query store.
Find which query suffer from the same parameterization problems
Create FUNCTION dbo.QueriesWithParameterizationProblems()
RETURNS TABLE
AS
RETURN
(
-- Parameterized queries with text and most recent planId
-- related with runtime stats grouped
-- Filtering system queries
select qsq.query_id,
max(qsqt.query_sql_text) query_sql_text,
max(qsp.plan_id) plan_id,
max(qsrs.max_duration) max_duration,
max(qsrs.max_cpu_time) max_cpu_time,
min(qsrs.min_cpu_time) min_cpu_time,
min(qsrs.min_duration) min_duration,
max(qsrs.stdev_duration) stdev_duration,
max(qsrs.stdev_cpu_time) stdev_cpu_time
from sys.query_store_query qsq,
sys.query_store_query_text qsqt,
sys.query_store_plan qsp,
sys.query_store_runtime_stats qsrs
where qsq.query_text_id= qsqt.query_text_id
and qsp.query_id=qsq.query_id
and qsrs.plan_id=qsp.plan_id
and (qsq.query_parameterization_type<>0
or qsqt.query_sql_text like '%@%')
and qsq.is_internal_query=0
and qsqt.query_sql_text not like '%sys.%'
and qsqt.query_sql_text not like '%sys[ ].%'
and qsqt.query_sql_text not like '%@[sys@].%' escape '@'
and qsqt.query_sql_text not like '%INFORMATION_SCHEMA%'
and qsqt.query_sql_text not like '%msdb%'
and qsqt.query_sql_text not like '%master%'
and qsp.last_execution_time=(select max(last_execution_time)
from sys.query_store_plan qsp2
where qsp2.query_id= qsp.query_id)
group by qsq.query_id
)
GO
檢查遇到參數探測問題的SQL Query
select * from dbo.QueriesWithParameterizationProblems()
where query_sql_text not like '%plancache%'
order by stdev_cpu_time desc
檢查stdev_cpu_time欄位非常高(標準差較高的)的表示遇到參數化探測問題
select * from dbo.QueriesWithParameterizationProblems()
where stdev_cpu_time > 20000 and query_sql_text not like '%plancache%'
order by stdev_cpu_time desc
過濾stdev_cpu_time欄位，顯示比較高標準差的資料
若檢查資料可能發現資料分佈不平均
當查詢Where listprice=245.01 應該會seek
但查詢Where listprice=0 如果也會seek則會執行超久，因為筆數超多
fix Parameter Sniffing issue by plan guide
Although query store allows us to force a specific query plan, this will not solve this kind of problem. The solution we need is the possibility to use different query plans according to the value of the parameter. We can solve this using “Option (Recompile)” in the queries, so that each execution will generate a new query plan. However, query store isn’t able to add one option to an existing plan. How can we achieve this without changing the source code of the system? The solution is the use of plan guides.
或官方建議建議的解決方法
Best practices with Query Store
Tune performance with the Query Store
Reference:
Monitor performance by using the Query Store
sp_BlitzCache™ Result: Parameter Sniffing
Query Store and Parameterization Problems
The SQL Server 2016 Query Store: Overview and Architecture
The SQL Server 2016 Query Store: Built-in Reporting
沒有留言:
張貼留言