Partilhar via

Infraestrutura de criação de perfil de consulta

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

O Mecanismo de Banco de Dados do SQL Server fornece acesso a 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. Assim, o acesso ao plano de execução real é 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 criação de perfil padrão, deve ser habilitada para coletar informações sobre planos de execução, ou seja, contagem de linhas, CPU e uso de E/S. Os seguintes métodos de coleta de informações do plano de execução para uma sessão de destino usam a infraestrutura de criação de perfil padrão:

Note

Selecionar o botão Incluir estatísticas de consulta ao vivo no SQL Server Management Studio usa 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 por estatísticas de consulta em tempo real em vez da criação de perfil padrão quando visualizada por meio do Monitor de Atividade ou consultando diretamente o sys.dm_exec_query_profiles DMV.

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

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

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

A partir do SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x), foi introduzida uma nova infraestrutura leve para criação de perfis de estatísticas da execução de consultas, também conhecida como criação de perfil leve.

Note

Procedimentos armazenados compilados nativamente não são suportados com perfis leves.

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

Aplica-se a: 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 leve ainda recolhe informações sobre a contagem de linhas e o uso de I/O.

Um novo query_thread_profile evento estendido também foi introduzido que usa perfis leves. 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 a seguir:

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
);

Note

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

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

Infraestrutura leve de perfilamento de estatísticas de execução de consultas v2

Aplica-se a: 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 o sinalizador de rastreamento 7412 para as versões indicadas anteriormente 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, o novo argumento de QUERY_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 essa nova dica é concluída, um novo query_plan_profile evento estendido também é gerado que fornece um XML de plano de execução real semelhante ao query_post_execution_showplan evento estendido.

Note

O query_plan_profile evento estendido também usa criação de perfil leve, mesmo que a dica de consulta não seja usada.

Uma sessão de exemplo usando o evento estendido pode ser configurada query_plan_profile 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 leve de criação de perfis de estatísticas de execução de consultas v3

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores 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. No SQL Server 2019 (15.x) e versões posteriores, 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 a configuração de escopo do LIGHTWEIGHT_QUERY_PROFILINGbanco 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 habilitadas no nível do banco de dados usando a LAST_QUERY_PLAN_STATS banco de dados: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Um novo query_post_execution_plan_profile evento estendido coleta o equivalente a um plano de execução real com base em criação de perfil leve, ao contrário query_post_execution_showplando , que usa perfil 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 estendido pode ser configurada query_post_execution_plan_profile 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 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 Extendida usando perfilação 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.

Scope Criação de perfil padrão Criação de perfil leve
Global Sessão de Evento Estendido com o query_post_execution_showplan XE; Começando com o SQL Server 2012 (11.x) Sinalizador de rastreamento 7412; Começando com o SQL Server 2016 (13.x) SP1
Global Rastreamento SQL e SQL Server Profiler com o Showplan XML evento de rastreamento Sessão de Evento Estendido com o query_thread_profile XE; Começando com o SQL Server 2014 (12.x) SP2
Global N/A Sessão de Evento Estendido com o query_post_execution_plan_profile XE; Começando com SQL Server 2017 (14.x) CU14 e SQL Server 2019 (15.x)
Session Utilize SET STATISTICS XML ON Use a dica QUERY_PLAN_PROFILE de consulta junto com uma sessão de Evento Estendido com o query_plan_profile XE; Começando com SQL Server 2016 (13.x) SP2 CU3 e SQL Server 2017 (14.x) CU11
Session Utilize SET STATISTICS PROFILE ON N/A
Session Selecione o botão Estatísticas de consulta dinâmica no SSMS; Começando com o SQL Server 2014 (12.x) SP2 N/A

Remarks

Important

Devido a uma possível violação de acesso aleatório durante a execução de um procedimento armazenado de monitoramento que faz referência a sys.dm_exec_query_statistics_xml, verifique se 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 sua baixa sobrecarga, qualquer servidor que ainda não esteja vinculado à CPU pode executar a criação de perfil leve continuamente e 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 , e obter o plano de consulta com estatísticas de tempo de sys.dm_exec_query_profilesexecução.

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

Os Eventos Estendidos que usam criação de perfil leve usam informações da criação de perfil padrão, caso a infraestrutura de criação de perfil padrão já esteja habilitada. Por exemplo, uma sessão de evento estendida 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 usa informações de criação de perfil padrão.

Note

No SQL Server 2017 (14.x), a Criação de Perfil Leve está desativada por padrão, mas é ativada quando um rastreamento de Evento Estendido é query_post_execution_plan_profile iniciado e, em seguida, é desabilitada novamente quando o rastreamento é interrompido. Como consequência, se os rastreamentos de Eventos Estendidos baseados em query_post_execution_plan_profile forem frequentemente iniciados e interrompidos numa instância do SQL Server 2017 (14.x), deve-se ativar a Perfilagem Leve a nível global com a flag de rastreio 7412 para evitar a sobrecarga repetida de ativação/desativação.

Conteúdo relacionado

  • Last updated on