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