Assistant Paramétrage duMoteur de base de données

Microsoft Database Engine Tuning Advisor (DTA) analyse les bases de données et émet des recommandations que vous pouvez utiliser pour optimiser les performances des requêtes. Vous pouvez utiliser l’Assistant Paramétrage du moteur de base de données pour sélectionner et créer un ensemble optimal d’index, de vues indexées ou de partitions de tables sans avoir une connaissance approfondie de la structure de base de données ou des internes de SQL Server. À l’aide de l’assistant DTA, vous pouvez effectuer les tâches suivantes.

• Évaluer les performances d'une requête problématique spécifique

• Paramétrer un grand ensemble de requêtes sur une ou plusieurs bases de données

• Effectuer une analyse exploratoire hypothétique des modifications de conception physique potentielles

• Gérer l’espace de stockage

Avantages de l’Assistant Paramétrage du moteur de base de données

L’optimisation des performances des requêtes peut être difficile sans comprendre complètement la structure de base de données et les requêtes exécutées sur la base de données. L’assistant de paramétrage du moteur de base de données peut faciliter cette tâche en analysant le cache actuel du plan de requête ou en analysant une charge de travail composée de Transact-SQL requêtes que vous avez créées, et en recommandant une conception physique appropriée. Pour les administrateurs de base de données plus avancés, DTA expose un mécanisme puissant pour effectuer une analyse exploratoire des scénarios de différentes alternatives de conception physique. Le DTA peut fournir les informations suivantes.

• Recommandez la meilleure combinaison d’index pour les bases de données à l’aide de l’optimiseur de requête pour analyser les requêtes dans une charge de travail.

• Recommandez des partitions alignées ou non alignées pour les bases de données référencées dans une charge de travail.

• Recommander des vues indexées pour les bases de données référencées dans une charge de travail.

• Analysez les effets des modifications proposées, notamment l’utilisation de l’index, la distribution des requêtes entre les tables et les performances des requêtes dans la charge de travail.

• Recommandez des façons de régler la base de données pour un petit ensemble de requêtes problématiques.

• Vous pouvez personnaliser la recommandation en spécifiant des options avancées telles que des contraintes d’espace disque.

• Fournissez des rapports qui résument les effets de l’implémentation des recommandations pour une charge de travail donnée.

L’Assistant Paramétrage du moteur de base de données est conçu pour gérer les types de charges de travail de requête suivants.

• Requêtes OLTP (Online Transaction Processing) uniquement

• Requêtes de traitement analytique en ligne (OLAP) uniquement

• Requêtes OLTP et OLAP mixtes

• Charges de travail gourmandes en requêtes (plus de requêtes que les modifications de données)

• Charges de travail axées sur les mises à jour (plus de modifications de données que de requêtes)

Composants et concepts de DTA

Interface graphique de l'Assistant de réglage du moteur de base de données
Interface facile à utiliser dans laquelle vous pouvez spécifier la charge de travail et sélectionner différentes options de réglage.

dta Utilité
Version en ligne de commande de l’Assistant de réglage du moteur de base de données. L'utilitaire dta est conçu pour permettre l'utilisation de l'Assistant Paramétrage du moteur de base de données dans des applications et des scripts.

charge de travail
Fichier de script Transact-SQL, fichier de trace ou table de trace qui contient une charge de travail représentative pour les bases de données que vous souhaitez paramétrer. À compter de SQL Server 2012, vous pouvez spécifier le cache de plan comme charge de travail.

Fichier d’entrée XML
Fichier au format XML que l’Assistant Paramétrage du moteur de base de données peut utiliser pour régler les charges de travail. Le fichier d’entrée XML prend en charge les options de réglage avancées qui ne sont pas disponibles dans l’utilitaire d’interface utilisateur graphique ou dta.

Limitations et restrictions

L’Assistant Paramétrage du moteur de base de données présente les limitations et restrictions suivantes.

• Il ne peut pas ajouter ou supprimer des index uniques ou des index qui appliquent des contraintes PRIMARY KEY ou UNIQUE.

• Il ne peut pas analyser une base de données définie sur le mode mono-utilisateur.

• Si vous spécifiez un espace disque maximal pour les recommandations de réglage qui dépassent l’espace disponible réel, l’Assistant Paramétrage du moteur de base de données utilise la valeur que vous spécifiez. Toutefois, lorsque vous exécutez le script de recommandation pour l’implémenter, le script peut échouer si plus d’espace disque n’est pas ajouté en premier. L’espace disque maximal peut être spécifié avec l’option -B de l’utilitaire dta ou en entrant une valeur dans la boîte de dialogue Options de réglage avancé .

• Pour des raisons de sécurité, l’Assistant Paramétrage du moteur de base de données ne peut pas régler une charge de travail dans une table de trace qui réside sur un serveur distant. Pour contourner cette limitation, vous pouvez utiliser un fichier de trace au lieu d’une table de trace ou copier la table de trace sur le serveur distant.

• Lorsque vous imposez des contraintes, telles que celles imposées lorsque vous spécifiez un espace disque maximal pour les recommandations de réglage (à l’aide de l’option -B ou de la boîte de dialogue Options de paramétrage avancées ), l’Assistant Paramétrage du moteur de base de données peut être obligé de supprimer certains index existants. Dans ce cas, la recommandation de l’Assistant Paramétrage du moteur de base de données résultante peut entraîner une amélioration négative attendue.

• Lorsque vous spécifiez une contrainte pour limiter le temps de réglage (à l’aide de l’option -A avec l’utilitaire dta ou en vérifiant limiter le temps de réglage sous l’onglet Options de paramétrage ), l’Assistant Paramétrage du moteur de base de données peut dépasser cette limite de temps pour produire une amélioration attendue précise et les rapports d’analyse pour toute partie de la charge de travail a été consommée jusqu’à présent.

• L’Assistant Paramétrage du moteur de base de données peut ne pas faire de recommandations dans les circonstances suivantes :

  1. La table en cours d’optimisation contient moins de 10 pages de données.

  2. Les index recommandés n’offrent pas suffisamment d’amélioration des performances des requêtes par rapport à la conception actuelle de la base de données physique.

  3. L’utilisateur qui exécute l’Assistant Paramétrage du moteur de base de données n’est pas membre du db_owner rôle de base de données ou du sysadmin rôle serveur fixe. Les requêtes de la charge de travail sont analysées dans le contexte de sécurité de l’utilisateur qui exécute l’Assistant Paramétrage du moteur de base de données. L’utilisateur doit être membre du db_owner rôle de base de données.

• L’Assistant d'Optimisation du moteur de base de données stocke les données de session d'optimisation et d’autres informations dans la msdb base de données. Si des modifications sont apportées à la msdb base de données, vous risquez de perdre les données de session de paramétrage. Pour éliminer ce risque, implémentez une stratégie de sauvegarde appropriée pour la msdb base de données.

Considérations relatives aux performances

L’Assistant Paramétrage du moteur de base de données peut consommer des ressources de processeur et de mémoire importantes pendant l’analyse. Pour éviter de ralentir votre serveur de production, suivez l’une des stratégies suivantes :

• Paramétrez vos bases de données lorsque votre serveur est gratuit. L’Assistant d'optimisation du moteur de base de données peut affecter les performances des tâches de maintenance.

• Utilisez la fonctionnalité serveur de test/serveur de production. Pour plus d’informations, consultez Réduire la charge de paramétrage du serveur de production.

• Spécifiez uniquement les structures de conception de base de données physiques que vous souhaitez que l’Assistant Paramétrage du moteur de base de données analyse. L’Assistant Paramétrage du moteur de base de données fournit de nombreuses options, mais spécifie uniquement celles nécessaires.

Dépendance sur la procédure stockée intégrée xp_msver

L'Assistant de réglage du moteur de base de données dépend de la procédure stockée étendue xp_msver pour assurer une fonctionnalité complète. Cette procédure stockée étendue est activée par défaut. L’Assistant Paramétrage du moteur de base de données utilise cette procédure stockée étendue pour extraire le nombre de processeurs et la mémoire disponible sur l’ordinateur sur lequel réside la base de données que vous paramétrez. Si xp_msver n’est pas disponible, l’Assistant Paramétrage du moteur de base de données suppose les caractéristiques matérielles de l’ordinateur sur lequel l’Assistant Paramétrage du moteur de base de données est en cours d’exécution. Si les caractéristiques matérielles de l’ordinateur sur lequel l’Assistant Paramétrage du moteur de base de données s’exécute ne sont pas disponibles, un processeur et 1024 mégaoctets (MB) de mémoire sont supposés.

Cette dépendance affecte les recommandations de partitionnement, car le nombre de partitions recommandées dépend de ces deux valeurs (nombre de processeurs et de mémoire disponible). La dépendance affecte également vos résultats de paramétrage lorsque vous utilisez un serveur de test pour régler votre serveur de production. Dans ce scénario, l’Assistant Paramétrage du moteur de base de données utilise xp_msver pour extraire les propriétés matérielles du serveur de production. Après avoir paramétré la charge de travail sur le serveur de test, l’Assistant Paramétrage du moteur de base de données utilise ces propriétés matérielles pour générer une recommandation. Pour plus d’informations, consultez xp_msver (Transact-SQL).

Tâches de l’Assistant Paramétrage du moteur de base de données

Le tableau suivant répertorie les tâches courantes de l’Assistant Paramétrage du moteur de base de données et les rubriques qui décrivent comment les effectuer.