Here are some reference links and script I used to fix performance issue by force query plan using query store feature:
--https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16
--https://www.sqlshack.com/force-query-execution-plan-using-sql-server-2016-query-store/
--ALTER DATABASE [system-abccompany] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
--ALTER DATABASE [system-abccompany] SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );
--SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb, readonly_reason, interval_length_minutes, stale_query_threshold_days, size_based_cleanup_mode_desc, query_capture_mode_desc
--FROM sys.database_query_store_options;
--select count(*) from sys.query_store_query
--ALTER DATABASE [system-abccompany] SET QUERY_STORE (MAX_STORAGE_SIZE_MB =100);
--ALTER DATABASE [system-abccompany] SET QUERY_STORE CLEAR;
--ALTER DATABASE [system-abccompany] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
--ALTER DATABASE [system-abccompany] SET QUERY_STORE (OPERATION_MODE = READ_ONLY);
/*check forced plan query
use [system-abccompany]
go
SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
force_failure_count, last_force_failure_reason_desc,p.query_plan,p.*
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;
*/
/* find out if the query is captured.
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id
where Txt.query_sql_text like '%SELECT * FROM (SELECT tblAssignmentStatus%'
*/
-- get the query_id and the good plan_id which last_duration is good
SELECT qsp.query_id, QSP.plan_id, QST.query_sql_text, qsq.query_hash, qsp.query_plan_hash, QSRS.last_duration,
QSRS.avg_duration, qsp.query_plan, QSQ.last_execution_time, -- timezone is not right, show 7 hours ahead, also sometimes it's even less than the first_execution_time
QSRS.first_execution_time, -- timezone is not right, show 7 hours ahead
QSRS.avg_logical_io_reads,QSRS.avg_logical_io_writes
FROM sys.query_store_plan AS QSP JOIN sys.query_store_query AS QSQ ON QSP.query_id = QSQ.query_id
JOIN sys.query_store_query_text AS QST ON QSQ.query_text_id = QST.query_text_id
JOIN sys.query_store_runtime_stats QSRS ON QSP.plan_id =QSRS.plan_id
where QST.query_sql_text like '%SELECT%tblAssignmentStatuses%AssignmentStatusesID%AS%'
order by QSQ.last_execution_time desc
--EXEC sp_query_store_force_plan @query_id = 125, @plan_id = 128
/* is there a forced plan?
SELECT p.query_id
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;
*/
-- stop the plan capture after the good plan is caught.
ALTER DATABASE [system-abccompany] SET QUERY_STORE (OPERATION_MODE = READ_ONLY);
/*remove redundant query id from query store, method 1, there are still some leftover in query_store_query, you can use method 2 below to remove
DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
SELECT q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE is_forced_plan <> 1
ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
EXEC sp_query_store_remove_query @id;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;
*/
/*remove redundant query id from query store. method 2, it will delete any query_id that's not using force plan.
DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
SELECT q.query_id from sys.query_store_query AS q
WHERE query_id not in
(
SELECT p.query_id
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1
)
ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
EXEC sp_query_store_remove_query @id;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;
*/
Comments
Post a Comment