Monitorando o desempenho de procedimentos armazenados compilados nativamenteMonitoring Performance of Natively Compiled Stored Procedures

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Este artigo discute como é possível monitorar o desempenho de procedimentos armazenados e de outros módulos T-SQL, ambos compilados nativamente.This article discusses how you can monitor the performance of natively compiled stored procedures and other natively compiled T-SQL modules.

Usando eventos estendidosUsing Extended Events

Use o evento estendido sp_statement_completed para rastrear a execução de uma consulta.Use the sp_statement_completed extended event to trace execution of a query. Crie uma sessão de evento estendido com esse evento, opcionalmente com um filtro no object_id para um procedimento armazenado específico compilado nativamente.Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure. O evento estendido é ativado depois da execução de cada consulta.The extended event is raised after the execution of each query. O tempo de CPU e a duração relatados pelo evento estendido indicam a quantidade de CPU usada pela consulta e o tempo de execução.The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. Um procedimento armazenado compilado de modo nativo que usa muito tempo da CPU pode ter problemas de desempenho.A natively compiled stored procedure that uses a lot of CPU time may have performance problems.

line_numberjunto com object_id no evento estendido pode ser usado para investigar a consulta.line_number, along with the object_id in the extended event can be used to investigate the query. A consulta a seguir pode ser usada para recuperar a definição de procedimento.The following query can be used to retrieve the procedure definition. O número da linha pode ser usado para identificar a consulta na definição:The line number can be used to identify the query within the definition:

SELECT [definition]
    from sys.sql_modules
    where object_id=object_id;

Usando exibições de gerenciamento de dados e Repositório de ConsultasUsing Data Management Views and Query Store

SQL ServerSQL Server e Banco de Dados SQLSQL Database dão suporte à coleta de estatísticas de execução para procedimentos armazenados compilados nativamente, nos níveis de procedimento e de consulta.and Banco de Dados SQLSQL Database support collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. Coletar estatísticas de execução não está habilitado por padrão devido ao impacto sobre o desempenho.Collecting execution statistics is not enabled by default due to performance impact.

As estatísticas de execução são refletidas nas exibições do sistema sys.dm_exec_procedure_stats e sys.dm_exec_query_stats, bem como no Repositório de Consultas.Execution statistics are reflected in the system views sys.dm_exec_procedure_stats and sys.dm_exec_query_stats, as well as in Query Store.

Estatísticas de execução em nível de procedimentoProcedure-Level Execution Statistics

SQL ServerSQL Server : habilite ou desabilite a coleta de estatísticas em procedimentos armazenados compilados nativamente no nível de procedimento usando sys.sp_xtp_control_proc_exec_stats (Transact-SQL).SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the procedure-level using sys.sp_xtp_control_proc_exec_stats (Transact-SQL). A instrução a seguir permite a coleta de estatísticas de execução de nível de procedimento para todos os módulos T-SQL compilados nativamente na instância atual:The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Banco de dados SQL do AzureAzure SQL Database : habilite ou desabilite a coleta de estatísticas em procedimentos armazenados compilados nativamente no nível de procedimento usando a opção configuração com a configuração de escopo do banco de dados XTP_PROCEDURE_EXECUTION_STATISTICS.Banco de dados SQL do AzureAzure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the database-scoped configuration option XTP_PROCEDURE_EXECUTION_STATISTICS. A instrução a seguir permite a coleta de estatísticas de execução de nível de procedimento para todos os módulos T-SQL compilados nativamente no banco de dados atual:The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE
    SCOPED CONFIGURATION
    SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Estatísticas de execução em nível de consultaQuery-Level Execution Statistics

SQL ServerSQL Server : habilite ou desabilite a coleta de estatísticas em procedimentos armazenados compilados nativamente no nível de consulta usando sys.sp_xtp_control_query_exec_stats (Transact-SQL).SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the query-level using sys.sp_xtp_control_query_exec_stats (Transact-SQL). A instrução a seguir permite a coleta de estatísticas de execução de nível de consulta para todos os módulos T-SQL compilados nativamente na instância atual:The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_query_exec_stats 1

Banco de dados SQL do AzureAzure SQL Database : habilite ou desabilite a coleta de estatísticas em procedimentos armazenados compilados nativamente no nível de instrução usando a opção configuração com a configuração de escopo do banco de dados XTP_QUERY_EXECUTION_STATISTICS.Banco de dados SQL do AzureAzure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the database-scoped configuration option XTP_QUERY_EXECUTION_STATISTICS. A instrução a seguir permite a coleta de estatísticas de execução de nível de consulta para todos os módulos T-SQL compilados nativamente no banco de dados atual:The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE
    SCOPED CONFIGURATION
    SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Consultas de exemploSample Queries

Depois que você coletar estatísticas, as estatísticas de execução de procedimentos armazenados compilados de modo nativo poderão ser consultadas para um procedimento com sys.dm_exec_procedure_stats (Transact-SQL) e para consultas com sys.dm_exec_query_stats (Transact-SQL).After you collect statistics, the execution statistics for natively compiled stored procedures can be queried for a procedure with sys.dm_exec_procedure_stats (Transact-SQL), and for queries with sys.dm_exec_query_stats (Transact-SQL).

A seguinte consulta retorna os nomes de procedimento e as estatísticas de execução para procedimentos armazenados compilados de modo nativo no banco de dados atual, após a coleta de estatísticas:The following query returns the procedure names and execution statistics for natively compiled stored procedures in the current database, after statistics collection:

SELECT
        object_id,
        object_name(object_id) as 'object name',
        cached_time,
        last_execution_time,  execution_count,
        total_worker_time,    last_worker_time,
        min_worker_time,      max_worker_time,
        total_elapsed_time,   last_elapsed_time,
        min_elapsed_time,     max_elapsed_time
    from
        sys.dm_exec_procedure_stats
    where
        database_id = db_id()
        and
        object_id in
            (
            SELECT object_id
                from sys.sql_modules
                where uses_native_compilation=1
            )
    order by
        total_worker_time desc;

A seguinte consulta retorna o texto da consulta, bem como as estatísticas de execução para todas as consultas em procedimentos armazenados compilados de modo nativo no banco de dados atual, para as quais as estatísticas foram coletadas, ordenadas pelo tempo de trabalho total, em ordem decrescente:The following query returns the query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected, ordered by total worker time, in descending order:

SELECT
        st.objectid,
        object_name(st.objectid) as 'object name',
        SUBSTRING()
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) as 'query text',
        qs.creation_time,
        qs.last_execution_time,   qs.execution_count,
        qs.total_worker_time,     qs.last_worker_time,
        qs.min_worker_time,       qs.max_worker_time,
        qs.total_elapsed_time,    qs.last_elapsed_time,
        qs.min_elapsed_time,      qs.max_elapsed_time
    FROM
                    sys.dm_exec_query_stats qs
        cross apply sys.dm_exec_sql_text(sql_handle) st
    WHERE
        st.dbid = db_id()
        and
        st.objectid in
            (SELECT object_id
                from sys.sql_modules
                where uses_native_compilation=1
            )
    ORDER BY
        qs.total_worker_time desc;

Planos de execução de consultaQuery Execution Plans

Os procedimentos armazenados compilados de modo nativo dão suporte ao SHOWPLAN_XML (plano de execução estimado).Natively compiled stored procedures support SHOWPLAN_XML (estimated execution plan). O plano de execução estimado pode ser usado para inspecionar o plano de consulta, para localizar quaisquer problemas de plano incorreto.The estimated execution plan can be used to inspect the query plan, to find any bad plan issues. As razões comuns de planos incorretos são:Common reasons for bad plans are:

  • As estatísticas não foram atualizadas antes da criação do procedimento.Stats were not updated before the procedure was created.

  • Índices ausentesMissing indexes

O plano de execução XML é obtido executando o seguinte Transact-SQLTransact-SQL:Showplan XML is obtained by executing the following Transact-SQLTransact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO  

Como alternativa, no SQL Server Management StudioSQL Server Management Studio, selecione o nome do procedimento e clique em Exibir Plano de Execução Estimado.Alternatively, in SQL Server Management StudioSQL Server Management Studio, select the procedure name and click Display Estimated Execution Plan.

O plano de execução estimado para procedimentos armazenados compilados de modo nativo mostra os operadores e as expressões para as consultas no procedimento.The estimated execution plan for natively compiled stored procedures shows the query operators and expressions for the queries in the procedure. SQL Server 2014 (12.x)SQL Server 2014 (12.x) não dá suporte a todos os atributos SHOWPLAN_XML para procedimentos armazenados compilados de modo nativo.does not support all SHOWPLAN_XML attributes for natively compiled stored procedures. Por exemplo, os atributos relacionados ao cálculo de custos do otimizador de consulta não fazem parte do SHOWPLAN_XML para o procedimento.For example, attributes related to query optimizer costing are not part of the SHOWPLAN_XML for the procedure.

Consulte TambémSee Also

Procedimentos armazenados compilados nativamenteNatively Compiled Stored Procedures