Assistant Paramétrage du moteur de base de donnéesDatabase Engine Tuning Advisor

L'Assistant Paramétrage du moteur de base de données MicrosoftMicrosoft analyse les bases de données et émet des recommandations que vous pouvez utiliser pour optimiser les performances des requêtes.The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. L'Assistant Paramétrage du moteur de base de données vous permet de sélectionner et de créer un ensemble optimal d'index, de vues indexées ou de partitions de table sans devoir être un expert familiarisé avec la structure de la base de données ou les mécanismes internes de SQL ServerSQL Server.You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL ServerSQL Server. Vous pouvez effectuer les tâches suivantes à l'aide de l'Assistant Paramétrage du moteur de base de données :Using the DTA, you can perform the following tasks.

  • Résoudre les problèmes liés aux performances d'une requête de problème spécifiqueTroubleshoot the performance of a specific problem query

  • Paramétrer un grand nombre de requêtes sur une ou plusieurs bases de donnéesTune a large set of queries across one or more databases

  • Exécuter une analyse de simulation exploratoire des modifications de conception physique potentiellesPerform an exploratory what-if analysis of potential physical design changes

  • Gérer l'espace de stockageManage storage space

Avantages de l'Assistant Paramétrage du moteur de base de donnéesDatabase Engine Tuning Advisor Benefits

L'optimisation des performances des requêtes peut être difficile sans une compréhension complète de la structure de la base de données et des requêtes exécutées sur la base de données.Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. L'Assistant Paramétrage du moteur de base de données peut simplifier cette tâche en analysant le cache du plan de requête actuel ou une charge de travail des requêtes Transact-SQLTransact-SQL que vous créez, puis en recommandant une conception physique appropriée.The Database Engine Tuning Advisor can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQLTransact-SQL queries that you create and recommending an appropriate physical design. Pour les administrateurs de base de données plus avancés, l'Assistant Paramétrage du moteur de base de données expose un mécanisme puissant pour effectuer une analyse de simulation exploratoire des différentes alternatives de conception physique.For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. L'Assistant Paramétrage du moteur de base de données peut fournir les informations suivantes :The DTA can provide the following information.

  • recommander la meilleure combinaison d’index columnstore pour les bases de données, en utilisant l’optimiseur de requête pour analyser des requêtes dans une charge de travail ;Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • recommander des partitions alignées ou non alignées pour les bases de données référencées dans une charge de travail ;Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • recommander des vues indexées pour les bases de données référencées dans une charge de travail ;Recommend indexed views for databases referenced in a workload.

  • analyser les effets des modifications suggérées, notamment l'utilisation des index, la distribution des requêtes au sein des tables et les performances des requêtes dans la charge de travail ;Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • recommander des solutions d'optimisation de la base de données pour un ensemble réduit de requêtes à problème ;Recommend ways to tune the database for a small set of problem queries.

  • vous permettre de personnaliser la recommandation en définissant des options avancées telles que les contraintes d'espace disque ;Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • fournir des rapports qui résument les effets de la mise en œuvre des recommandations pour une charge de travail donnée.Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • prendre en compte d'autres solutions dans lesquelles vous fournissez des choix de conception possibles sous forme de configurations hypothétiques que l'Assistant Paramétrage du moteur de base de données évalue.Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • paramétrer les charges de travail à partir de diverses sources, notamment le magasin de requêtes SQL Server, le cache du plan, une table ou un fichier de trace SQL Server Profiler, ou encore un fichier .SQL.Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

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êtes suivants :The Database Engine Tuning Advisor is designed to handle the following types of query workloads.

  • Requêtes de traitement transactionnel en ligne (OLTP) uniquementOnline transaction processing (OLTP) queries only

  • Requêtes de traitement analytique en ligne (OLAP) uniquementOnline analytical processing (OLAP) queries only

  • Requêtes mixtes OLTP et OLAPMixed OLTP and OLAP queries

  • Charges de travail à nombre élevé de requêtes (plus de requêtes que de modifications de données)Query-heavy workloads (more queries than data modifications)

  • Charges de travail à nombre élevé de mises à jour (plus de modifications de données que de requêtes)Update-heavy workloads (more data modifications than queries)

Composants et concepts liés à l'Assistant Paramétrage du moteur de base de donnéesDTA Components and Concepts

Interface utilisateur graphique de l'Assistant Paramétrage du moteur de base de donnéesDatabase Engine Tuning Advisor Graphical User Interface
Interface simple d'utilisation dans laquelle vous pouvez spécifier la charge de travail et sélectionner plusieurs options de paramétrage.An easy-to-use interface in which you can specify the workload and select various tuning options.

Utilitairedta dta Utility
Version d'invite de commandes de l'Assistant Paramétrage du moteur de base de données.The command prompt version of Database Engine Tuning Advisor. 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.The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

charge de travailworkload
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 à paramétrer.A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. À compter de SQL Server 2012SQL Server 2012, vous pouvez spécifier le cache du plan comme charge de travail.Beginning with SQL Server 2012SQL Server 2012, you can specify the plan cache as the workload. À compter de SQL Server 2016SQL Server 2016, vous pouvez spécifier le magasin de requêtes comme charge de travail.Beginning with with SQL Server 2016SQL Server 2016, you can specify the Query Store as the workload.

Fichier d'entrée XMLXML input file
Fichier formaté en XML que l'Assistant Paramétrage du moteur de base de données peut utiliser pour paramétrer les charges de travail.An XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. Le fichier d’entrée XML prend en charge les options avancées de paramétrage disponibles dans l’interface utilisateur graphique ou l’utilitaire dta .The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

Limitations et restrictionsLimitations and Restrictions

L'Assistant Paramétrage du moteur de base de données présente les limitations et restrictions suivantes :The Database Engine Tuning Advisor has the following limitations and restrictions.

  • Il ne peut pas ajouter ni supprimer des index uniques ou des index qui imposent des contraintes PRIMARY KEY ou UNIQUE.It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • Il ne peut pas analyser une base de données qui est définie en mode mono-utilisateur.It cannot analyze a database that is set to single-user mode.

  • Si, dans le cadre des recommandations pour le paramétrage, vous spécifiez un espace disque maximal supérieur à l'espace disponible réel, l'Assistant Paramétrage du moteur de base de données utilise la valeur que vous indiquez.If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. Toutefois, lorsque vous exécutez le script des recommandations pour les implémenter, il peut échouer si vous n'ajoutez pas d'abord davantage d'espace disque.However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. Vous pouvez spécifier l’espace disque maximal à l’aide de l’option -B de l’utilitaire dta ou en entrant une valeur dans la boîte de dialogue Options de paramétrage avancées .Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • Pour des raisons de sécurité, l'Assistant Paramétrage du moteur de base de données ne peut pas paramétrer une charge de travail dans une table de trace qui se trouve sur un serveur distant.For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. 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.To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

  • Quand vous appliquez des contraintes, telles que celles que vous imposez lors de la définition d’un espace disque maximal pour les recommandations pour le paramétrage (à 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 amené à supprimer certains index existants.When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. Dans ce cas, les recommandations obtenues de l'Assistant Paramétrage du moteur de base de données peuvent produire une détérioration au lieu de l'amélioration attendue.In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • Quand vous spécifiez une contrainte de limitation de la durée du paramétrage (à l’aide de l’option -A de l’utilitaire dta ou en activant l’option Limiter la durée du paramétrage sous l’onglet Options de paramétrage ), l’Assistant Paramétrage du moteur de base de données peut aller au-delà de cette limite pour produire une amélioration particulière attendue ainsi que les rapports d’analyse sur la partie de la charge de travail consommée jusqu’alors, quelle qu’elle soit.When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

  • L'Assistant Paramétrage du moteur de base de données peut ne pas faire de recommandations dans les circonstances suivantes :Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. La table en cours de paramétrage contient moins de 10 pages de données.The table being tuned contains less than 10 data pages.

    2. Les index recommandés n'apporteraient pas d'amélioration suffisante aux performances des requêtes par rapport à la conception actuelle de la base de données physique.The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    3. L’utilisateur qui exécute l’Assistant Paramétrage du moteur de base de données n’est pas membre du rôle de base de données db_owner ou du rôle de serveur fixe sysadmin .The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. Les requêtes présentes dans 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.The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. Cet utilisateur doit être membre du rôle de base de données db_owner .The user must be a member of the db_owner database role.

  • L’Assistant Paramétrage du moteur de base de données stocke les données de la session de paramétrage et les autres données dans la base de données msdb .Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Si des changements sont apportés à la base de données msdb , vous risquez de perdre les données de la session de paramétrage.If changes are made to the msdb database you may risk losing tuning session data. Pour éliminer ce risque, mettez en œuvre une stratégie de sauvegarde appropriée pour la base de données msdb .To eliminate this risk, implement an appropriate backup strategy for the msdb database.

Considérations relatives aux performancesPerformance Considerations

L'Assistant Paramétrage du moteur de base de données peut consommer des ressources processeur et mémoire significatives au cours de l'analyse.Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. Pour éviter de ralentir le serveur de production, adoptez l'une des stratégies suivantes :To avoid slowing down your production server, follow one of these strategies:

  • Paramétrez les bases de données lorsque le serveur est disponible.Tune your databases when your server is free. L'Assistant Paramétrage du moteur de base de données peut affecter les performances des tâches de maintenance.Database Engine Tuning Advisor can affect maintenance task performance.

  • Utilisez la fonctionnalité serveur de test/serveur de production.Use the test server/production server feature. Pour plus d’informations, consultez Réduire la charge de paramétrage du serveur de production.For more information, see Reduce the Production Server Tuning Load.

  • Spécifiez seulement les structures de conception de base de données physique qui doivent être analysées par l'Assistant Paramétrage du moteur de base de données.Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. L'Assistant Paramétrage du moteur de base de données offre de nombreuses options, mais il ne spécifie que celles qui sont nécessaires.Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Dépendance vis-à-vis de la procédure stockée étendue xp_msverDependency on xp_msver Extended Stored Procedure

Pour bénéficier de toutes ses fonctionnalités, l’Assistant Paramétrage du moteur de base de données fait appel à la procédure stockée étendue xp_msver .Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. Cette procédure stockée étendue est activée par défaut.This extended stored procedure is turned on by default. L'Assistant Paramétrage du moteur de base de données utilise cette procédure stockée étendue pour déterminer le nombre de processeurs et la quantité de mémoire disponibles sur l'ordinateur où réside la base de données que vous paramétrez.Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. Si xp_msver n’est pas disponible, l’Assistant Paramétrage du moteur de base de données définit de façon arbitraire les caractéristiques matérielles de l’ordinateur sur lequel s’exécute l’Assistant Paramétrage du moteur de base de données.If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. 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, l'ordinateur est supposé être équipé d'un seul processeur et de 1024 mégaoctets (Mo) de mémoire.If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

Cette dépendance présente un impact sur les recommandations en matière de partitionnement, car le nombre de partitions recommandées dépend de ces deux valeurs (nombre de processeurs et quantité de mémoire disponible).This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). Les résultats de votre paramétrage s'en trouvent également affectées lorsque vous paramétrez votre serveur de production par le biais d'un serveur de test.The dependency also affects your tuning results when you use a test server to tune your production server. Dans ce scénario, l’Assistant Paramétrage du moteur de base de données utilise xp_msver pour déterminer les propriétés matérielles du serveur de production.In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. 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 se base sur ces propriétés matérielles pour générer une recommandation.After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. Pour plus d’informations, consultez xp_msver (Transact-SQL).For more information, see xp_msver (Transact-SQL).

Tâches de l'Assistant Paramétrage du moteur de base de donnéesDatabase Engine Tuning Advisor Tasks

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 exécuter.The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

Tâche de l'Assistant Paramétrage du moteur de base de donnéesDatabase Engine Tuning Advisor Task RubriqueTopic
Initialiser et démarrer l'Assistant Paramétrage du moteur de base de données.Initialize and start the Database Engine Tuning Advisor.

Créer une charge de travail en spécifiant le cache du plan, en créant un script ou en générant un fichier de trace ou une table de trace.Create a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

Paramétrer une base de données à l'aide de l'outil d'interface utilisateur graphique de l'Assistant Paramétrage du moteur de base de données.Tune a database by using the Database Engine Tuning Advisor graphical user interface tool.

Créer des fichiers d'entrée XML pour paramétrer les charges de travail.Create XML input files to tune workloads.

Afficher les descriptions des options d'interface utilisateur de l'Assistant Paramétrage du moteur de base de données.View descriptions of the Database Engine Tuning Advisor user interface options.
Démarrer et utiliser l'Assistant Paramétrage du moteur de base de donnéesStart and Use the Database Engine Tuning Advisor
Afficher les résultats de l'opération de paramétrage de base de données.View the results of the database tuning operation.

Sélectionner et mettre en œuvre des recommandations de paramétrage.Select and implement tuning recommendations.

Réaliser une analyse de simulation exploratoire sur la charge de travail.Perform what-if exploratory analysis against the workload.

Examiner les sessions de paramétrage et les sessions de clonage en fonction des sessions existantesReview existing tuning sessions, clone sessions based on existing ones
ou modifier les recommandations de paramétrage existantes pour une évaluation ou une implémentation plus poussée.or edit existing tuning recommendations for further evaluation or implementation.

Afficher les descriptions des options d'interface utilisateur de l'Assistant Paramétrage du moteur de base de données.View descriptions of the Database Engine Tuning Advisor user interface options.
Afficher et utiliser la sortie de l'Assistant Paramétrage du moteur de base de donnéesView and Work with the Output from the Database Engine Tuning Advisor