Como obtenho um plano de execução de consulta?

No Microsoft SQL Server como posso obter um plano de execução de consulta para uma consulta / procedimento armazenado?

Existem vários methods de obtenção de um plano de execução, que um deles irá depender das suas circunstâncias. Normalmente, você pode usar o SQL Server Management Studio para obter um plano, mas se por algum motivo não puder executar sua consulta no SQL Server Management Studio, talvez seja útil obter um plano por meio do SQL Server Profiler ou inspecionar o cache do plano.

Método 1 – usando o SQL Server Management Studio

O SQL Server vem com alguns resources interessantes que facilitam muito a captura de um plano de execução. Basta verificar se o item de menu “Incluir Plano de Execução Real” (localizado no menu “Consulta”) está marcado e executar sua consulta como normal. .

Incluir item de menu Plano de Execução de Ação

Se você estiver tentando obter o plano de execução para instruções em um procedimento armazenado, execute o procedimento armazenado da seguinte forma:

exec p_Example 42 

Quando sua consulta for concluída, você deverá ver uma guia adicional intitulada “Plano de execução” no painel de resultados. Se você executou muitas instruções, poderá ver muitos planos exibidos nessa guia.

Captura de tela de um plano de execução

A partir daqui, você pode inspecionar o plano de execução no SQL Server Management Studio ou clicar com o botão direito no plano e selecionar “Salvar plano de execução como …” para salvar o plano em um arquivo no formato XML.

Método 2 – Usando as opções SHOWPLAN

Esse método é muito semelhante ao método 1 (na verdade, isso é o que o SQL Server Management Studio faz internamente), no entanto, incluí-lo para integridade ou se você não tiver o SQL Server Management Studio disponível.

Antes de executar sua consulta, execute uma das instruções a seguir. A declaração deve ser a única declaração no lote, ou seja, você não pode executar outra instrução ao mesmo tempo:

 SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- The is the recommended option to use 

Estas são opções de conexão e, portanto, você só precisa executar isso uma vez por conexão. A partir deste ponto, todas as instruções executadas serão acompanhadas por um conjunto de resultados adicional contendo seu plano de execução no formato desejado – simplesmente execute sua consulta como faria normalmente para ver o plano.

Quando estiver pronto, você pode desativar essa opção com a seguinte declaração:

 SET < 

Comparação de formatos de plano de execução

A menos que você tenha uma preferência forte, minha recomendação é usar a opção STATISTICS XML . Essa opção é equivalente à opção “Incluir Plano de Execução Real” no SQL Server Management Studio e fornece a maioria das informações no formato mais conveniente.

  • SHOWPLAN_TEXT – Exibe um plano de execução estimado baseado em texto básico, sem executar a consulta
  • SHOWPLAN_ALL – Exibe um plano de execução estimado baseado em texto com estimativas de custo, sem executar a consulta
  • SHOWPLAN_XML – Exibe um plano de execução estimado baseado em XML com estimativas de custo, sem executar a consulta. Isso é equivalente à opção “Exibir plano de execução estimado …” no SQL Server Management Studio.
  • STATISTICS PROFILE – Executa a consulta e exibe um plano de execução real baseado em texto.
  • STATISTICS XML – Executa a consulta e exibe um plano de execução real baseado em XML. Isso é equivalente à opção “Incluir Plano de Execução Real” no SQL Server Management Studio.

Método 3 – usando o SQL Server Profiler

Se você não puder executar sua consulta diretamente (ou se sua consulta não for executada lentamente quando você a executar diretamente – lembre-se de que deseja executar um plano mal executado), você poderá capturar um plano usando um rastreamento do SQL Server Profiler. A ideia é executar sua consulta enquanto um rastreamento que captura um dos events “Showplan” estiver em execução.

Observe que, dependendo da carga, você pode usar esse método em um ambiente de produção; no entanto, obviamente, você deve ter cuidado. Os mecanismos de criação de perfil do SQL Server são projetados para minimizar o impacto no database, mas isso não significa que não haverá impacto no desempenho. Você também pode ter problemas ao filtrar e identificar o plano correto em seu rastreio se seu database estiver sob uso intenso. Você deve, obviamente, verificar com o seu DBA para ver se eles estão felizes com você fazendo isso em seu precioso database!

  1. Abra o SQL Server Profiler e crie um novo rastreamento conectando-se ao database desejado com o qual você deseja registrar o rastreamento.
  2. Na guia “Seleção de Eventos” marque “Mostrar todos os events”, marque a linha “Desempenho” -> “Mostrar plano XML” e execute o rastreamento.
  3. Enquanto o rastreamento estiver em execução, faça o que for necessário para que a consulta de execução lenta seja executada.
  4. Aguarde a conclusão da consulta e pare o rastreamento.
  5. Para salvar o rastreamento, clique com o botão direito do mouse no plano xml no SQL Server Profiler e selecione “Extrair dados do evento …” para salvar o plano no arquivo no formato XML.

O plano obtido é equivalente à opção “Incluir plano de execução real” no SQL Server Management Studio.

Método 4 – inspecionando o cache de consulta

Se você não puder executar sua consulta diretamente e também não conseguir capturar um rastreio do profiler, ainda poderá obter um plano estimado inspecionando o cache do plano de consulta SQL.

Inspecionamos o cache do plano consultando os DMVs do SQL Server. A seguir, uma consulta básica que listará todos os planos de consulta armazenados em cache (como xml) junto com seu texto SQL. Na maioria dos bancos de dados, você também precisará adicionar cláusulas de filtragem adicionais para filtrar os resultados para apenas os planos nos quais está interessado.

 SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

Execute essa consulta e clique no plano XML para abrir o plano em uma nova janela – clique com o botão direito e selecione “Salvar plano de execução como …” para salvar o plano no arquivo no formato XML.

Notas:

Como há muitos fatores envolvidos (desde a tabela e o esquema do índice até os dados armazenados e as statistics da tabela), você deve sempre tentar obter um plano de execução do database em que está interessado (normalmente aquele que está experimentando um desempenho). problema).

Você não pode capturar um plano de execução para stored procedures criptografados.

Planos de execução “reais” e “estimados”

Um plano de execução real é aquele em que o SQL Server realmente executa a consulta, enquanto um plano de execução estimado do SQL Server trabalha o que ele faria sem executar a consulta. Embora logicamente equivalente, um plano de execução real é muito mais útil, pois contém detalhes adicionais e statistics sobre o que realmente aconteceu ao executar a consulta. Isso é essencial ao diagnosticar problemas em que as estimativas de SQL Servers estão desativadas (por exemplo, quando as statistics estão desatualizadas).

  • Plano de execução estimado e real revisitado

Como faço para interpretar um plano de execução de consulta?

Este é um tópico digno de um livro (gratuito) por si só.

Veja também:

  • Fundamentos do Plano de Execução
  • Lotes de permissão SHOWPLAN e Transact-SQL
  • SQL Server 2008 – Usando Hashes de Consulta e Hashes de Plano de Consulta
  • Analisando o cache do plano do SQL Server

Além da resposta abrangente já publicada às vezes, é útil poder acessar o plano de execução programaticamente para extrair informações. Exemplo de código para isso está abaixo.

 DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID 

Exemplo de Definição de StartCapture

 CREATE PROCEDURE StartCapture @Spid INT, @TraceID INT OUTPUT AS DECLARE @maxfilesize BIGINT = 5 DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36) EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL exec sp_trace_setevent @TraceID, 122, 1, 1 exec sp_trace_setevent @TraceID, 122, 22, 1 exec sp_trace_setevent @TraceID, 122, 34, 1 exec sp_trace_setevent @TraceID, 122, 51, 1 exec sp_trace_setevent @TraceID, 122, 12, 1 -- filter for spid EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid -- start the trace EXEC sp_trace_setstatus @TraceID, 1 

Exemplo de Definição de StopCapture

 CREATE PROCEDURE StopCapture @TraceID INT AS WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), CTE as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData, ObjectID, ObjectName, EventSequence, /*costs accumulate up the tree so the MAX should be the root*/ MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM fn_trace_getinfo(@TraceID) fn CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1) CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost', 'float') AS EstimatedTotalSubtreeCost FROM xPlan.nodes('//sql:RelOp') T(relop)) ca WHERE property = 2 AND TextData IS NOT NULL AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' ) GROUP BY CAST(TextData AS VARCHAR(MAX)), ObjectID, ObjectName, EventSequence) SELECT ObjectName, SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM CTE GROUP BY ObjectID, ObjectName -- Stop the trace EXEC sp_trace_setstatus @TraceID, 0 -- Close and delete the trace EXEC sp_trace_setstatus @TraceID, 2 GO 

Supondo que você esteja usando o Microsoft SQL Server Management Studio

  • Para o Plano de consulta estimado, você pode pressionar Ctrl + L ou o botão a seguir.

insira a descrição da imagem aqui

  • Para o plano de consulta real , você pode pressionar Ctrl + M ou o botão a seguir antes de executar a consulta.

insira a descrição da imagem aqui

  • Para Live Query Plan , (somente no SSMS 2016) use o seguinte botão antes de executar a consulta.

insira a descrição da imagem aqui

Além dos methods descritos nas respostas anteriores, você também pode usar um visualizador de plano de execução livre e uma ferramenta de otimização de consultas ApexSQL Plan (na qual eu recentemente esbarrei).

Você pode instalar e integrar o ApexSQL Plan no SQL Server Management Studio, para que os planos de execução possam ser visualizados diretamente do SSMS.

Visualizando Planos de Execução Estimados no Plano ApexSQL

  1. Clique no botão Nova consulta no SSMS e cole o texto da consulta na janela de texto da consulta. Clique com o botão direito e selecione a opção “Exibir plano de execução estimada” no menu de contexto.

Novo botão de consulta no SSMS

  1. Os diagramas do plano de execução serão mostrados na guia Plano de execução na seção de resultados. Em seguida, clique com o botão direito do mouse no plano de execução e, no menu de contexto, selecione a opção “Abrir no ApexSQL Plan”.

Plano de execução

  1. O plano de execução Estimativa será aberto no ApexSQL Plan e poderá ser analisado para otimização de consultas.

Plano de execução estimado

Visualizando Planos de Execução Reais no Plano ApexSQL

Para visualizar o plano de execução real de uma consulta, continue a partir da segunda etapa mencionada anteriormente, mas agora, quando o plano Estimativa for exibido, clique no botão “Real” na barra de opções principal no ApexSQL Plan.

clique no botão

Quando o botão “Real” for clicado, o plano de execução real será mostrado com uma visualização detalhada dos parâmetros de custo, juntamente com outros dados do plano de execução.

Plano de execução real

Mais informações sobre a visualização de planos de execução podem ser encontradas seguindo este link .

Minha ferramenta favorita para obter e analisar profundamente os planos de execução de consultas é o SQL Sentry Plan Explorer . É muito mais fácil de usar, conveniente e abrangente para a análise detalhada e visualização de planos de execução do que o SSMS.

Aqui está um exemplo de captura de canvas para você ter uma ideia de qual funcionalidade é oferecida pela ferramenta:

Captura de tela da janela do SQL Sentry Plan Explorer

É apenas uma das visualizações disponíveis na ferramenta. Observe um conjunto de guias na parte inferior da janela do aplicativo, que permite obter diferentes tipos de representação do plano de execução e informações adicionais úteis.

Além disso, eu não notei nenhuma limitação de sua edição gratuita que evite usá-la diariamente ou o force a comprar a versão Pro eventualmente. Então, se você preferir ficar com a edição gratuita, nada o proíbe de fazer isso.

ATUALIZAÇÃO: (Graças a Martin Smith ) O Plan Explorer agora é grátis! Veja http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view para detalhes.

Os planos de consulta podem ser obtidos a partir de uma session do Extended Events por meio do evento query_post_execution_showplan . Aqui está uma session XEvent de amostra:

 /* Generated via "Query Detail Tracking" template. */ CREATE EVENT SESSION [GetExecutionPlan] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan( ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)), /* Remove any of the following events (or include additional events) as desired. */ ADD EVENT sqlserver.error_reported( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.rpc_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sql_batch_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sql_statement_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO 

Depois de criar a session, (no SSMS) vá para o Pesquisador de Objetos e mergulhe no Management | Eventos estendidos | Sessões Clique com o botão direito do mouse na session “GetExecutionPlan” e inicie-a. Clique com o botão direito novamente e selecione “Watch Live Data”.

Em seguida, abra uma nova janela de consulta e execute uma ou mais consultas. Aqui está uma para o AdventureWorks:

 USE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO 

Depois de um momento ou dois, você verá alguns resultados na guia “GetExecutionPlan: Live Data”. Clique em um dos events query_post_execution_showplan na grade e, em seguida, na guia “Plano de consulta” abaixo da grade. Deve ser semelhante a isto:

insira a descrição da imagem aqui

EDIT : O código XEvent e a captura de canvas foram gerados a partir do SQL / SSMS 2012 w / SP2. Se você estiver usando o SQL 2008 / R2, poderá ajustar o script para executá-lo. Mas essa versão não tem uma GUI, então você teria que extrair o XML do showplan, salvá-lo como um arquivo * .sqlplan e abri-lo no SSMS. Isso é complicado. XEvents não existiam no SQL 2005 ou anterior. Então, se você não está no SQL 2012 ou posterior, sugiro fortemente uma das outras respostas postadas aqui.

A partir do SQL Server 2016+, o recurso Query Store foi introduzido para monitorar o desempenho. Ele fornece informações sobre a escolha e o desempenho do plano de consulta. Não é uma substituição completa de events estendidos ou de rastreamento, mas conforme está evoluindo de versão para versão, podemos obter um armazenamento de consultas totalmente funcional em versões futuras do SQL Server. O stream principal do Query Store

  1. Os componentes existentes do SQL Server interagem com o repository de consultas, utilizando o Query Store Manager.
  2. Gerenciador de armazenamento de consultas determina qual armazenamento deve ser usado e, em seguida, passa a execução para esse armazenamento (statistics de plano ou tempo de execução ou statistics de espera de consulta)
    • Loja de planos – Persistindo as informações do plano de execução
    • Armazenamento de Estatísticas de Tempo de Execução – Persistindo as informações de statistics de execução
    • Query Wait Stats Store – Persistir informações statistics de espera.
  3. As statistics Plan, Runtime Stats e Wait usam o Query Store como uma extensão do SQL Server.

insira a descrição da imagem aqui

  1. Ativando o Query Store : o Query Store funciona no nível do database no servidor.

    • O Query Store não está ativo para novos bancos de dados por padrão.
    • Você não pode ativar o armazenamento de consulta para o database mestre ou tempdb .
    • DMV disponível

      sys.database_query_store_options (Transact-SQL)

  2. Coletar informações no Query Store : coletamos todas as informações disponíveis das três lojas usando o DMV (Data Management Views) da loja de consultas.

    • Armazenamento do Plano de Consulta: Persistindo as informações do plano de execução e é responsável por capturar todas as informações relacionadas à compilation de consultas.

      sys.query_store_query (Transact-SQL) sys.query_store_plan (Transact-SQL) sys.query_store_query_text (Transact-SQL)

    • Armazenamento de Estatísticas de Tempo de Execução: Persistindo as informações de statistics de execução e provavelmente é o armazenamento atualizado com mais frequência. Essas statistics representam dados de execução de consulta.

      sys.query_store_runtime_stats (Transact-SQL)

    • Query Wait Stats Store: Persistindo e capturando informações de statistics de espera.

      sys.query_store_wait_stats (Transact-SQL)

NOTA: A Loja de Estatísticas de Espera de Consulta está disponível apenas no SQL Server 2017+

Aqui está uma coisa importante a saber, além de tudo o que foi dito antes.

Os planos de consulta geralmente são muito complexos para serem representados pelo tipo de coluna XML interno que possui uma limitação de 127 níveis de elementos nesteds. Essa é uma das razões pelas quais o sys.dm_exec_query_plan pode retornar NULL ou até mesmo lançar um erro em versões anteriores do MS SQL, então geralmente é mais seguro usar o sys.dm_exec_text_query_plan . O último também tem um recurso de bônus útil de selecionar um plano para uma instrução específica em vez de todo o lote. Veja como você o utiliza para visualizar planos para instruções em execução no momento:

 SELECT p.query_plan FROM sys.dm_exec_requests AS r OUTER APPLY sys.dm_exec_text_query_plan( r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS p 

No entanto, a coluna de texto na tabela resultante não é muito útil em comparação com uma coluna XML. Para poder clicar no resultado a ser aberto em uma aba separada como um diagrama, sem ter que salvar seu conteúdo em um arquivo, você pode usar um pequeno truque (lembre-se que você não pode simplesmente usar CAST(... AS XML) ) , embora isso só funcione para uma única linha:

 SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan FROM sys.dm_exec_text_query_plan( -- set these variables or copy values -- from the results of the above query @plan_handle, @statement_start_offset, @statement_end_offset) FOR XML EXPLICIT 

Como no SQL Server Management Studio (já explicado), também é possível com o Datagrip, conforme explicado aqui .

  1. Clique com o botão direito do mouse em uma instrução SQL e selecione Explain plan.
  2. No painel Saída, clique em Planejar.
  3. Por padrão, você vê a representação em tree da consulta. Para ver o plano de consulta, clique no ícone Exibir Visualização ou pressione Ctrl + Shift + Alt + U