Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure
Este artigo descreve como você pode monitorar o desempenho de procedimentos armazenados compilados nativamente e outros módulos T-SQL compilados nativamente.
Usando Eventos Extendidos
Utilize o evento estendido sp_statement_completed para rastrear a execução de uma consulta. Crie uma sessão de evento estendida com esse evento, opcionalmente com um filtro em object_id para um determinado procedimento armazenado compilado nativamente. O evento estendido é gerado após a execução de cada consulta. O tempo e a duração da CPU relatados pelo evento estendido indicam a quantidade de CPU usada pela consulta e o tempo de execução. Um procedimento armazenado compilado nativamente que usa muito tempo de CPU pode ter problemas de desempenho.
O line_number, juntamente com o object_id, pode ser usado no evento estendido para investigar a consulta. A consulta a seguir pode ser usada para recuperar a definição de procedimento. O número da linha pode ser usado para identificar a consulta dentro da definição:
SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;
Usando Visões de Gestão de Dados e Armazenamento de Consultas
O SQL Server e o Banco de Dados SQL do Azure dão suporte à coleta de estatísticas de execução para procedimentos armazenados compilados nativamente, tanto no nível do procedimento quanto no nível da consulta. A coleta de estatísticas de execução não está habilitada por padrão devido ao impacto no desempenho.
As estatísticas de execução são refletidas nas visualizações do sistema sys.dm_exec_procedure_stats e sys.dm_exec_query_stats, bem como no Query Store.
Procedure-Level Estatísticas de Execução
SQL Server: habilite ou desative a coleta de estatísticas em procedimentos armazenados compilados nativamente ao nível do procedimento usando sys.sp_xtp_control_proc_exec_stats (Transact-SQL). A instrução a seguir permite a coleta de estatísticas de execução no nível de procedimento para todos os módulos T-SQL compilados nativamente na instância atual:
EXEC sys.sp_xtp_control_proc_exec_stats 1
Base de Dados SQL do Azure e SQL Server: ative ou desative a coleta de estatísticas em procedimentos armazenados compilados nativamente no nível do procedimento usando a opção de configuração com escopo de base de dados XTP_PROCEDURE_EXECUTION_STATISTICS. A instrução a seguir permite a coleta de estatísticas de execução no nível de procedimento para todos os módulos T-SQL compilados nativamente no banco de dados atual:
ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;
Query-Level Estatísticas de Execução
SQL Server: habilite ou desative a coleta de estatísticas em procedimentos armazenados compilados nativamente ao nível de consulta usando sys.sp_xtp_control_query_exec_stats (Transact-SQL). A instrução a seguir permite a coleta de estatísticas de execução no nível de consulta para todos os módulos T-SQL compilados nativamente na instância atual:
EXEC sys.sp_xtp_control_query_exec_stats 1
Banco de Dados SQL do Azure e SQL Server: ative ou desative a coleta de estatísticas em procedimentos armazenados compilados nativamente ao nível da instrução utilizando a opção de configuração com escopo de base de dadosXTP_QUERY_EXECUTION_STATISTICS. A instrução a seguir permite a coleta de estatísticas de execução no nível de consulta para todos os módulos T-SQL compilados nativamente no banco de dados atual:
ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;
Consultas de exemplo
Depois de coletar estatísticas, as estatísticas de execução para procedimentos armazenados compilados nativamente podem ser consultadas para um procedimento com sys.dm_exec_procedure_stats (Transact-SQL)e para consultas com sys.dm_exec_query_stats (Transact-SQL).
A consulta a seguir retorna os nomes de procedimento e as estatísticas de execução para procedimentos armazenados compilados nativamente no banco de dados atual, após a coleta de estatísticas:
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 consulta a seguir retorna o texto da consulta, bem como as estatísticas de execução para todas as consultas em procedimentos armazenados compilados nativamente no banco de dados atual para os quais as estatísticas foram coletadas, ordenadas pelo tempo total do trabalhador, em ordem decrescente:
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 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;
Planos de execução de consultas
Procedimentos armazenados compilados nativamente suportam SHOWPLAN_XML (plano de execução estimado). O plano de execução estimado pode ser usado para inspecionar o plano de consulta, para encontrar quaisquer problemas de plano incorreto. Razões comuns para maus planos são:
As estatísticas não foram atualizadas antes da criação do procedimento.
Índices em falta
O Showplan XML é obtido executando o seguinte Transact-SQL:
SET SHOWPLAN_XML ON
GO
EXEC my_proc
GO
SET SHOWPLAN_XML OFF
GO
Como alternativa, no SQL Server Management Studio, selecione o nome do procedimento e clique em Exibir Plano de Execução Estimado .
O plano de execução estimado para procedimentos armazenados compilados nativamente mostra os operadores de consulta e as expressões para as consultas no procedimento. O SQL Server 2014 (12.x) não oferece suporte a todos os atributos SHOWPLAN_XML para procedimentos armazenados compilados nativamente. Por exemplo, atributos relacionados ao cálculo de custos do otimizador de consulta não fazem parte do SHOWPLAN_XML para o procedimento.