Принудительные планы в SQL Server перезаписывают хэш запроса

Автор: Paul White, SQL Server Forced Plans Overwite the Query Hash

Если вы «принудительно» задаёте план, любым методом, включая руководство планом (Plan Guides), хранилище запросов (Query Store) и автоматическое исправление плана (Automatic Plan Correction), результирующий план будет иметь свой query_hash перезаписанным значением query_plan_hash.

Другими словами, хэш плана и хэш плана запроса получат значение хэша плана запроса. Реальное значение хэша запроса просто теряется. 🤦

Это нарушит работу всего, где вы используете query_hash для любых целей, включая скрипты и инструменты.

Пример

Один из примеров воспроизведения:

1. Создайте таблицу.
2. Выполните выборку из неё, сохранив план выполнения.
3. Скопируйте XML showplan для выборки.
4. Запустите выборку снова с XML showplan в подсказке USE PLAN.

Код:

DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1 (c1 integer NOT NULL);
GO
SELECT c1
FROM dbo.T1
OPTION (USE PLAN N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.4165.4"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT c1 &#xd;&#xa;FROM dbo.T1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x31B14566F8F8A256" QueryPlanHash="0x10D77A8E4F35F994" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="80"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="12103680"></OptimizerHardwareDependentProperties><RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.0032035" EstimateCPU="7.96e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="" Schema="[dbo]" Table="[T1]" Column="c1"></ColumnReference></OutputList><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="" Schema="[dbo]" Table="[T1]" Column="c1"></ColumnReference></DefinedValue></DefinedValues><Object Database="" Schema="[dbo]" Table="[T1]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>');

«Принудительный» план выполнения имеет идентичные значения query_hash и query_plan_hash:

QueryHash="0x10D77A8E4F35F994"
QueryPlanHash="0x10D77A8E4F35F994"

Обратите внимание на идентичные хэши.

Влияние

Эта ошибка затрагивает SQL Server с версии 2008 до версии 2022 CU16 включительно.

Пока Microsoft не исправит это (и не перенесёт исправление на 2008 🤣), любой план, подвергнутый «принуждению», будет иметь некорректный хэш запроса. 🥳

Примечание: Затронут только поиск с руководимым планом. Указание отдельных подсказок запроса (кроме USE PLAN, разумеется) в руководимом плане (или через аналогичную функцию) не провоцирует такое некорректное поведение.

Я видел сообщение о таком поведении в сообщениях в блогах и в статье блога Kendra Little.

«Облико Морале» планы

По смежной теме, кажется, существует некоторая путаница вокруг значения «морально эквивалентного» плана выполнения — термина, придуманного Коннором Каннингемом из команды разработчиков оптимизатора запросов Microsoft. Это может быть полезным сокращённым термином, но только если вы понимаете, что именно он сокращает.

Когда вы «принудительно» задаёте план выполнения, созданный план может не точно соответствовать шаблону. Цель состоит в том, чтобы полученный план был очень похож на план-руководство.

Microsoft признаёт это в документации (выделено в тексте):

Полученный при применении этой возможности план выполнения будет таким же, как принудительно применяемый план, или очень близким к нему. Так как итоговый план может не совпадать с планом, указанным в структуре плана, производительность этих планов может различаться. В редких случаях возможна значительная негативная разница в производительности, и тогда администратору следует удалить принудительный план.

Руководство, а не правило

Термин «руководимый план» технически более точен, чем «принудительный план».

XML-представление плана используется для направления оптимизатора запросов через его обычный процесс оптимизации. XML анализируется в «дерево подсказок» с формой, похожей на внутреннее дерево логических операторов, сформированное путём синтаксического анализа и преобразования исходного оператора T-SQL в форму, удобную для обработчика запросов.

Дерево подсказок направляет оптимизатор запросов по мере его прохождения через обычный поиск оптимизации на основе стоимости. Для ясности: это не подсказки T-SQL; SQL Server использует форму, свойства и типы узлов в дереве подсказок для направления поиска.

Направляемый поиск

Что ж, это не совсем обычный поиск, потому что все функции оптимизатора включены на случай, если они понадобятся для достижения цели. В старых версиях SQL Server это означало, что поиск с руководимым планом всегда сразу переходил в стратегию search 2 (полная оптимизация). В последних версиях ускоренное принуждение планов позволяет направляемый поиск в search 1.

Работая сверху вниз, любое преобразование или оптимизация, которое не могло — или не смогло — произвести результат, описанный соответствующим узлом дерева подсказок, отбрасывается. Эффект заключается в ограничении оптимизатора путями исследования, которые приводят к той же структуре, что описана подсказками.

Различия

SQL Server использует предоставленное XML-представление в качестве руководства, а не строгого шаблона, по нескольким причинам.

• XML Showplan, как и любое текстовое представление плана, не является полным описанием внутреннего исполняемого плана, который по сути представляет собой динамическую программу, способную напрямую исполняться для получения результатов и эффектов, логически описанных оператором T-SQL. Другими словами, в исходном коде просто недостаточно информации для непосредственного создания исполняемого плана.
• Следование обычным путям оптимизатора гарантирует, что результат — это план, который оптимизатор мог бы создать естественным образом. Таким образом, он гарантированно соблюдает все логические семантики исходного T-SQL.
• Разрешение пользователю или программе отправлять произвольные исполняемые файлы, описанные XML, могло бы открыть путь к проблемам безопасности или неопределённому поведению на сервере.

Всё это означает, что план, в конечном итоге выбранный оптимизатором, часто не будет точно соответствовать руководимому плану. Предполагается, что он будет соответствовать всем важным аспектам, захваченным в XML, но реализация не является исчерпывающей даже с учётом ограниченной доступной детализации исходного кода. Например, в текущем процессе направляющего поиска предварительная выборка вложенных циклов не считается важным свойством для соответствия.

Причины

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

Существует множество других ограничений. Некоторые из них в принципе можно было бы устранить довольно легко, но нужно помнить, что Microsoft — небольшая компания с ограниченным бюджетом, и вместо этого у неё есть важные функции, такие как Big Data Clusters, над которыми нужно работать.

Эффекты

План, созданный в процессе направляющего поиска, будет визуально очень похож на предоставленный XML-план. Он может отличаться в деталях, считающихся менее важными или просто не захваченных в XML-представлении.

Поскольку хэш результирующего плана (query_plan_hash) зависит от всех деталей исполняемого плана (включая многие, невидимые пользователям в любой форме), готовый план часто будет иметь другой query_plan_hash, отличный от исходного. Однако хеш на входе, основанный на полученном логическом дереве (query_hash), будет тем же (за исключением ошибок).

В заключение

Термин «морально эквивалентный» предназначен для отражения многих нюансов, описанных выше. Не знаю, насколько это удачно, или какое отношение мораль имеет ко всему этому, но вот так.

Направляющий план может создавать планы с незначительными вариациями по сравнению с исходным планом. Иногда эти «незначительные вариации» могут иметь большое влияние на производительность. Было время, когда Microsoft была заинтересована в получении информации о таких случаях. Понятия не имею, актуально ли это до сих пор.