Identifier les plans de requête problématiques

Effectué

L’approche classique que les administrateurs de base de données prennent pour résoudre les problèmes de performances des requêtes implique d’abord d’identifier la requête problématique, généralement celle qui consomme le plus de ressources système, puis de récupérer son plan d’exécution. Il existe deux scénarios principaux. Dans l’un des scénarios, la requête s’exécute de manière cohérente. Cela peut être dû à différents problèmes, tels que les contraintes de ressources matérielles (bien que cela n’affecte généralement pas une requête unique s’exécutant en isolation), une structure de requête non optimale, des paramètres de compatibilité de base de données, des index manquants ou des choix de plan médiocres par l’optimiseur de requête. Dans le deuxième scénario, la requête fonctionne bien dans certaines exécutions, mais mal dans d’autres. Cette incohérence peut être due à des facteurs tels que l’asymétrie des données dans une requête paramétrable, qui a un plan efficace pour certaines exécutions et un mauvais plan pour d’autres. D’autres facteurs courants incluent le blocage, où une requête attend qu’une autre requête se termine pour accéder à une table ou à une contention matérielle.

Examinons chacun de ces scénarios plus en détail.

Contraintes matérielles

Les contraintes matérielles ne se manifestent généralement pas pendant les exécutions de requêtes uniques, mais deviennent évidentes sous une charge de production lorsque les threads de processeur et la mémoire sont limités. La contention du processeur peut être détectée en observant le compteur de moniteur de performances « % de temps processeur », qui mesure l’utilisation du processeur du serveur. Dans SQL Server, les types d’attente SOS_SCHEDULER_YIELD et CXPACKET peuvent indiquer la pression du processeur. Les performances médiocres du système de stockage peuvent ralentir même les exécutions de requêtes uniques optimisées. Les performances de stockage sont mieux suivies au niveau du système d’exploitation à l’aide de compteurs de moniteur de performances Disk Seconds/Read et Disk Seconds/Write, qui mesurent les temps d’achèvement des opérations d’E/S. SQL Server enregistre des performances de stockage médiocres si une E/S prend plus de 15 secondes. Des temps d’attente PAGEIOLATCH_SH élevés dans SQL Server peuvent indiquer des problèmes de performances de stockage. Les performances matérielles sont généralement évaluées au début du processus de résolution des problèmes en raison de sa facilité d’évaluation.

La plupart des problèmes de performances de base de données proviennent de modèles de requête non optimaux, ce qui peut entraîner une pression excessive sur le matériel. Par exemple, les index manquants peuvent entraîner une sollicitation du processeur, du stockage et de la mémoire en récupérant plus de données que nécessaire. Il est recommandé de résoudre et de régler les requêtes non optimales avant de résoudre les problèmes matériels. Ensuite, nous examinons le réglage des requêtes.

Constructions de requêtes non optimales

Les bases de données relationnelles fonctionnent le mieux lors de l’exécution d’opérations basées sur un jeu, qui manipulent des données (INSERT, UPDATE, DELETE et SELECT) dans des jeux, produisant une valeur unique ou un jeu de résultats. L’alternative est le traitement basé sur les lignes, à l’aide de curseurs ou de boucles WHILE, qui augmentent les coûts de manière linéaire en fonction du nombre de lignes affectées, une échelle problématique à mesure que les volumes de données augmentent.

La détection d’une utilisation non optimale des opérations basées sur des lignes avec des curseurs ou des boucles WHILE est importante, mais il existe d’autres contre-modèles SQL Server à reconnaître. Les fonctions table (TVF), en particulier les fichiers TVF à plusieurs instructions, ont provoqué des modèles de plan d’exécution problématiques avant SQL Server 2017. Les développeurs utilisent souvent des fonctions TVF à plusieurs instructions pour exécuter plusieurs requêtes au sein d’une fonction unique et agréger les résultats dans une table unique. Toutefois, l’utilisation de TVF peut entraîner des pénalités de performances.

SQL Server a deux types de fichiers TVF : inline et à plusieurs instructions. Les fichiers TVF inline sont traités comme des vues, tandis que les TVF à plusieurs instructions sont traités comme des tables pendant le traitement des requêtes. Étant donné que les fichiers TVF sont dynamiques et manquent de statistiques, SQL Server utilise un nombre de lignes fixe pour estimer le coût du plan de requête. Cela peut être correct pour les petits nombres de lignes, mais inefficace pour des milliers ou des millions de lignes.

Un autre anti-modèle est l’utilisation de fonctions scalaires, qui présentent des problèmes d’estimation et d’exécution similaires. Microsoft a apporté des améliorations significatives des performances avec le traitement intelligent des requêtes, sous les niveaux de compatibilité 140 et 150.

SARGability

Le terme « SARGable » dans les bases de données relationnelles fait référence à un prédicat (clause WHERE) mis en forme pour utiliser un index pour accélérer l’exécution des requêtes. Les prédicats au format correct sont appelés des arguments de recherche, ou SRAG. Dans SQL Server, l’utilisation d’un SARG signifie que l’optimiseur évalue à l’aide d’un index non-cluster sur la colonne référencée dans le SARG pour une opération SEEK , au lieu d’analyser l’intégrité de l’index ou de la table pour récupérer une valeur.

La présence d’une SARG ne garantit pas l’utilisation d’un index pour un SEEK. Les algorithmes de coût de l’optimiseur peuvent toujours déterminer que l’index est trop coûteux, en particulier si un SARG fait référence à un grand pourcentage des lignes d’une table. L’absence d’un SARG signifie que l’optimiseur n’évalue pas un SEEK sur un index non-cluster.

Les exemples d’expressions non SARGables incluent ceux avec une clause LIKE utilisant un caractère générique au début de la chaîne, comme WHERE lastName LIKE '%SMITH%'. D’autres prédicats non SARGables se produisent lors de l’utilisation de fonctions sur une colonne, comme WHERE CONVERT(CHAR(10), CreateDate,121) = '2020-03-22'. Ces requêtes sont généralement identifiées en examinant les plans d’exécution pour les analyses d’index ou de table où les recherches doivent se produire autrement.

Il existe un index sur la colonne City qui est utilisé dans la WHERE clause de la requête et pendant qu’il est utilisé dans ce plan d’exécution ci-dessus, vous pouvez voir que l’index est analysé, ce qui signifie que l’index entier est lu. La fonction LEFT dans le prédicat rend cette expression non SARGable. L’optimiseur n’effectuera pas d’évaluation en effectuant une recherche d’index sur l’index de la colonne City.

Cette requête peut être écrite pour utiliser un prédicat SARGable. L’optimiseur évalue ensuite une recherche (SEEK) sur l’index de la colonne City. Dans ce cas, un opérateur de recherche d’index lit un plus petit ensemble de lignes.

Transformer la fonction LEFT en LIKE entraîne une recherche d'index.

Remarque

Le mot clé LIKE, dans cet exemple, n’a pas de caractère générique à gauche. Il recherche donc les villes qui commencent par M. S’il était « à deux côtés » ou démarré avec un caractère générique (%M% ou %M) il serait non SARGable. L’opération de recherche devrait retourner 1 267 lignes, ou environ 15 % de l’estimation de la requête avec le prédicat non SARGable.

D’autres anti-modèles de développement de base de données traitent la base de données comme un service plutôt que comme un magasin de données. L’utilisation d’une base de données pour convertir des données au format JSON, manipuler des chaînes ou effectuer des calculs complexes peut entraîner une utilisation excessive du processeur et une latence accrue. Les requêtes qui tentent de récupérer tous les enregistrements, puis d’effectuer des calculs dans la base de données, peuvent entraîner une utilisation excessive des E/S et du processeur. Dans l’idéal, vous devriez utiliser la base de données pour les opérations d’accès aux données et pour les constructions de base de données optimisées, comme l’agrégation.

Index manquants

Les problèmes de performances les plus courants pour les administrateurs de base de données proviennent d’un manque d’index utiles, ce qui entraîne la lecture du moteur plus de pages que nécessaire pour retourner les résultats de la requête. Bien que les index consomment des ressources (affectant les performances d’écriture et l’espace de consommation), leurs gains de performances dépassent souvent les coûts de ressources supplémentaires. Les plans d’exécution avec ces problèmes peuvent être identifiés par l’opérateur de requête Analyse d’index cluster ou la combinaison de Recherche d’index non-cluster et Recherche de clés, indiquant les colonnes manquantes dans un index existant.

Le moteur de base de données permet de signaler les index manquants dans les plans d’exécution. Les noms et les détails des index recommandés sont disponibles via la vue de gestion dynamique sys.dm_db_missing_index_details. D’autres DMV tels que sys.dm_db_index_usage_stats et sys.dm_db_index_operational_stats mettent en évidence l’utilisation des index existants.

La suppression d’un index inutilisé peut être sensible. Les vues DMV d’index manquantes et les avertissements de plan doivent être des points de départ pour le paramétrage des requêtes. Il est essentiel de comprendre les requêtes clés et de créer des index pour les prendre en charge. Il n’est pas recommandé de créer tous les index manquants sans les évaluer dans le contexte.

Statistiques manquantes et obsolètes

Comprendre l’importance des statistiques de colonne et d’index pour l’optimiseur de requête est primordial. Il est également essentiel de reconnaître les conditions qui peuvent entraîner des statistiques obsolètes et la façon dont ce problème peut se manifester dans SQL Server. Les offres Azure SQL proposent par défaut des statistiques de mise à jour automatique définies sur Activé. Avant SQL Server 2016, le comportement par défaut des statistiques de mise à jour automatique n’était pas de mettre à jour les statistiques tant que le nombre de modifications apportées aux colonnes de l’index était égal à 20 % du nombre de lignes d’une table. Ce comportement pouvait entraîner des modifications significatives des données qui modifient les performances des requêtes sans mettre à jour les statistiques, ce qui entraîne des plans non optimaux basés sur des statistiques obsolètes.

Avant SQL Server 2016, l’indicateur de trace 2371 peut être utilisé pour modifier le nombre requis de modifications apportées à une valeur dynamique, de sorte que votre table a augmenté, le pourcentage de modifications de ligne nécessaires pour déclencher une mise à jour des statistiques a diminué. Les versions plus récentes de SQL Server, d’Azure SQL Database et d’Azure SQL Managed Instance prennent en charge ce comportement par défaut. La fonction de gestion dynamique sys.dm_db_stats_properties affiche la dernière fois que les statistiques ont été mises à jour et le nombre de modifications depuis la dernière mise à jour, ce qui vous permet d’identifier rapidement les statistiques susceptibles de nécessiter des mises à jour manuelles.

Mauvais choix de l’optimiseur

Bien que l’optimiseur de requête effectue un bon travail d’optimisation de la plupart des requêtes, dans certains cas de périphérie, l’optimiseur basé sur les coûts peut prendre des décisions impactantes qui ne sont pas entièrement comprises. Il existe de nombreuses méthodes pour remédier à cela, notamment l’utilisation d’indicateurs de requête ou d’indicateurs de trace, le forçage de plan d’exécution et d’autres ajustements permettant d’obtenir un plan de requête stable et optimal. Microsoft dispose d’une équipe de support qui peut vous aider dans de tels scénarios.

Dans l’exemple ci-dessous de la base de données AdventureWorks2017 , un indicateur de requête est utilisé pour indiquer à l’optimiseur de base de données de toujours utiliser un nom de ville de Seattle. Cet indicateur ne garantit pas le meilleur plan d’exécution pour toutes les valeurs de ville, mais il est prévisible. La valeur de « Seattle » pour @city_name ne sera utilisée que pendant l’optimisation. Pendant l’exécution, la valeur fournie réelle (‘Ascheim’) est utilisée.

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

Comme le montre l’exemple, la requête utilise un indicateur (la clause OPTION) pour indiquer à l’optimiseur qu’il doit utiliser une valeur de variable spécifique afin de générer son plan d’exécution.

Détection de paramètre

SQL Server met en cache les plans d’exécution des requêtes en vue d’une utilisation ultérieure. Étant donné que le processus de récupération des plans d’exécution est basé sur la valeur de hachage d’une requête, le texte de la requête doit être identique à chaque exécution pour que le plan mis en cache soit utilisé. Pour prendre en charge plusieurs valeurs dans la même requête, de nombreux développeurs utilisent des paramètres, transmis via des procédures stockées, comme indiqué dans l’exemple suivant :

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

Les requêtes peuvent également être paramétrées explicitement à l’aide de la procédure sp_executesql. Toutefois, le paramétrage explicite des requêtes individuelles est effectué via l’application avec un certain formulaire (selon l’API) de PREPARE et EXECUTE. Lorsque le moteur de base de données exécute cette requête pour la première fois, il optimise la requête en fonction de la valeur initiale du paramètre, dans ce cas, 42. Ce comportement appelé « détection des paramètres » permet de réduire la charge de travail globale qui consiste à compiler les requêtes sur le serveur. Toutefois, s’il existe une asymétrie des données, les performances des requêtes pourraient largement varier.

Prenons l’exemple d’une table de 10 millions d’enregistrements. Si 99 % de ces enregistrements ont un ID de 1, et si les 1 % restants sont des nombres uniques, les performances sont basées sur l’ID initialement utilisé pour optimiser la requête. Des performances qui fluctuent de manière importante indiquent une asymétrie des données et n’est pas un problème inhérent à la détection des paramètres. Ce comportement est un problème de performances assez courant que vous devez connaître. Vous devez connaître les options qui permettent d’atténuer le problème. Il existe plusieurs façons de résoudre ce problème. Cela dit, chacune présente des inconvénients :

• Utilisez l’indicateur RECOMPILE dans votre requête ou l’option WITH RECOMPILE d’exécution dans vos procédures stockées. Cet indicateur entraîne la recompilation de la requête ou de la procédure chaque fois qu’elle est exécutée, ce qui augmente l’utilisation du processeur sur le serveur, mais utilise toujours la valeur de paramètre actuelle.
• Vous pouvez utiliser l’indicateur de requête OPTIMIZE FOR UNKNOWN. Cet indicateur entraîne le choix de l’optimiseur de ne pas détecter les paramètres et de comparer la valeur avec l’histogramme de données de colonne. Cette option ne vous permettra pas d’obtenir le meilleur plan possible, mais elle permet d’avoir un plan d’exécution cohérent.
• Réécrivez votre procédure ou vos requêtes en ajoutant une logique autour des valeurs de paramètre de manière à recompiler uniquement pour les paramètres qui sont connus pour causer des problèmes. Dans l’exemple ci-dessous, si le paramètre SalesPersonID a la valeur NULL, la requête est exécutée avec le OPTION (RECOMPILE).

CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

Cet exemple est une bonne solution, mais il nécessite un effort de développement assez important et une compréhension ferme de votre distribution de données. Cela nécessite une maintenance au fur et à mesure que les données changent.