Partager via

Guide de validation et d’optimisation post-migration

S'applique à :SQL Server

L’étape post-migration de SQL Server est cruciale pour rapprocher et compléter les données, ainsi que pour détecter les problèmes de performances liés à la charge de travail.

Scénarios de niveau de performances courants

Voici quelques-uns des scénarios de performance courants rencontrés après la migration vers la plateforme SQL Server et leur résolution. Certains scénarios sont spécifiques à la migration de SQL Server vers SQL Server (d’une version antérieure vers une version plus récente), d’autres à la migration d’une plateforme étrangère (comme Oracle, DB2, MySQL ou Sybase) vers SQL Server.

Régression des requêtes en raison d’un changement de version de l’estimateur de la cardinalité (CE)

S’applique à : la migration de SQL Server vers SQL Server.

Quand vous migrez d’une ancienne version de SQL Server vers SQL Server 2014 (12.x) ou une version ultérieure et que vous passez au tout dernier niveau de compatibilité de la base de données, il est possible que les performances d’une charge de travail fassent l’objet d’une régression.

Cela vient du fait qu’à compter de SQL Server 2014 (12.x), tous les changements de l’optimiseur de requête sont liés au tout dernier niveau de compatibilité de base de données, de sorte que les plans ne sont pas changés au moment même de la mise à niveau, mais quand un utilisateur remplace l’option de base de données COMPATIBILITY_LEVEL par la plus récente. Cette fonctionnalité, en association avec le magasin de requêtes, vous offre un niveau de contrôle élevé sur les performances des requêtes dans le processus de mise à niveau.

Pour plus d’informations sur les changements apportés à l’optimiseur de requête dans SQL Server 2014 (12.x), consultez Optimisation de vos plans de requête avec l’estimateur de cardinalité SQL Server 2014.

Pour plus d’informations sur le CE, consultez Estimation de la cardinalité (SQL Server).

Étapes de résolution

Remplacez le niveau de compatibilité de base de données par celui de la version de la source, puis suivez la procédure de mise à niveau recommandée que présente l’image suivante :

Diagramme montrant le workflow de mise à niveau recommandé.

Pour plus d’informations sur cet article, consultez Maintenir la stabilité des performances lors de la mise à niveau vers une version plus récente de SQL Server.

Sensibilité de la détection de paramètres

S’applique à : Plateforme étrangère (par exemple Oracle, DB2, MySQL et Sybase) et à la migration de SQL Server.

Remarque

Pour les migrations SQL Server vers SQL Server, si ce problème existait dans sql Server source, la migration vers une version plus récente de SQL Server as-is ne résout pas ce scénario.

SQL Server compile les plans de requête sur les procédures stockées en utilisant la détection de paramètres d’entrée au moment de la première compilation et en générant un plan paramétrable et réutilisable, optimisé pour la distribution des données d’entrée. Même s’il ne s’agit pas de procédures stockées, la plupart des instructions qui génèrent des plans triviaux sont paramétrables. Après la première mise en cache d’un plan, les exécutions futures sont mappées au plan déjà mis en cache.

Un problème peut se produire quand la première compilation n’utilise pas les jeux de paramètres les plus courants pour la charge de travail usuelle. Pour des paramètres distincts, le même plan d’exécution devient inefficace. Pour plus d’informations sur cet article, consultez le paramètre de sensibilité.

Étapes de résolution

  1. Utilisez le conseil RECOMPILE. Un plan est calculé chaque fois de manière adaptée à chaque valeur de paramètre.

  2. Réécrivez la procédure stockée pour utiliser l’option (OPTIMIZE FOR(<input parameter> = <value>)). Déterminez la valeur à utiliser qui correspond le mieux à la plupart des charges de travail appropriées. Cela vous permet de créer et de gérer un plan qui devient efficace pour la valeur paramétrable.

  3. Réécrivez la procédure stockée en utilisant une variable locale dans la procédure. L’optimiseur utilise désormais le vecteur de densité pour les estimations, ce qui permet d’obtenir le même plan, quelle que soit la valeur du paramètre.

  4. Réécrivez la procédure stockée pour utiliser l’option (OPTIMIZE FOR UNKNOWN). Même effet qu’avec la technique de la variable locale.

  5. Réécrivez la requête pour utiliser le conseil DISABLE_PARAMETER_SNIFFING. Même effet qu’avec la technique de la variable locale en désactivant totalement la détection de paramètres, sauf si OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR <value> est utilisé.

Conseil

Utilisez la fonctionnalité d’analyse de plan de Management Studio pour déterminer rapidement s’il s’agit d’un problème. Pour plus d’informations, consultez Nouveautés de SSMS : Résolution des problèmes des performances des requêtes plus facile.

Index manquants

S’applique à : Plateforme étrangère (par exemple Oracle, DB2, MySQL et Sybase) et à la migration de SQL Server vers SQL Server.

Les index incorrects ou manquants provoquent des suppléments d’E/S qui entraînent un gaspillage de mémoire et d’UC. Cela peut être dû au fait que le profil de charge de travail a changé, par exemple en raison de l’utilisation d’autres prédicats ou à la suite de l’invalidation de la conception d’index existante. Voici comment identifier une mauvaise stratégie d’indexation ou l’existence de changements dans le profil de charge de travail :

  • Recherchez les index dupliqués, redondants, rarement utilisés et complètement inutilisés.
  • Prêtez une attention particulière aux index inutilisés avec des mises à jour.

Étapes de résolution

  1. Utiliser le plan d’exécution graphique pour les références d’index manquantes.

  2. Indexez les suggestions générées par l’Assistant Paramétrage du moteur de base de données.

  3. Utilisez le sys.dm_db_missing_index_details.

  4. Utilisez des scripts préexistants qui peuvent utiliser des DMV existantes pour fournir un aperçu des index manquants, dupliqués, redondants, rarement utilisés et complètement inutilisés, mais également si une référence d’index est indiquée/codée en dur dans les procédures et fonctions existantes de votre base de données.

Conseil

Parmi ces scripts préexistants, citons la création d’index et les informations d’index.

Incapacité à utiliser les prédicats pour filtrer les données

S’applique à : Plateforme étrangère (par exemple Oracle, DB2, MySQL et Sybase) et à la migration de SQL Server vers SQL Server.

Remarque

Pour les migrations SQL Server vers SQL Server, si ce problème existait dans sql Server source, la migration vers une version plus récente de SQL Server as-is ne résout pas ce scénario.

L’optimiseur de requête SQL Server peut uniquement prendre en compte les informations connues au moment de la compilation. Si une charge de travail s’appuie sur des prédicats qui ne peuvent être connus qu’au moment de l’exécution, le potentiel d’un mauvais choix de plan augmente. Pour un plan de meilleure qualité, les prédicats doivent être sargables.

Remarque

Le terme SARGable dans les bases de données relationnelles fait référence à unprédicatS earch ARGcapable d’utiliser un index pour accélérer l’exécution de la requête. Pour plus d’informations, consultez sql Server et l’architecture d’index Azure SQL et le guide de conception.

Voici quelques exemples de prédicats non SARGables :

  • Conversions de données implicites, par exemple de varchar à nvarchar, ou de int à varchar. Recherchez les avertissements d’exécution liés à CONVERT_IMPLICIT dans les plans d’exécution réels. La conversion d’un type vers un autre type peut également entraîner une perte de précision.

  • Expressions indéterminées complexes telles que WHERE UnitPrice + 1 < 3.975 mais pas WHERE UnitPrice < 320 * 200 * 32.

  • Expressions utilisant des fonctions, telles que WHERE ABS(ProductID) = 771 ou WHERE UPPER(LastName) = 'Smith'

  • Chaînes commençant par un caractère générique, par exemple WHERE LastName LIKE '%Smith' mais pas WHERE LastName LIKE 'Smith%'.

Étapes de résolution

  1. Déclarez toujours les variables/paramètres en tant que types de données cible prévu.

    Cela peut impliquer la comparaison des constructions de code définies par l’utilisateur stockées dans la base de données (telles que les procédures stockées, les fonctions définies par l’utilisateur ou les vues) avec les tables système qui contiennent des informations sur les types de données utilisés dans les tables sous-jacentes (telles que sys.columns).

  2. Si vous ne parvenez pas à traverser l’ensemble du code jusqu’au point précédent, pour la même finalité, changez le type de données de la table afin qu’il corresponde à une déclaration de variable/paramètre.

  3. Vérifiez l’utilité des constructions suivantes :

    • fonctions utilisées en tant que prédicats ;
    • recherches à l’aide de caractères génériques ;
    • expressions complexes basées sur des données en colonne (évaluez la nécessité de créer plutôt des colonnes calculées persistantes, lesquelles sont indexables).

Remarque

Toutes ces étapes peuvent être réalisées par programmation.

Utilisation de fonctions table (à instructions multiples ou inline)

S’applique à : Plateforme étrangère (par exemple Oracle, DB2, MySQL et Sybase) et à la migration de SQL Server vers SQL Server.

Remarque

Pour les migrations SQL Server vers SQL Server, si ce problème existait dans sql Server source, la migration vers une version plus récente de SQL Server as-is ne résout pas ce scénario.

Les fonctions table retournent un type de données de table qui peut représenter une alternative aux vues. Alors que les vues sont limitées à une seule instruction SELECT, les fonctions définies par l’utilisateur peuvent contenir des instructions supplémentaires qui autorisent plusieurs logiques dans les vues.

Étant donné que la table de sortie d’une fonction table à plusieurs instructions (MSTVF) n’est pas créée au moment de la compilation, l’optimiseur de requête SQL Server s’appuie sur des heuristiques, et non sur des statistiques réelles, pour déterminer les estimations de lignes.

Même si des index sont ajoutés aux tables de base, cela n’est pas utile.

En ce qui concerne les fonctions table à instructions multiples, SQL Server utilise une estimation fixe égale à 1 pour le nombre de lignes à retourner par une fonction table à instructions multiples (à partir de SQL Server 2014 (12.x), cette estimation fixe est de 100 lignes).

Étapes de résolution

  1. Si la fonction MSTVF est une seule instruction, convertissez en fonction table inline.

    CREATE FUNCTION dbo.tfnGetRecentAddress (@ID INT)
RETURNS
    @tblAddress TABLE ([Address] VARCHAR (60) NOT NULL)
AS
BEGIN
    INSERT INTO @tblAddress ([Address])
    SELECT TOP 1 [AddressLine1]
    FROM [Person].[Address]
    WHERE AddressID = @ID
    ORDER BY [ModifiedDate] DESC;
    RETURN;
END

    L’exemple de format inline s’affiche ci-après.

    CREATE FUNCTION dbo.tfnGetRecentAddress_inline
(@ID INT)
RETURNS TABLE
AS
RETURN
    (SELECT TOP 1 [AddressLine1] AS [Address]
     FROM [Person].[Address]
     WHERE AddressID = @ID
     ORDER BY [ModifiedDate] DESC)

  2. Si la situation est plus complexe, utilisez des résultats intermédiaires stockés dans des tables à mémoire optimisée ou des tables temporaires.

Contenu connexe

  • Last updated on