Compartilhar via

Infraestrutura de Criação de Perfil de Consulta

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 de perfil de estatísticas de execução de consulta padrão

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 de 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 Dinâmica 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 dinâmica em vez de criação de perfil padrão quando exibida 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 Atividades 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.

Note

Não há suporte para procedimentos armazenados compilados nativamente com 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 2014 (12.x) SP2 por meio do 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 da 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 query_thread_profile evento estendido também foi introduzido que usa criação de 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
);

Note

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 evento estendida que usa o query_thread_profile evento, o sys.dm_exec_query_profiles DMV também é preenchido usando a criação de perfil leve, o que permite estatísticas de consulta ao vivo para todas as sessões, usando o Monitor de Atividades ou consultando diretamente a DMV.

Infraestrutura de criação de perfil de estatísticas de execução de consulta leve 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. 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.

A partir do SQL Server 2016 (13.x) SP2 CU3 e 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 é uma saída 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 a criação de perfil leve mesmo se a dica de consulta não for usada.

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 de criação de perfil de estatísticas de execução de consulta leve 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 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 efeito. A criação de perfil leve pode ser desabilitada no nível do banco de dados usando a LIGHTWEIGHT_QUERY_PROFILING 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_STATS 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 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.

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 de 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; A partir do SQL Server 2017 (14.x) CU14 e SQL Server 2019 (15.x)
Session Utilize SET STATISTICS XML ON Use a QUERY_PLAN_PROFILE dica de consulta junto com uma sessão de Evento Estendido com o query_plan_profile XE; A partir do 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ó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 sua baixa sobrecarga, qualquer servidor que ainda não esteja associado à CPU pode executar a criação de perfil leve continuamente e permitir que os profissionais de banco de dados toquem em qualquer execução em execução a qualquer momento, por exemplo, usando o Monitor de Atividade ou consulta sys.dm_exec_query_profilesdireta 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).

Os Eventos Estendidos que usam a 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 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 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 com base é query_post_execution_plan_profile iniciado e, em seguida, é desativado novamente quando o rastreamento é interrompido. Como consequência, se os rastreamentos de Eventos Estendidos com base em query_post_execution_plan_profile forem frequentemente iniciados e interrompidos em uma instância do SQL Server 2017 (14.x), você deverá ativar a Criação de Perfil Leve em nível global com o sinalizador de rastreamento 7412 para evitar a sobrecarga de ativação e desativação repetida.

Conteúdo relacionado

  • Last updated on