Infraestrutura de Criação de Perfil de Consulta

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do Azure

O Mecanismo de Banco de Dados do SQL Server oferece a capacidade de acessar informações de runtime nos planos de execução de consulta. Uma das ações mais importantes quando ocorre um problema de desempenho é obter a compreensão precisa sobre a carga de trabalho que está em execução e como o uso de recursos está sendo controlado. Para isso, o acesso ao plano de execução real é importante.

Enquanto a conclusão da consulta é um pré-requisito para a disponibilidade de um plano de consulta real, as estatísticas de consulta dinâmica podem fornecer informações em tempo real sobre o processo de execução de consulta como os fluxos de dados de um operador de plano de consulta para outro. O plano de consulta ao vivo exibe o progresso geral da consulta e as estatísticas de tempo de execução do nível de operador, como número de linhas produzidas, tempo decorrido, progresso do operador, etc. Como esses dados estão disponíveis em tempo real sem a necessidade de aguardar a conclusão da consulta, essas estatísticas de execução são extremamente úteis para depurar problemas de desempenho de consulta, como consultas de execução prolongada e as que são executadas indefinidamente e nunca terminam.

A infraestrutura de perfil de estatísticas de execução de consulta padrão

A infraestrutura do perfil de estatísticas de execução de consulta ou a criação de perfil padrão, deve ser habilitada para coletar informações sobre planos de execução, ou seja, a contagem de linhas, uso de CPU e E/S. Os métodos de coleta de informações de plano de execução a seguir para uma sessão de destino aproveitam a infraestrutura de criação de perfil padrão:

Observação

Clicando no botão Incluir Estatísticas de Consulta Dinâmica no SQL Server Management Studio, aproveita-se a infraestrutura de criação de perfil padrão.
Em versões posteriores do SQL Server, se a infraestrutura de criação de perfil leve estiver habilitada, ela será usada pelas estatísticas de consulta dinâmica, em vez da criação de perfil padrão, quando exibida por meio do Monitor de Atividade ou consultando diretamente o DMV exec_query_profiles.

Os métodos de coleta de informações de plano de execução a seguir para todas as sessão aproveitam a infraestrutura de criação de perfil padrão:

Ao executar uma sessão de eventos estendidos que usa o evento query_post_execution_showplan, o DMV sys.dm_exec_query_profiles também é populado, o que permite estatísticas de consulta dinâmica para todas as sessões, usando o Monitor de Atividade ou consultando diretamente o DMV. Para obter mais informações, consulte Live Query Statistics.

A infraestrutura de criação de perfil de estatísticas de execução de consulta leve

No SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x) ou posteriores, foi introduzida uma nova infraestrutura de criação de perfil de estatísticas de execução de consulta leve ou criação de perfil leve.

Observação

Os procedimentos armazenados compilados nativamente não são compatíveis com a criação de perfil leve.

Infraestrutura de criação de perfil de estatísticas de execução de consulta leve v1

Aplica-se a: SQL Server (SQL Server 2014 (12.x) SP2 até o SQL Server 2016 (13.x)).

No SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x) ou posteriores, a sobrecarga de desempenho para coletar informações sobre planos de execução foi reduzida com a introdução da criação de perfil leve. Ao contrário da criação de perfil padrão, a criação de perfil leve não coleta informações de runtime de CPU. No entanto, a criação de perfil leve ainda coleta informações de uso de E/S e de contagem de linhas.

Um novo evento query_thread_profile estendido que utiliza a criação de perfil leve também foi introduzido. Esse evento estendido expõe estatísticas de execução por operador, permitindo mais informações sobre o desempenho de cada nó e thread. Uma sessão de exemplo usando este evento estendido pode ser configurada como no exemplo abaixo:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
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=OFF,
  STARTUP_STATE=OFF);

Observação

Para obter mais informações sobre a sobrecarga de desempenho da criação de perfil de consulta, confira a postagem no blog Developers Choice: Query progress – anytime, anywhere (Escolha dos desenvolvedores: consultar o andamento – a qualquer momento, em qualquer lugar).

Ao executar uma sessão de eventos estendidos que usa o evento query_thread_profile, o DMV sys.dm_exec_query_profiles também é populado usando a criação de perfil leve, o que permite estatísticas de consulta dinâmica para todas as sessões, usando o Monitor de Atividade ou consultando diretamente o DMV.

Infraestrutura de criação de perfil de estatísticas de execução de consulta leve v2

Aplica-se a: SQL Server (SQL Server 2016 (13.x) SP1 até o SQL Server 2017 (14.x)).

O SQL Server 2016 (13.x) SP1 inclui uma versão revisada da criação de perfil leve com sobrecarga mínima. A criação de perfil leve também pode ser habilitada globalmente usando o sinalizador de rastreamento 7412 para as versões mencionadas acima em Aplica-se a. Um novo DMF DM exec_query_statistics_xml é introduzido para retornar o plano de execução de consulta para as solicitações em trânsito.

No SQL Server 2016 (13.x) SP2 CU3 e SQL Server 2017 (14.x) CU11 e posteriores, se a criação de perfil leve não estiver habilitada globalmente, o novo argumento de dica de consulta USE HINTQUERY_PLAN_PROFILE poderá ser usado para habilitar a criação de perfil leve no nível da consulta, para qualquer sessão. Quando uma consulta que contém essas nova dica termina, um novo evento estendido query_plan_profile também é a saída que fornece um plano de execução real XML semelhante ao evento estendido query_post_execution_showplan.

Observação

O evento estendido query_plan_profile também se beneficia da criação de perfil leve, mesmo se a dica de consulta não é usada.

Uma sessão de exemplo usando o evento estendido query_plan_profile pode ser configurado como o exemplo a seguir:

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
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=OFF,
  STARTUP_STATE=OFF);

Infraestrutura de criação de perfil de estatísticas de execução de consulta leve v3

Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

O SQL Server 2019 (15.x) e o Banco de Dados SQL do Azure incluem uma versão revisada recentemente da criação de perfil leve coletando informações de contagem de linha para todas as execuções. A criação de perfil leve é habilitada por padrão no SQL Server 2019 (15.x) e no Banco de Dados SQL do Azure. No SQL Server 2019 (15.x) e versões posteriores, o sinalizador de rastreamento 7412 não tem nenhum efeito. A criação de perfil leve pode ser desabilitada no nível de banco de dados usando a configuração de escopo do banco de dados LIGHTWEIGHT_QUERY_PROFILING: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Um novo DMF sys.dm_exec_query_plan_stats é introduzido para retornar o equivalente do último plano de execução real conhecido para a maioria das consultas e é chamado de últimas estatísticas do plano de consulta. As últimas estatísticas do plano de consulta podem ser habilitadas no nível de banco de dados usando a configuração de escopo do banco de dados LAST_QUERY_PLAN_STATS: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Um novo evento estendido query_post_execution_plan_profile coleta o equivalente a um plano de execução real com base em criação de perfil leve, ao contrário de query_post_execution_showplan, que usa a criação de perfil padrão. O SQL Server 2017 (14.x) também oferece esse evento no CU14 e posteriores. Uma sessão de exemplo usando o evento estendido query_post_execution_plan_profile pode ser configurada como o exemplo a seguir:

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
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=OFF,
  STARTUP_STATE=OFF);

Exemplo 1 – sessão de evento estendido usando a criação de perfil padrão

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
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=OFF, STARTUP_STATE=OFF);

Exemplo 2 – sessão de evento estendido usando a criação de perfil leve

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
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=OFF, STARTUP_STATE=OFF);

Diretrizes de uso da infraestrutura de criação de perfil de consulta

A tabela a seguir resume as ações para habilitar a criação de perfil padrão ou de perfil leve, tanto globalmente (no nível do servidor) como em uma única sessão. Também inclui a versão mais antiga para a qual a ação está disponível.

Escopo Criação de perfil padrão Criação de perfil leve
Global Sessão xEvent com o XE query_post_execution_showplan; no SQL Server 2012 (11.x) e versões posteriores Sinalizador de rastreamento 7412; no SQL Server 2016 (13.x) SP1 e versões posteriores
Global Rastreamento do SQL e SQL Server Profiler com o evento de rastreamento Showplan XML; a partir do SQL Server 2000 Sessão xEvent com o XE query_thread_profile; no SQL Server 2014 (12.x) SP2 e versões posteriores
Global - Sessão xEvent com o XE query_post_execution_plan_profile; no SQL Server 2017 (14.x) CU14 e o SQL Server 2019 (15.x) e versões posteriores
Session Use SET STATISTICS XML ON; a partir do SQL Server 2000 Use a dica de consulta QUERY_PLAN_PROFILE junto com uma sessão xEvent com o XE query_plan_profile; no SQL Server 2016 (13.x) SP2 CU3 e o SQL Server 2017 (14.x) CU11 e versões posteriores
Session Use SET STATISTICS PROFILE ON; a partir do SQL Server 2000 -
Session Clique no botão Estatísticas de Consulta Dinâmica no SSMS; no SQL Server 2014 (12.x) SP2 e versões posteriores -

Comentários

Importante

Devido a uma possível violação de acesso aleatória durante a execução de um procedimento armazenado de monitoramento que faz referência a sys.dm_exec_query_statistics_xml, verifique se o KB 4078596 está instalado no SQL Server 2016 (13.x) e no SQL Server 2017 (14.x).

Começando com a criação de perfil leve v2 e a baixa sobrecarga, qualquer servidor que não esteja vinculado à CPU poderá executar a criação de perfil leve continuamente e permitir que os profissionais de banco de dados explorem qualquer execução a qualquer momento, por exemplo, usando o Monitor de Atividade ou consultando sys.dm_exec_query_profiles diretamente e obtenham o plano de consulta com estatísticas de runtime.

Para obter mais informações sobre a sobrecarga de desempenho da criação de perfil de consulta, confira a postagem no blog Developers Choice: Query progress – anytime, anywhere (Escolha dos desenvolvedores: consultar o andamento – a qualquer momento, em qualquer lugar).

Observação

Eventos estendidos que aproveitam a criação de perfil leve usarão informações da criação de perfil padrão se a infraestrutura de criação de perfil padrão já estiver habilitada. Por exemplo, uma sessão de evento estendido usando query_post_execution_showplan está em execução e outra sessão usando query_post_execution_plan_profile é iniciada. A segunda sessão ainda usará informações de criação de perfil padrão.

Observação

No SQL Server 2017 (14.x), a Criação de Perfil Leve fica desativada por padrão, mas é ativada quando um rastreamento de XEvent que depende de query_post_execution_plan_profile é iniciado e é desativado novamente quando o rastreamento é interrompido. Como consequência, se os rastreamentos de XEvent com base em query_post_execution_plan_profile forem frequentemente iniciados e interrompidos em uma instância do SQL Server 2017 (14.x), é altamente recomendável ativar a Criação de Perfil Leve em nível global com o sinalizador de rastreamento 7412 para evitar a sobrecarga de ativação/desativação repetida.

Confira também

Monitorar e ajustar o desempenho
Ferramentas para monitoramento e ajuste de desempenho
Abrir o Monitor de Atividade (SQL Server Management Studio)
Monitor de Atividade
Monitorando o desempenho com o repositório de consultas
Monitorar a atividade do sistema usando Eventos Estendidos
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Sinalizadores de rastreamento
Referência de operadores físicos e lógicos de plano de execução
plano de execução real
Estatísticas de consulta dinâmica