Хранилище запросов, навязывание плана и табличные переменные

Пересказ статьи Grant Fritchey. Query Store, Plan Forcing and Table Variables

Steinar Andersen рассказал о проблеме, с которой он столкнулся при навязывании планов, которые содержали табличные переменные.

Без паники. Конечно, по большей части вы можете навязывать план с табличными переменными. У Стейнара была довольно специфическая проблема. Прежде чем я перейду к объяснению, замечу, что Стейнар сам все понял. Когда он описывал проблему, я сразу понял, каким будет его вывод. Меня подвигло на этот пост сообщение Стейнара о том, что он нигде не нашел обсуждения этой проблемы. Так что давайте поговорим о ней.

Навязывание плана (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. Но это же не так. Мы создали первичный ключ. Это прямо в скрипте. Однако обратите внимание, что мы полагались на имена по умолчанию для первичного ключа. Оно генерируется, новое всякий раз, когда создается табличная переменная. Это означает, что вы не можете навязать план, поскольку план, который вы пытаетесь навязать неверен из-за того, что он имеет отличный первичный ключ.

Заключение

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

Добавить комментарий