SET SHOWPLAN_XML (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics (somente pool de SQL dedicado)

Faz com que o SQL Server não execute instruções Transact-SQL. Em vez disso, o SQL Server retorna informações detalhadas sobre como as instruções serão executadas no formulário de um documento XML bem-definido.

Convenções de sintaxe de Transact-SQL

Sintaxe

SET SHOWPLAN_XML { ON | OFF }

Comentários

A configuração de SHOWPLAN_XML é definida durante a execução ou o tempo de execução, e não no momento da análise.

Quando SET SHOWPLAN_XML for ON, o SQL Server retornará informações do plano de execução para cada instrução sem executá-las e as instruções Transact-SQL não serão executadas. Depois que essa opção estiver definida como ON, as informações do plano de execução sobre todas as instruções Transact-SQL subsequentes serão retornadas, até que a opção seja definida como OFF. Por exemplo, se uma instrução CREATE TABLE for executada enquanto SET SHOWPLAN_XML estiver definido como ON, o SQL Server retornará uma mensagem de erro de uma instrução SELECT subsequente, envolvendo essa mesma tabela. A tabela especificada não existe. Portanto, haverá falha nas referências subsequentes para essa tabela. Quando SET SHOWPLAN_XML for OFF, o SQL Server executará as instruções sem gerar um relatório.

SET SHOWPLAN_XML deve retornar uma saída como nvarchar(max) para aplicativos como o utilitário sqlcmd, em que a saída XML será usada em seguida por outras ferramentas para exibir e processar as informações do plano de consulta.

Observação

A exibição de gerenciamento dinâmico sys.dm_exec_query_plan retorna as mesmas informações que SET SHOWPLAN XML para o tipo de dados XML. Essas informações são retornadas da coluna query_plan de sys.dm_exec_query_plan. Para obter mais informações, confira sys.dm_exec_query_plan (Transact-SQL).

SET SHOWPLAN_XML não pode ser especificado em um procedimento armazenado. Ele precisa ser a única instrução em um lote.

SET SHOWPLAN_XML retorna informações como um conjunto de documentos XML. Cada lote posterior à instrução SET SHOWPLAN_XML ON é refletido na saída por um único documento. Cada documento contém o texto das instruções do lote, seguido dos detalhes das etapas de execução. O documento mostra os custos estimados, os números de linhas, os índices acessados e os tipos de operadores executados, a ordem de junção e mais informações sobre os planos de execução.

Observação

Se Incluir Plano de Execução Real estiver selecionado no Server Management Studio, essa opção SET não produzirá a saída do plano de execução XML. Desmarque o botão Incluir Plano de Execução Real antes de usar esta opção SET.

Os planos de execução estimados por meio do SSMS e do SET SHOWPLAN_XML estão disponíveis para pools de SQL dedicados (antigo SQL DW) e pools de SQL dedicados no Azure Synapse Analytics. Para recuperar um plano de execução real para pools de SQL dedicados (antigo SQL DW) e pools de SQL dedicados no Azure Synapse Analytics, há comandos diferentes. Para mais informações, confira Monitorar a carga de trabalho do pool de SQL dedicado do Azure Synapse Analytics usando DMVs.

Local de saída de SHOWPLAN

O documento que contém o esquema XML para a saída do XML por SET SHOWPLAN_XML é copiado durante a instalação em um diretório local no computador no qual o Microsoft SQL Server está instalado. O documento pode ser encontrado na unidade que contém os arquivos de instalação do SQL Server, em um caminho semelhante ao seguinte:

  • \Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

No caminho anterior, o nó 130\ é usado pelo SQL Server 2016. O número de 130 deriva do primeiro nó do valor retornado por SELECT @@VERSION, que é 13. Para o SQL Server 2017, o caminho usaria 140\, porque o primeiro nó do seu valor @@VERSION é 14. Para o SQL Server 2019, o primeiro o valor de @@VERSION é 15. Para o SQL Server 2022, o primeiro o valor de @@VERSION é 16.

O Esquema de Plano de Execução também pode ser encontrado em Esquemas XML do Microsoft SQL Server.

Permissões

Para usar SET SHOWPLAN_XML, é preciso ter permissões suficientes para executar as instruções nas quais SET SHOWPLAN_XML é executado e é preciso ter a permissão SHOWPLAN para todos os bancos de dados que contenham objetos referenciados.

Para instruções SELECT, INSERT, UPDATE, DELETE, EXEC *stored_procedure* e EXEC *user_defined_function*, para produzir um Plano de Execução, o usuário deve:

  • Ter as permissões apropriadas para executar as instruções Transact-SQL.

  • Ter permissão SHOWPLAN em todos os bancos de dados que contenham objetos referenciados pelas instruções Transact-SQL, como tabelas, exibições e assim por diante.

Para todas as outras instruções, como USE *database_name*, SET, DECLARE, SQL dinâmico, e assim por diante, são necessárias apenas as permissões adequadas para executar as instruções Transact-SQL.

Exemplos

As duas instruções a seguir usam as configurações SET SHOWPLAN_XML para mostrar o modo pelo qual o SQL Server analisa e otimiza o uso de índices em consultas.

A primeira consulta usa o operador de comparação Igual (=) na cláusula WHERE em uma coluna indexada. A segunda consulta usa o operador LIKE na cláusula WHERE. Isso força o SQL Server a usar uma verificação de índice clusterizado e a localizar os dados que satisfazem a condição da cláusula WHERE. Os valores nos atributos EstimateRows e EstimatedTotalSubtreeCost são menores na primeira consulta indexada, indicando que ela é processada com mais rapidez e usa menos recursos que a consulta não indexada.

USE AdventureWorks2022;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;

Próximas etapas