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.
Remarque
Pour les migrations de SQL Server vers SQL Server, si ce problème existait dans SQL Server source, la migration telle quelle vers une version plus récente de SQL Server ne s'attaquerait pas à cette situation.
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
- Utilisez le conseil
RECOMPILE. Un plan est calculé chaque fois de manière adaptée à chaque valeur de paramètre. - 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. - 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.
- 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. - 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 siOPTION(RECOMPILE),WITH RECOMPILEouOPTIMIZE 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
- Utiliser le plan d’exécution graphique pour les références d’index manquantes.
- Indexez les suggestions générées par l’Assistant Paramétrage du moteur de base de données.
- Utilisez sys.dm_db_missing_index_details ou le Tableau de bord Performances SQL Server.
- 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.
Conseil
La Création d’index et les Informations d’index sont des exemples de scripts préexistants.
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 de SQL Server vers SQL Server, si ce problème existait dans SQL Server source, la migration telle quelle vers une version plus récente de SQL Server ne s'attaquerait pas à cette situation.
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_IMPLICITdans 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.975mais pasWHERE UnitPrice < 320 * 200 * 32. - Expressions utilisant des fonctions, telles que
WHERE ABS(ProductID) = 771ouWHERE UPPER(LastName) = 'Smith' - Chaînes commençant par un caractère générique, par exemple
WHERE LastName LIKE '%Smith'mais pasWHERE LastName LIKE 'Smith%'.
Étapes de résolution
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)).
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.
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 de SQL Server vers SQL Server, si ce problème existait dans SQL Server source, la migration telle quelle vers une version plus récente de SQL Server ne s'attaquerait pas à cette situation.
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.
Important
Dans la mesure où la table de sortie d’une fonction table à instructions multiples (MSTVF) n’est pas créée au moment de la compilation, l’optimiseur de requête SQL Server s’appuie sur des valeurs heuristiques et non des statistiques réelles pour déterminer les estimations de lignes. Même si les index sont ajoutés aux tables de base, cela n’est d’aucune aide. 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
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 ENDL’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 )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.