Saturday, June 22, 2019

SQL Server Query Store testing tools - USE PLAN Hint derived from plan_id

I use this stored procedure to generate a 'USE PLAN' hint style query, based on the Query Store plan_id supplied as an input parameter.



CREATE PROCEDURE useplan_query_from_planid @plan_id BIGINT
AS
DECLARE @amp NVARCHAR(MAX)
SELECT @amp = qsqt.query_sql_text + N' OPTION (
USE PLAN N''' + qsp.query_plan + ''')'
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsp.plan_id = @plan_id
IF @amp IS NOT NULL EXEC (@amp)
/* 
   20190622
   input parameter: 

   @plan_id
   - make query text from Query Store query_sql_text for @plan_id
   - add planXML in use plan hint
   - execute the query

   assumptions:
   query_sql_text does not already contain OPTION() hints

   expected outcomes:
   the relevant query will use a Query Store plan_id
      other than the one specified by the plan_id input parameter.
      it must do so because the query text has changed
   resulting in a different query_text_id 
      and a different query_id
*/



Q

No comments:

Post a Comment