Infraestrutura de criação de perfil de consulta

Aplica-se a:SQL ServerBase de Dados SQL do AzureAzure 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 criação de perfis de estatísticas de execução de consultas, ou criação de perfis padrão, tem de estar ativada para recolher informações sobre os planos de execução, nomeadamente o número de linhas, CPU e utilização de I/O. 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:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Estatísticas das Consultas ao Vivo

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:

• O evento query_post_execution_showplan expandido. Para habilitar eventos estendidos, consulte Monitorar 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 eventos alargados que utiliza o evento query_post_execution_showplan, a DMV sys.dm_exec_query_profiles também é preenchida, o que permite obter estatísticas de consultas em tempo real para todas as sessões, utilizando o Activity Monitor ou consultando diretamente a DMV. 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.

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

Aplica-se a: SQL Server 2014 (12.x) SP2 até 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.

Foi também introduzido um novo evento expandido query_thread_profile que utiliza criação de perfis 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 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
);

Ao executar uma sessão de eventos estendidos que utiliza o evento query_thread_profile, a DMV sys.dm_exec_query_profiles também é preenchida através de criação de perfis leve, o que permite estatísticas de consulta em tempo real para todas as sessões, através do Monitor de Atividade ou consultando diretamente a DMV.

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

Aplica-se a: SQL Server 2016 (13.x) SP1 até 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 perfis simplificada não estiver ativada globalmente, o novo argumento USE HINT query hintQUERY_PLAN_PROFILE pode ser utilizado para ativar a criação de perfis simplificada ao 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.

Uma sessão de exemplo que utiliza o evento estendido query_plan_profile pode ser configurada como no exemplo seguinte:

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 ativadas ao nível da base de dados utilizando a LAST_QUERY_PLAN_STATSconfiguração com âmbito da base de dados: 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 efetivo com base em criação de perfis simples, ao contrário de query_post_execution_showplan, que utiliza criação de perfis padrão. O SQL Server 2017 (14.x) também oferece esse evento começando com CU14. Uma sessão de exemplo que utiliza o evento estendido query_post_execution_plan_profile pode ser configurada como no exemplo seguinte:

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.

Remarks

Começando com o profiling leve v2 e a sua baixa sobrecarga, qualquer servidor que ainda não esteja limitado pela CPU pode executar o profiling leve continuamente e permitir que os profissionais de bases de dados acedam a qualquer execução em curso, a qualquer momento, por exemplo, utilizando o Monitor de Atividade ou consultando diretamente sys.dm_exec_query_profiles, e obtenham o plano de consulta com estatísticas de execuçã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 da definição de perfis padrão.

Conteúdo relacionado

• Monitore e otimize para desempenho
• Ferramentas de monitoramento e ajuste de desempenho
• Abrir o Monitor de Atividade no SQL Server Management Studio (SSMS)
• Monitor de Atividade
• Monitorar 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
• Definir sinalizadores de rastreamento com DBCC TRACEON (Transact-SQL)
• Referência aos operadores lógicos e físicos do showplan
• plano de execução real
• Estatísticas das Consultas ao Vivo