SET SHOWPLAN_ALL (Transact-SQL)

Область применения: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure

Приводит к тому, что Microsoft SQL Server не выполняет инструкции Transact-SQL. Вместо этого SQL Server возвращает подробные сведения о выполнении инструкций и предоставляет оценку требований к ресурсам для выполнения этих инструкций.

Topic link iconСинтаксические обозначения в Transact-SQL

Синтаксис

  
SET SHOWPLAN_ALL { ON | OFF }  

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Remarks

Задание параметра инструкции SET SHOWPLAN_ALL происходит во время выполнения или запуска инструкций, а не во время синтаксического анализа.

Если для параметра SET SHOWPLAN_ALL установлено значение ON, то SQL Server возвращает подробные сведения о планируемом способе выполнения каждой инструкции, не выполняя их, и инструкции Transact-SQL не выполняются. Если для этого параметра установлено значение ON, данные обо всех последующих инструкциях Transact-SQL возвращаются до тех пор, пока для этого параметра не будет установлено значение OFF. Например, если инструкция CREATE TABLE была выполнена, когда для SET SHOWPLAN_ALL было установлено значение ON, SQL Server возвращает сообщение об ошибке для всех последующих инструкций SELECT, в которых используется данная таблица, уведомляя пользователей о том, что указанная таблица не существует. Следовательно, последующие ссылки на эту таблицу не действуют. Если для параметра SET SHOWPLAN_ALL установлено значение OFF, SQL Server выполняет инструкции без создания отчета.

Инструкция SET SHOWPLAN_ALL предназначена для использования приложениями, которые могут обрабатывать ее вывод. Используйте инструкцию SET SHOWPLAN_TEXT для возврата доступного для чтения вывода для приложений командной строки Microsoft Win32, таких как служебная программа osql.

Инструкции SET SHOWPLAN_TEXT и SET SHOWPLAN_ALL не могут использоваться внутри хранимой процедуры; они должны быть единственными инструкциями в пакете.

Инструкция SET SHOWPLAN_ALL возвращает данные в виде набора строк, формирующего иерархическое дерево, представляющее последовательность шагов, выполняемых обработчиком запросов SQL Server в ходе выполнения каждой инструкции. Каждой инструкции, отраженной в выходных данных, соответствует одна строка с текстом инструкции, за которой следуют несколько строк с подробными описаниями шагов выполнения. В таблице ниже приведены столбцы, содержащиеся в выводе.

Имя столбца Описание
StmtText Для строк, тип которых отличается от PLAN_ROW, этот столбец содержит текст инструкции Transact-SQL. В строках типа PLAN_ROW этот столбец содержит описание операции. Этот столбец содержит физический оператор и может также, при необходимости, содержать логический оператор. За этим столбцом может идти описание, зависящее от физического оператора. Дополнительные сведения см. в разделе Справочник по логическим и физическим операторам Showplan.
StmtId Номер инструкции в текущем пакете.
NodeId Идентификатор узла в текущем запросе.
Parent Идентификатор узла родительского шага.
PhysicalOp Физическая реализация алгоритма для узла. Только для строк типа PLAN_ROWS.
LogicalOp Оператор реляционной алгебры, которому соответствует данный узел. Только для строк типа PLAN_ROWS.
Argument Предоставляет дополнительные сведения о выполняемой операции. Содержимое этого столбца зависит от физического оператора.
DefinedValues Содержит список значений с разделителями-запятыми, вводимых данным оператором. Эти значения могут представлять собой вычисляемые выражения, присутствующие в текущем запросе (например в списке SELECT или предложении WHERE), или внутренние значения, введенные обработчиком запросов для выполнения данного запроса. Затем на эти определенные значения можно ссылаться из других частей запроса. Только для строк типа PLAN_ROWS.
EstimateRows Предполагаемое количество строк вывода от данного оператора. Только для строк типа PLAN_ROWS.
EstimateIO Предполагаемые затраты на ввод-вывод* для данного оператора. Только для строк типа PLAN_ROWS.
EstimateCPU Предполагаемая загрузка ЦП* для данного оператора. Только для строк типа PLAN_ROWS.
AvgRowSize Предполагаемый средний размер строк (в байтах), передаваемых через данный оператор.
TotalSubtreeCost Предполагаемая (совокупная) стоимость* данной операции и всех дочерних операций.
OutputList Содержит список столбцов с разделителями-запятыми, проецируемых текущей операцией.
Warnings Содержит список предупреждений с разделителями-запятыми, относящихся к текущей операции. Предупреждения могут содержать строку «NO STATS:()» со списком столбцов. Это означает, что оптимизатор запросов попытался принять решение на основе статистики для данного столбца, но она отсутствовала. Поэтому оптимизатору запросов пришлось принимать решение самостоятельно, что могло привести к выбору неэффективного плана запроса. Дополнительные сведения о создании или обновлении статистик столбцов (которые помогают оптимизатору запросов выбирать более эффективный план запроса) см. в разделе UPDATE STATISTICS. В этом столбце также может содержаться строка «MISSING JOIN PREDICATE», означающая, что производится соединение (с использованием таблиц) без предиката соединения. Случайное удаление предиката соединения может привести к созданию запроса, чье выполнение займет значительно больше времени, чем предполагалось, и вернет большой результирующий набор. При появлении этого предупреждения убедитесь, что предикат соединения не был удален случайно.
Type Тип узла. Для родительского узла каждого запроса это тип инструкции Transact-SQL (например SELECT, INSERT, EXECUTE и т. д.). Для дочерних узлов, соответствующих планам выполнения, это тип PLAN_ROW.
Parallel 0 = оператор выполняется не в параллельном режиме.

1 = оператор выполняется в параллельном режиме.
EstimateExecutions Предполагаемое количество вызовов данного оператора при выполнении текущего запроса.

*Значения показателей основаны на внутреннем измерении времени, а не на показаниях настенных часов. Они используются для определения относительной стоимости плана в сравнении с другими планами.

Разрешения

Чтобы использовать инструкцию SET SHOWPLAN_ALL, должны быть необходимые разрешения на выполнение инструкций, для которых запускается инструкция SET SHOWPLAN_ALL, и разрешение SHOWPLAN на доступ ко всем базам данных, содержащим используемые в запросах объекты.

Чтобы инструкции SELECT, INSERT, UPDATE, DELETE, EXEC stored_procedure и EXEC user_defined_function создавали планы SHOWPLAN, пользователь должен:

  • обладать необходимыми разрешениями на выполнение инструкций Transact-SQL;

  • Обладать разрешениями SHOWPLAN для всех баз данных, содержащих объекты (например таблицы, представления и т. д.), на которые ссылаются инструкции Transact-SQL.

Для всех остальных инструкций (например, DDL, USE database_name, SET, DECLARE, динамического SQL и т. д.) требуются лишь соответствующие разрешения на выполнение инструкций Transact-SQL.

Примеры

В следующих двух инструкциях используются параметры SET SHOWPLAN_ALL для определения способа анализа и оптимизации SQL Server использования индексов в запросах.

В предложении WHERE первого запроса оператор сравнения «равно» (=) применяется к индексированному столбцу. При этом в столбец LogicalOp записывается значение "Поиск в кластеризованном индексе", а в столбец Argument — имя индекса.

Во втором запросе в предложении WHERE используется оператор LIKE. В результате этого SQL Server использует просмотр кластеризованного индекса и выполнят поиск данных, удовлетворяющих условию в предложении WHERE. В столбец LogicalOp записывается значение "Просмотр кластеризованного индекса", имя индекса появляется в столбце Argument, также в столбец LogicalOp записывается значение "Фильтр", а в соответствующем столбце Argument появляется условие предложения WHERE.

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

USE AdventureWorks2012;  
GO  
SET SHOWPLAN_ALL ON;  
GO  
-- First query.  
SELECT BusinessEntityID   
FROM HumanResources.Employee  
WHERE NationalIDNumber = '509647174';  
GO  
-- Second query.  
SELECT BusinessEntityID, EmergencyContactID   
FROM HumanResources.Employee  
WHERE EmergencyContactID LIKE '1%';  
GO  
SET SHOWPLAN_ALL OFF;  
GO  

См. также:

Инструкции SET (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)
SET SHOWPLAN_XML (Transact-SQL)