Infraestrutura de criação de perfil de consulta

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

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

Embora a conclusão da consulta seja um pré-requisito para a disponibilidade de um plano de consulta real, as estatísticas de consulta em tempo real podem fornecer informações imediatas sobre o processo de execução da consulta, à medida que os dados fluem 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 execução em tempo de execução no nível do operador, como o número de linhas produzidas, o tempo decorrido, o 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 longa execução e consultas que são executadas indefinidamente e nunca terminam.

A infraestrutura padrão de perfilar estatísticas de execução de consultas

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

• DEFINIR ESTATÍSTICAS XML
• DEFINIR PERFIL DE ESTATÍSTICAS
• Estatísticas das Consultas ao Vivo

Os seguintes métodos de coleta de informações globalmente do plano de execução para todas as sessões aproveitam a infraestrutura padrão de perfilagem:

• O query_post_execution_showplan evento estendido. Para habilitar eventos estendidos, consulte Monitorizar a atividade do sistema usando eventos estendidos.
• O evento de rastreamento Showplan XML no SQL Trace e no SQL Server Profiler. Para obter mais informações sobre esse evento de rastreamento, consulte Showplan XML Event Class.

Ao executar uma sessão de evento estendida que utiliza o evento query_post_execution_showplan, a DMV sys.dm_exec_query_profiles também é preenchida. Isso possibilita estatísticas de consultas ao vivo para todas as sessões, seja usando o Monitor de Atividade ou consultando diretamente a DMV. Para obter mais informações, consulte Live Query Statistics.

A infraestrutura leve de perfis de estatísticas de execução de consultas

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

Infraestrutura de perfilagem de estatísticas leves de execução de consultas v1

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

A partir do SQL Server 2014 (12.x) SP2 e do SQL Server 2016 (13.x), a sobrecarga de desempenho para recolher informações sobre planos de execução foi reduzida com a introdução da perfilagem leve. Ao contrário da criação de perfil padrão, a criação de perfil leve não coleta informações de tempo de execução da CPU. No entanto, a perfilagem ligeira ainda recolhe informações sobre a contagem de linhas e o uso de Entrada/Saída.

Foi também introduzido um novo evento estendido query_thread_profile que aproveita a perfilagem leve. 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 esse 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);

Ao executar uma sessão de evento estendida que usa o evento query_thread_profile, o sys.dm_exec_query_profiles DMV também é preenchido usando profiling leve, o que permite estatísticas de consulta ao vivo para todas as sessões, utilizando o Monitor de Atividade ou consultando diretamente o DMV.

Infraestrutura de análise leve de estatísticas de execução de consulta v2

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

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

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

Uma sessão de exemplo usando o evento estendido query_plan_profile pode ser configurada como o exemplo abaixo:

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 leve de criação de perfis de estatísticas de execução de consultas v3

Aplica-se a: SQL Server (a partir do 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 recém-revisada do perfil leve coletando informações de contagem de linhas 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. A partir do SQL Server 2019 (15.x), o sinalizador de rastreamento 7412 não tem efeito. A criação de perfil leve pode ser desabilitada no nível do banco de dados usando o LIGHTWEIGHT_QUERY_PROFILING de configuração com escopo do banco de dados: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Um novo DMF sys.dm_exec_query_plan_stats é introduzido para retornar o equivalente ao último plano de execução real conhecido para a maioria das interrogações e é chamado estatísticas do último plano de consulta. As estatísticas do último plano de consulta podem ser ativadas ao nível da base de dados usando a configuração do escopo da base 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 recolhe o equivalente a um plano de execução real baseado em perfilagem leve, ao contrário de query_post_execution_showplan que usa a perfilagem padrão. O SQL Server 2017 (14.x) também oferece esse evento começando com CU14. Uma sessão de exemplo usando o evento query_post_execution_plan_profile estendido pode ser configurada como o exemplo abaixo:

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 Eventos Estendidos usando a perfilagem 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 Eventos Alargada usando perfilagem ligeira

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 perfilamento de consultas

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

Comentários

Começando com a criação de perfil leve v2 e sua baixa sobrecarga, qualquer servidor que ainda não esteja vinculado à CPU pode executar de criação de perfil leves continuamentee permitir que os profissionais de banco de dados aproveitem qualquer execução em execução a qualquer momento, por exemplo, usando o Monitor de Atividade ou consultando diretamente sys.dm_exec_query_profiles, e obter o plano de consulta com estatísticas de tempo de execução.

Para obter mais informações sobre o impacto no desempenho da perfilagem de consultas, consulte a postagem do blog Developers Choice: Progresso da consulta - a qualquer hora, em qualquer lugar.

Ver também

Monitore e otimize para desempenho
Ferramentas de monitoramento e ajuste de desempenho
Abrir o Monitor de Atividades (SQL Server Management Studio)
Monitor de Atividade
Monitorando o desempenho usando o repositório de consultas
Monitorizar a Atividade do Sistema Usando Eventos Estendidos
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Sinalizadores de rastreamento
Referência dos Operadores Lógicos e Físicos do Showplan
plano de execução real
Estatísticas de Consulta ao Vivo