SET SHOWPLAN XML: примеры (SQL)

Примеры использования SET SHOWPLAN_XML

Включение режима и выполнение простого запроса.

SET SHOWPLAN_XML ON;
GO

SELECT * FROM [Sales].[SalesOrderHeader] h
INNER JOIN [Sales].[SalesOrderDetail] d ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate > '2011-07-01';
GO

SET SHOWPLAN_XML OFF;
GO

Результатом будет не набор строк данных, а XML-план в одном столбце. Пример фрагмента результата:

<ShowPlanXML xmlns='http://schemas.microsoft.com/sqlserver/2004/07/showplan' Version='1.6' Build='15.0.2000'>
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple ... >
          <QueryPlan CachedPlanSize='32' ... >
            <RelOp NodeId='0' PhysicalOp='Nested Loops' ... >
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Пример использования для отдельного запроса в рамках пакета.

SET SHOWPLAN_XML ON;
GO
-- Этот запрос не выполнится, вернется только план.
UPDATE [Production].[Product]
SET ListPrice = ListPrice * 1.1
WHERE ProductID = 707;
GO
SET SHOWPLAN_XML OFF;

Похожие функции в MS SQL

SET SHOWPLAN_TEXT и SET SHOWPLAN_ALL

Более старые инструкции, возвращающие план выполнения в текстовом формате. SET SHOWPLAN_ALL предоставляет более структурированный текстовый вывод с колонками. Они менее информативны и удобны для анализа, чем XML-формат.

STATISTICS XML

Инструкция SET STATISTICS XML ON выполняет запрос и после его завершения возвращает как фактические результаты, так и XML-план выполнения, содержащий реальные, а не предполагаемые метрики (количество строк, затраченное время). Предпочтительна для анализа фактического выполнения после оптимизации.

Динамические административные представления (DMV)

Запросы к представлениям, таким как sys.dm_exec_query_plan, позволяют извлекать XML-планы для уже выполненных и кэшированных запросов. Используются для ретроспективного анализа.

SET SHOWPLAN_XML оптимален для предварительного, «сухого» анализа без изменения данных. STATISTICS XML нужен для проверки реального выполнения. DMV полезны для мониторинга рабочей нагрузки.

Альтернативы в других СУБД

MySQL

Используется команда EXPLAIN FORMAT=XML.

EXPLAIN FORMAT=XML
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

<?xml version='1.0' encoding='utf-8'?>
<explain>
  <query_block>
    <table table='o' />
  </query_block>
</explain>

Oracle

Применяется команда EXPLAIN PLAN FOR с последующим выбором из DBMS_XPLAN.DISPLAY. Для XML формата — DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'XML').

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT DBMS_XPLAN.DISFORMAT('PLAN_TABLE', NULL, 'XML') FROM dual;

PostgreSQL

Команда EXPLAIN (FORMAT XML).

EXPLAIN (FORMAT XML)
SELECT * FROM pg_user;

SQLite

Команда EXPLAIN QUERY PLAN возвращает текстовый план. Поддержки XML нет.

EXPLAIN QUERY PLAN
SELECT * FROM t1 WHERE a=1;

Sybase ASE

Используется SET SHOWPLAN ON, аналогичная старая текстововая версия из SQL Server. Современные версии также поддерживают XML-вывод через другие средства, например, sa_showplan().

Типичные ошибки

Попытка выполнения в транзакции с модификацией данных

При активированном SHOWPLAN_XML ON инструкции DML (UPDATE, INSERT, DELETE, MERGE) не выполняются. Это может нарушить логику скрипта, ожидающего реального изменения данных.

SET SHOWPLAN_XML ON;
BEGIN TRANSACTION;
-- Следующий UPDATE не изменит ни одной строки, только вернет план.
UPDATE Production.Product SET Name = 'Test' WHERE ProductID = 1;
-- Ошибка логики: последующий SELECT не увидит изменений.
SELECT Name FROM Production.Product WHERE ProductID = 1;
ROLLBACK TRANSACTION;
SET SHOWPLAN_XML OFF;

Неправильная область действия

Инструкция SET SHOWPLAN_XML действует на уровне сеанса (сессии). Ее активация в одном окне запросов влияет на все последующие выполнения в этом же сеансе, что может запутать пользователя.

Игнорирование пакетного разделителя GO

В SQL Server Management Studio без использования GO для отделения команды SET от анализируемого запроса может привести к неочевидному поведению.

Изменения в последних версиях

Сама инструкция SET SHOWPLAN_XML остается стабильной. Основные изменения касаются содержимого возвращаемого XML-документа, который расширяется с каждым крупным обновлением SQL Server. Например, в SQL Server 2016 и выше в план могут добавляться элементы, связанные с обработкой индексов columnstore, временными таблицами в памяти или новыми операторами, такими как BATCH MODE. Рекомендуется использовать последние версии средств просмотра планов (таких как SSMS), которые корректно интерпретируют новые атрибуты XML.

Расширенные примеры

Анализ плана для сложного запроса с CTE и оконными функциями

SET SHOWPLAN_XML ON;
GO

WITH SalesCTE AS (
    SELECT 
        CustomerID,
        YEAR(OrderDate) as OrderYear,
        TotalDue,
        RANK() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) as RankByAmount
    FROM [Sales].[SalesOrderHeader]
)
SELECT CustomerID, OrderYear, TotalDue
FROM SalesCTE
WHERE RankByAmount = 1;
GO

SET SHOWPLAN_XML OFF;
GO

В XML-плане можно будет увидеть операторы Segment, Sequence Project и Filter, соответствующие работе оконной функции и фильтрации.

Использование XQuery для извлечения конкретной информации из плана

XML-план можно сохранить в переменную и проанализировать.

DECLARE @PlanXML xml;
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Person.Person WHERE LastName LIKE 'S%';
GO
-- Результат плана нужно вручную скопировать в переменную.
-- В SSMS результат возвращается как набор строк.
SET SHOWPLAN_XML OFF;

-- Пример анализа скопированного XML.
DECLARE @MyPlanXML xml = '...'; -- Вставьте сюда скопированный XML
SELECT 
    n.value('(@PhysicalOp)[1]', 'nvarchar(50)') as PhysicalOp,
    n.value('(@LogicalOp)[1]', 'nvarchar(50)') as LogicalOp,
    n.value('(@EstimatedTotalSubtreeCost)[1]', 'float') as EstimatedCost
FROM @MyPlanXML.nodes('//RelOp') AS T(n);

Сравнение двух планов путем их сохранения

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

CREATE TABLE #PlanCache (PlanHandle varbinary(64), PlanXML xml);

SET SHOWPLAN_XML ON;
GO
-- Запрос до индекса
SELECT BusinessEntityID, FirstName FROM Person.Person WHERE LastName = 'Smith';
GO
SET SHOWPLAN_XML OFF;
-- Сохранение плана (условно, т.к. SHOWPLAN_XML не кэширует план).
-- На практике для кэшированных планов используют sys.dm_exec_query_plan.

Для реального сравнения лучше использовать STATISTICS XML или DMV.