Surveillance des performances des procédures stockées compilées en mode natif

Article
05/30/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article explique comment surveiller les performances des procédures stockées compilées en mode natif et celles d’autres modules T-SQL compilés en mode natif.

Utilisation des événements étendus

Utilisez l’événement étendu sp_statement_completed pour tracer l’exécution d’une requête. Créez une session d’événements étendus avec cet événement, éventuellement avec un filtre sur object_id pour une procédure stockée compilée en mode natif. L’événement étendu est déclenché après l’exécution de chaque requête. Le temps processeur et la durée signalés par l'événement étendu indiquent l'UC utilisée par la requête et le temps d'exécution. Une procédure stockée compilée en mode natif qui utilise beaucoup de temps processeur peut rencontrer des problèmes de performances.

Vous pouvez utiliser line_number et object_id dans l’événement étendu pour analyser la requête. La requête suivante peut être utilisée pour extraire la définition de procédure. Le numéro de ligne peut être utilisé pour identifier la requête dans la définition :

SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;

Utilisation des vues de gestion des données et du magasin des requêtes

SQL Server et Azure SQL Database prennent en charge la collecte des statistiques d’exécution pour les procédures stockées compilées en mode natif, à la fois au niveau de la procédure et au niveau de la requête. Le collecte des statistiques d'exécution n'est pas activée par défaut en raison de l'impact sur les performances.

Les statistiques d’exécution sont présentées dans les vues système sys.dm_exec_procedure_stats et sys.dm_exec_query_stats, ainsi que dans le magasin des requêtes.

Statistiques d’exécution au niveau de la procédure

SQL Server : activez ou désactivez la collecte de statistiques sur les procédures stockées compilées en mode natif au niveau de la procédure à l’aide de sys.sp_xtp_control_proc_exec_stats (Transact-SQL). L’instruction suivante active la collecte des statistiques d’exécution au niveau de la procédure pour tous les modules T-SQL compilés en mode natif sur l’instance actuelle :

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL Database et SQL Server : activez ou désactivez la collecte de statistiques sur les procédures stockées compilées en mode natif au niveau de la procédure à l’aide de l’option XTP_PROCEDURE_EXECUTION_STATISTICSde configuration délimitée à la base de données. L’instruction suivante active la collecte des statistiques d’exécution au niveau de la procédure pour tous les modules T-SQL compilés en mode natif dans la base de données actuelle :

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Statistiques d’exécution au niveau de la requête

SQL Server : activez ou désactivez la collecte de statistiques sur les procédures stockées compilées en mode natif au niveau de la requête à l’aide de sys.sp_xtp_control_query_exec_stats (Transact-SQL). L’instruction suivante active la collecte des statistiques d’exécution au niveau de la requête pour tous les modules T-SQL compilés en mode natif sur l’instance actuelle :

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL Database et SQL Server : activez ou désactivez la collecte de statistiques sur les procédures stockées compilées en mode natif au niveau de l’instruction à l’aide de l’option XTP_QUERY_EXECUTION_STATISTICSde configuration étendue à la base de données. L’instruction suivante active la collecte des statistiques d’exécution au niveau de la requête pour tous les modules T-SQL compilés en mode natif dans la base de données actuelle :

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Exemples de requêtes

Après avoir collecté des statistiques, les statistiques d’exécution pour les procédures stockées compilées en mode natif peuvent être interrogées pour une procédure avec sys.dm_exec_procedure_stats (Transact-SQL) et pour les requêtes avec sys.dm_exec_query_stats (Transact-SQL) .

La requête suivante retourne les noms de procédure et les statistiques d'exécution des procédures stockées compilées en mode natif dans la base de données active, après collection de statistiques :

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;

La requête suivante retourne le texte des requêtes ainsi que les statistiques d'exécution de toutes les requêtes dans les procédures stockées compilées en mode natif dans la base de données active pour laquelle les statistiques ont été collectées, triées par temps total de travail, dans l'ordre décroissant :

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;

Plans d’exécution de requête

Les procédures stockées compilées en mode natif prennent en charge SHOWPLAN_XML (plan d'exécution de requêtes). Le plan d'exécution estimé permet d'inspecter le plan de requête, afin de trouver des problèmes de plan erroné. Causes courantes de plans incorrects :

Les statistiques n'ont pas été mises à jour avant de créer la procédure.
Index manquants

Showplan XML est obtenu en exécutant transact-SQL suivant :

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

Sinon, dans SQL Server Management Studio, sélectionnez le nom de la procédure, puis cliquez sur Afficher le plan d’exécution estimé.

Le plan d'exécution estimé pour les procédures stockées compilées en mode natif affiche les opérateurs de requête et les expressions des requêtes dans la procédure. SQL Server 2014 (12.x) ne prend pas en charge tous les attributs SHOWPLAN_XML pour les procédures stockées compilées en mode natif. Par exemple, les attributs liés au coût de l'optimiseur de requête ne font pas partie de SHOWPLAN_XML pour la procédure.

Voir aussi

Procédures stockées compilées en mode natif