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 :

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.

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é.

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 sys.dm_db_missing_index_details ou le Tableau de bord Performances SQL Server.
4. Utilisez les scripts préexistants qui peuvent utiliser les vues DMV existantes pour fournir un insight des index manquants, dupliqués, redondants, rarement utilisés et complètement inutilisés, ainsi que des références d’index avec indicateur/codées en dur dans les procédures et fonctions de votre base de données.

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.

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 permettre l’amélioration de la qualité d’un plan, les prédicats doivent être SARGable, ou Search Argumentable.

Voici quelques exemples de prédicats non SARGable :

• 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 d’une construction de code définie par l’utilisateur et stockée dans la base de données (par exemple des procédures stockées, des fonctions définies par l’utilisateur ou des vues) aux tables système qui contiennent des informations sur les types de données utilisés dans les tables sous-jacentes (par exemple sys.columns (Transact-SQL)).

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).

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.

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.

É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

• Bonnes pratiques relatives au Magasin des requêtes
• Tables à mémoire optimisée
• Fonctions définies par l'utilisateur
• Variables de table et estimations de lignes - Partie 1
• Variables de table et estimations de lignes - Partie 2
• Mise en cache et réutilisation du plan d’exécution