Infraestrutura de Análise de Perfil de Consultas

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

O Mecanismo de Banco de Dados do SQL Server fornece acesso a informações de runtime sobre 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. Portanto, 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 padrão para criação de perfil de estatísticas de execução de consultas

A infraestrutura de criação de perfil das 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, a saber, contagem de linhas, CPU e uso de E/S. Os seguintes métodos de coleta de informações de 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 de consulta dinâmica

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 query_post_execution_showplan evento 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 o evento de rastreamento, confira Classe de evento Showplan XML.

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 Atividades ou consultando diretamente o DMV. Para obter mais informações, consulte Live Query Statistics.

A infraestrutura leve para geração 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 para criação de perfil de estatísticas de execução de consultas, ou uma criação de perfil leve.

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

Aplica-se a: SQL Server 2014 (12.x) SP2 por meio do 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 coletar informações sobre planos de execução foi reduzida com a introdução do perfil leve. Ao contrário do perfilamento padrão, o perfilamento leve não coleta informações sobre o tempo de execução da CPU. No entanto, a criação de perfil leve ainda coleta informações sobre contagem de linhas e uso de E/S.

Um novo evento estendido query_thread_profile também foi introduzido, que utiliza perfil 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 usa o evento query_thread_profile, a DMV sys.dm_exec_query_profiles também é populada usando profiling leve, o que habilita estatísticas de consulta em tempo real para todas as sessões, por meio do Monitor de Atividades ou consultando a DMV diretamente.

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 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 declaradas anteriormente em Applies to. Uma nova DMF sys.dm_exec_query_statistics_xml foi introduzida para retornar o plano de execução da consulta para solicitações em execução.

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 QUERY_PLAN_PROFILE da dica de consulta USE HINT query hint pode ser usado para habilitar a criação de perfil leve no nível da consulta, em qualquer sessão. Quando uma consulta que contém essa nova dica termina, um novo evento estendido query_plan_profile também é gerado, fornecendo um plano de execução real em XML semelhante ao do evento estendido query_post_execution_showplan.

Uma sessão de exemplo usando o query_plan_profile evento estendido pode ser configurada 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 perfilamento 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 recentemente revisada da criação de perfil leve, que coleta informações de contagem de linhas em 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 LIGHTWEIGHT_QUERY_PROFILINGconfiguração no 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 do último plano de execução real conhecido para a maioria das consultas e é chamado de últimas estatísticas do 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_STATSconfiguração no escopo do 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 na criação de perfil leve, diferentemente query_post_execution_showplando 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 query_post_execution_plan_profile evento estendido 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 análise de perfil de consultas

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.

Remarks

Começando com o profiling leve v2 e 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 banco de dados acessem qualquer execução em andamento a qualquer momento, por exemplo, usando o Monitor de Atividade ou consultando diretamente sys.dm_exec_query_profiles, e obtenham o plano de consulta com estatísticas de tempo de execuçã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).

Os Eventos Estendidos que usam perfilamento leve obtêm informações do perfilamento padrão, caso a infraestrutura de perfilamento padrão já esteja 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 usa informações da definição de perfil padrão.

Conteúdo relacionado

• Monitorar e ajustar o desempenho
• Ferramentas de monitoramento e ajuste de desempenho
• Abrir o Monitor de Atividades no SSMS (SQL Server Management Studio)
• Monitor de Atividades
• Monitorar o desempenho usando o Repositório de Consultas
• Monitorar 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 de operadores lógicos e físicos do Showplan
• plano de execução real
• Estatísticas de consulta dinâmica