SQL-Ex blog

Навязывание плана (Plan Forcing) с табличными переменными

Сначала давайте посмотрим, как фактически работает навязывание плана с табличными переменными. Вот простой запрос, использующий табличную переменную:

DECLARE @MyTableVar TABLE

(

	City VARCHAR(50)

);

INSERT INTO @MyTableVar

(

City

)

VAL	UES

('London' -- City - varchar(50)

);

SELECT a.AddressID,

	a.AddressLine1,

	a.City,

	sp.Name

FROM Person.Address AS a

	JOIN @MyTableVar AS mtv

	ON a.City = mtv.City

	Person.StateProvince AS sp

	ON sp.StateProvinceID = a.StateProvinceID;

Мы можем извлечь id запроса и плана из хранилища запросов (Query Store) и связать их:

DECLARE @QueryId INT,

	@PlanID INT;

SELECT @QueryId = qsq.query_id,

	@PlanID = qsp.plan_id

FROM sys.query_store_query AS qsq

	JOIN sys.query_store_query_text AS qsqt

	ON qsqt.query_text_id = qsq.query_text_id

	JOIN sys.query_store_plan AS qsp

	ON qsp.query_id = qsq.query_id

WHERE qsqt.query_sql_text LIKE 'SELECT a.AddressID,%';

EXEC sys.sp_query_store_force_plan @QueryId, @PlanID;

Замечание. Я могу так сделать только потому, что работаю с чистым хранилищем. Если вы выполните другие запросы, которые начинаются с ‘SELECT a.AddressID,’, этот код не будет работать, и вам придется быть более конкретным.

Так вот, если мы перезапустим исходный скрипт, а затем либо посмотрим на свойства первого оператора в плане (который покажет Use Plan = True), либо обратимся к хранилищу запросов, мы сможем утверждать, что навязывание плана работало с табличной переменной. Вот код, который может об этом вам сообщить:

SELECT query_id,

plan_id,

is_forced_plan,

force_failure_count,

last_force_failure_reason_desc

FROM sys.query_store_plan

WHERE is_forced_plan = 1;

Вывод на моей системе выглядит следующим образом:


Итак, где возникает проблема?

Неудачное навязывание плана

Сейчас мы слегка изменим оригинальный скрипт. Я собираюсь сделать столбец в табличной переменной первичным ключом:

DECLARE @MyTableVar TABLE

(

City VARCHAR(50) PRIMARY KEY

);

INSERT INTO @MyTableVar

(

City

)

VALUES

('London' -- City - varchar(50)

);

SELECT a.AddressID,

a.AddressLine1,

a.City,

sp.Name

FROM Person.Address AS a

JOIN @MyTableVar AS mtv

ON a.City = mtv.City

JOIN Person.StateProvince AS sp

ON sp.StateProvinceID = a.StateProvinceID;

Теперь мне нужно заглянуть в хранилище запросов, чтобы проверить, был ли создан новый запрос, новый план или что-то еще; и тогда мы модифицируем и другой запрос тоже.

SELECT qsq.query_id,

	qsq.query_hash,

	CAST(qsp.query_plan AS XML) AS QueryPlan,

	qsp.plan_id,

	qsp.query_plan_hash,

	qsqt.query_sql_text

FROM sys.query_store_query AS qsq

	JOIN sys.query_store_plan AS qsp

	ON qsp.query_id = qsq.query_id

	JOIN sys.query_store_query_text AS qsqt

	ON qsqt.query_text_id = qsq.query_text_id

WHERE qsqt.query_sql_text LIKE 'SELECT a.AddressID,%';

Вот результаты:



Вы можете видеть, что, хотя мы никоим образом не изменили рассматриваемый запрос, Query Store считает, что у него есть новый запрос и новый план. Я объясню почему через минуту. Сначала давайте попробуем навязать план:

EXEC sys.sp_query_store_force_plan 7,2;

Имея это в виду, давайте снова выполним запрос с первичным ключом, а затем проверим Query Store, чтобы увидеть, навязан был план или нет:



Упс! Навязывание плана завершилось неудачно с причиной NO_INDEX. Но это же не так. Мы создали первичный ключ. Это прямо в скрипте. Однако обратите внимание, что мы полагались на имена по умолчанию для первичного ключа. Оно генерируется, новое всякий раз, когда создается табличная переменная. Это означает, что вы не можете навязать план, поскольку план, который вы пытаетесь навязать неверен из-за того, что он имеет отличный первичный ключ.

Заключение

Одним из условий успешного использования навязанного плана является понимание того, что вы можете навязать только действительный план. Изменение имен объектов, подобное использованию имен ключей по умолчанию, приведет к неудаче.