Déterminer si un tableau ou une procédure stockée doit être déplacée vers l'OLTP en mémoire
Le collecteur de performances de transaction dans SQL Server Management Studio vous aide à déterminer si In-Memory OLTP améliore les performances de votre application de base de données. Le rapport d'analyse des performances de transaction indique également le volume de travail nécessaire pour activer l'OLTP en mémoire dans votre application. Après avoir identifié une table sur disque pour la fonctionnalité OLTP en mémoire, utilisez le Conseiller d’optimisation de la mémoirepour migrer la table. De même, le Conseiller de compilation native vous aide à déplacer une procédure stockée vers une procédure stockée compilée en mode natif.
Cette rubrique explique comment :
configurer l'entrepôt de données de gestion ;
configurer la collecte de données ;
générer des rapports d'analyse des performances de transaction pour identifier les tables et les procédures stockées ayant un impact sur les performances.
Pour plus d’informations sur les méthodologies de migration, consultez OLTP en mémoire - Modèles de charge de travail courants et considérations relatives à la migration.
Le collecteur de performances et les rapports d'évaluation des performances de transaction vous aident à effectuer les tâches suivantes :
Analyse de votre charge de travail afin de déterminer si l'OLTP en mémoire va améliorer les performances. Le collecteur de performances de transaction collecte et évalue les caractéristiques des performances de votre charge de travail. . Le rapport d'analyse des performances de transaction recommande ensuite les tables et les procédures stockées qui tireront le plus parti de la migration vers l'OLTP en mémoire.
Aide pour la planification et l'exécution de votre migration vers l'OLTP en mémoire. Le chemin de migration d'une table sur disque vers une table mémoire optimisée peut prendre beaucoup de temps. Le Conseiller d'optimisation de la mémoire vous aide à identifier les incompatibilités dans votre table que vous devez supprimer avant de déplacer la table vers l'OLTP en mémoire. Le gestionnaire d'optimisation de la mémoire vous aide également à comprendre l'impact que la migration d'une table vers une table mémoire optimisée aura sur votre application.
Déterminez si votre application va tirer parti de l'OLTP en mémoire, lorsque vous souhaitez planifier votre migration vers l'OLTP en mémoire et lorsque vous travaillez pour migrer certaines de vos tables et procédures stockées vers l'OLTP en mémoire.
Important
Les performances d'un système de base de données dépendent de différents facteurs, tous ne pouvant pas être observés et mesurés par le collecteur de performances de transaction. Par conséquent, le rapport d'analyse des performances de transaction ne garantit pas que les gains de performances réels correspondront aux prédictions, si des prédictions sont faites.
Le collecteur de performances des transactions et la possibilité de générer un rapport d’analyse des performances des transactions sont installés lorsque vous sélectionnez Outils d’administration de base ou Outils d’administration avancés lorsque vous installez SQL Server 2019 (15.x).
Bonnes pratiques
Le flux de travail recommandé est illustré dans l'organigramme suivant. Les nœuds jaunes représentent les procédures facultatives :
Vous pouvez utiliser n’importe quelle méthode pour établir une base de référence de performances, y compris, mais sans s’y limiter, l’utilisation des journaux du compteur de performances ou du moniteur d’activité SQL Server. Les informations à utiliser pour déterminer vos performances de base et vos comparaisons sont les suivantes :
Consommation du processeur de SQL Server.
Consommation de mémoire des SQL Server.
Activité d’E/S de SQL Server.
Débit de l'instance lors du traitement des transactions.
Le collecteur de performances de transaction capture les données toutes les 15 minutes. Pour obtenir des résultats utilisables, exécutez le collecteur de performances de transaction pendant au moins une heure. Pour obtenir de meilleurs résultats, exécutez le collecteur de performances de transaction aussi longtemps que nécessaire pour capturer des données pour vos principaux scénarios. Générez un rapport d'évaluation des performances de transaction uniquement après avoir terminé de regrouper les données.
Configurez le collecteur de performances de transaction pour qu’il s’exécute sur votre SQL Server instance en production et collectez les données sur un SQL Server instance dans votre environnement de développement (test) pour garantir une surcharge minimale. Pour plus d’informations sur l’enregistrement de données dans une base de données de gestion Data Warehouse sur une SQL Server instance distante, consultez Configurer la collecte de données sur une instance de SQL Server distante.
Impacts sur les performances
Le collecteur de performances de transaction comprend deux jeux d'éléments de collecte de données :
Analyse de l'utilisation des tables
Analyse des procédures stockées
Les jeux d'éléments de collecte collectent des données de trois vues de gestion dynamique toutes les quinze minutes, puis téléchargent les données dans la base de données configurée pour faire office d'entrepôt de données de gestion. Le téléchargement des données collectées a un faible impact sur les performances.
Utiliser le collecteur de performances de transaction
Les étapes suivantes nécessitent SQL Server Management Studio dans SQL Server 2019 (15.x).
Important
Ne modifiez pas le schéma (par exemple, en ajoutant ou en supprimant des bases de données ou des tables) lors du profilage. Si vous modifiez le schéma d'une base de données pendant la collecte des données, la base de données peut ne pas être correctement incluse dans le rapport.
Configurer l'entrepôt de données de gestion
L'entrepôt de données de gestion doit être configuré pour utiliser le collecteur de performances de transaction.
La version de l’SQL Server instance sur laquelle vous allez collecter des données (profil) doit être la même version ou antérieure à la SQL Server où Data Warehouse de gestion est configuré.
Dans l’Explorateur d’objets, développez Gestion.
Cliquez avec le bouton droit sur Collecte de données, sélectionnez Tâches, puis Configure Management Data Warehouse. L’Assistant Configurer la gestion Data Warehouse commence.
Cliquez sur Suivant pour sélectionner la base de données qui fera office de Data Warehouse de gestion.
Cliquez sur Nouveau pour créer une base de données pour contenir les données de profil. Une fois la création de la base de données terminée, cliquez sur Suivant dans l’Assistant.
L'étape suivante de l'Assistant vous permet d'ajouter des utilisateurs et des noms de connexion. Vous pouvez mapper les noms de connexion aux appartenances aux rôles pour l'instance de l'entrepôt de données de gestion (MDW). Cela n'est pas obligatoire pour collecter des données sur l'instance locale. Si vous ne collectez pas de données depuis l'instance locale, vous pouvez accorder l'appartenance au rôle de base de données
mdw_adminau compte qui exécutera les transactions qui seront profilées. Quand vous avez terminé, cliquez sur Suivant.Vérifiez que SQL Server Agent est en cours d’exécution.
Dans l’écran suivant, cliquez sur Terminer pour quitter l’Assistant.
Configurer la collecte de données sur une instance de SQL Server locale
La collecte de données nécessite le démarrage de SQL Server Agent. Vous ne devez configurer qu'un collecteur de données sur un serveur.
Un collecteur de données peut être configuré sur une version SQL Server 2012 ou ultérieure de SQL Server.
Pour configurer la collecte de données à télécharger vers une base de données de l'entrepôt de données de gestion sur la même instance, procédez comme suit :
Dans Explorateur d'objets, développez Gestion.
Cliquez avec le bouton droit sur Collecte de données, sélectionnez Tâches, puis Configurer la collecte de données. L’Assistant Configurer la collecte de données commence.
Cliquez sur Suivant pour sélectionner la base de données qui collectera les données de profil.
Sélectionnez le SQL Server instance actuel et une base de données Data Warehouse de gestion sur cette instance.
Dans la zone intitulée Sélectionner les ensembles de collecteurs de données que vous souhaitez activer, sélectionnez Ensembles de collection de performances de transaction. Quand vous avez terminé, cliquez sur Suivant.
Vérifiez les sélections. Cliquez sur Retour pour modifier les paramètres. Lorsque vous avez terminé, cliquez sur Terminer .
Configurer la collecte de données sur une instance distante de SQL Server
La collecte de données nécessite SQL Server Agent de démarrer sur le instance qui collectera les données.
Un collecteur de données peut être configuré sur une version SQL Server 2012 ou ultérieure de SQL Server.
Vous avez besoin d’un proxy SQL Server Agent établi avec les informations d’identification appropriées pour qu’un collecteur de données charge les données dans une base de données de gestion Data Warehouse sur un instance différent de l’emplacement où les transactions seront profilées. Pour activer un proxy SQL Server Agent, vous devez d’abord établir des informations d’identification avec une connexion avec un domaine. La connexion activée pour le domaine doit être membre du groupe mdw_admin pour la base de données de l'entrepôt de données de gestion. Consultez Guide pratique pour créer des informations d’identification (SQL Server Management Studio) pour plus d’informations sur la création d’informations d’identification.
Pour configurer la collecte de données à télécharger vers une base de données de l'entrepôt de données de gestion sur une autre instance, procédez comme suit :
Sur le instance qui contient les objets sur disque que vous souhaitez migrer vers In-Memory OLTP, développez le nœud Gestion dans Explorateur d'objets.
Cliquez avec le bouton droit sur Collecte de données , sélectionnez Tâches , puis Configurer la collecte de données. L’Assistant Configurer la collecte de données commence.
Cliquez sur Suivant pour sélectionner la base de données qui collectera les données de profil.
Assurez-vous qu’une base de données Data Warehouse de gestion existe sur l’autre SQL Server instance.
Sélectionnez un autre SQL Server instance et une base de données Data Warehouse de gestion sur cette instance.
La version de l’SQL Server instance sur laquelle vous allez collecter des données (profil) doit être la même version ou antérieure à la SQL Server où Data Warehouse de gestion est configuré.
Dans la zone intitulée Sélectionner les ensembles de collecteurs de données que vous souhaitez activer, sélectionnez Ensembles de collection de performances de transaction.
Sélectionnez Utiliser un proxy SQL Server Agent pour les chargements distants.
Quand vous avez terminé, cliquez sur Suivant.
Sélectionnez le proxy.
Si vous souhaitez créer un proxy SQL Server Agent,
Cliquez sur Nouveau pour afficher la boîte de dialogue Nouveau compte proxy .
Dans la boîte de dialogue Nouveau compte proxy , entrez le nom du proxy, sélectionnez les informations d’identification, puis entrez éventuellement une description. Cliquez ensuite sur Principaux.
Cliquez sur Ajouter et sélectionnez rôle Msdb .
Sélectionnez
dc_proxyet cliquez sur OK. Cliquez à nouveau sur OK.
Une fois le proxy approprié sélectionné, cliquez sur Suivant.
Pour configurer des ensembles de collectes de systèmes, case activée Ensembles de regroupements système, puis cliquez sur Suivant.
Vérifiez les sélections. Cliquez sur Précédent pour modifier les paramètres. Clicck Finish lorsque vous avez terminé.
Les jeux d'éléments de collecte de données doivent maintenant être configurés et exécutés sur votre instance.
Générer des rapports
Vous pouvez générer des rapports d’analyse des performances des transactions en cliquant avec le bouton droit sur la base de données du Data Warehouse de gestion et en sélectionnant Rapports, gestion Data Warehouse, puis Vue d’ensemble de l’analyse des performances des transactions.
Le rapport collecte des informations sur toutes les bases de données utilisateur sur le serveur de charge de travail. Si la base de données de l'entrepôt de données de gestion (MDW) se trouve sur l'ordinateur local, la base de données MDW s'affiche dans le rapport.
Une procédure stockée avec un rapport élevé temps UC/temps écoulé est un candidat pour la migration. Le rapport affiche toutes les références de table, car les procédures stockées compilées en mode natif ne peuvent référencer que les tables mémoire optimisées, ce qui augmente le coût de migration.
Le rapport détaillé d'une table comprend trois sections :
Section des statistiques d'analyse
Cette section comprend une seule table contenant les statistiques collectées à propos des analyses sur la table de base de données. Les colonnes sont les suivantes :
Pourcentage du total des accès. Pourcentage des analyses et des recherches sur cette table, par rapport à l'activité de la base de données totale. Plus ce pourcentage est élevé, plus la table est sollicitée par rapport aux autres tables de la base de données.
Statistiques de recherche/Statistiques d'analyse de plage. Cette colonne indique le nombre de recherches de point et d'analyses de plage (analyses d'index et de table) effectuées sur la table pendant le profilage. La moyenne par transaction est une estimation.
Gain d'interopérabilité et gain natif. Ces colonnes estiment les avantages au niveau des performances que la recherche de point et l'analyse de plage pourraient offrir si la table était convertie en table mémoire optimisée.
Section des statistiques de contention
Cette section comprend un tableau indiquant la contention sur la table de base de données. Pour plus d’informations sur les verrous et verrous de base de données, consultez Architecture de verrouillage. Les colonnes sont les suivantes :
Pourcentage du total des attentes. Pourcentage des attentes attribuables à un verrou interne et un verrou sur cette table de base de données par rapport à l'activité de la base de données. Plus ce pourcentage est élevé, plus la table est sollicitée par rapport aux autres tables de la base de données.
Statistiques des verrous internes. Ces colonnes indiquent le nombre d'attentes attribuables à un verrou interne pour les requêtes impliquant cette table. Pour plus d’informations sur les verrous, consultez Verrouillage. Plus ce nombre est élevé, plus il y a de contention de verrous internes sur la table.
Statistiques des verrous. Ce groupe de colonnes indique le nombre d'acquisitions et d'attentes attribuables à des verrous de page pour les requêtes de cette table. Pour plus d’informations sur les verrous, consultez Présentation du verrouillage dans SQL Server. Plus le nombre d'attentes est élevé, plus il y a de contention de verrous sur la table.
Section des difficultés de migration
Cette section comprend un tableau des difficultés rencontrées pour convertir cette table de base de données en une table mémoire optimisée. Plus le taux de difficultés est élevé, plus il est difficile de convertir la table. Pour plus d’informations sur la conversion de cette table de base de données, utilisez l’Assistant Optimisation de la mémoire.
Les statistiques d’analyse et de contention sur le rapport de détails de table sont collectées et agrégées à partir de sys.dm_db_index_operational_stats (Transact-SQL).
Le rapport détaillé d'une procédure stockée comprend deux sections :
Section des statistiques d'exécution
Cette section comprend un tableau contenant les statistiques collectées à propos des exécutions de la procédure stockée. Les colonnes sont les suivantes :
Heure de mise en cache. Heure à laquelle ce plan d'exécution est mis en cache. Si la procédure stockée supprime le cache du plan et recommence, il y aura des heures pour chaque cache.
Temps processeur total. Temps processeur total consommé par la procédure stockée pendant le profilage. Plus ce nombre est élevé, plus la procédure stockée utilise de temps de processeur.
Durée totale d'exécution. Durée d'exécution totale de la procédure stockée pendant le profilage. Plus la différence entre ce chiffre et le temps processeur est élevée, moins le temps processeur est utilisé efficacement par la procédure stockée.
Total des absences dans le cache. Nombre d’absences dans le cache (lectures depuis un stockage physique) causées par les exécutions de la procédure stockée pendant le profilage.
Nombre d'exécutions. Nombre de fois que la procédure stockée est exécutée pendant le profilage.
Section des références de table
Cette section comprend un tableau répertoriant les tables auxquelles cette procédure stockée se réfère. Avant de convertir la procédure stockée en une procédure stockée compilée en mode natif, toutes ces tables doivent être converties en tables mémoire optimisées, et résider sur le même serveur et la même base de données.
Les statistiques d’exécution sur le rapport de détails de procédure stockée sont collectées et agrégées à partir de sys.dm_exec_procedure_stats (Transact-SQL). Les références sont obtenues à partir de sys.sql_expression_dependencies (Transact-SQL).
Pour plus d’informations sur la conversion d’une procédure stockée en procédure stockée compilée en mode natif, utilisez le Conseiller de compilation native.