DatenbankoptimierungsratgeberDatabase Engine Tuning Advisor

Dieses Thema gilt für: JaSQL ServerkeineAzure SQL-DatenbankkeineAzure SQL Data Warehouse keine Parallel DatawarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse Der MicrosoftMicrosoft-Datenbankoptimierungsratgeber (DTA) analysiert Datenbanken und gibt Empfehlungen zum Optimieren der Abfrageleistung. The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. Mit dem Datenbankoptimierungsratgeber können Sie einen optimalen Satz von Indizes, indizierten Sichten oder Tabellenpartitionen auswählen und erstellen, auch wenn Sie nicht über detaillierte Kenntnisse bezüglich der Datenbankstruktur oder der internen Mechanismen von SQL ServerSQL Serververfügen.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. Mit dem DTA können Sie die folgenden Aufgaben ausführen:Using the DTA, you can perform the following tasks.

  • Beheben der Leistungsprobleme einer bestimmten ProblemabfrageTroubleshoot the performance of a specific problem query

  • Optimieren eines großen Satzes von Abfragen auf mehreren DatenbankenTune a large set of queries across one or more databases

  • Ausführen einer explorativen Was-wäre-wenn-Analyse der potenziellen Änderungen des physischen EntwurfsPerform an exploratory what-if analysis of potential physical design changes

  • Verwalten des SpeicherplatzesManage storage space

Vorteile des Datenbankoptimierungsratgebers:Database Engine Tuning Advisor Benefits

Die Optimierung der Abfrageleistung kann sich ohne umfassende Kenntnisse bzgl. der Datenbankstruktur und der für die Datenbank ausgeführten Abfragen schwierig gestalten.Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. Der Datenbankoptimierungsratgeber kann diese Aufgabe durch Analysieren des aktuellen Abfrageplancache oder einer Arbeitsauslastung der erstellten Transact-SQLTransact-SQL -Abfragen und Empfehlen eines entsprechenden physischen Entwurfs erleichtern.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. Für erfahrenere Datenbankadministratoren stellt DTA einen leistungsstarken Mechanismus zum Ausführen explorativer Was-wäre-wenn-Analysen verschiedener Alternativen physischer Entwürfe bereit.For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. Der DTA kann die folgenden Informationen bereitstellen:The DTA can provide the following information.

  • Empfehlen der besten Mischung aus Rowstore- und Columnstore-Indizes für Datenbanken mithilfe des Abfrageoptimierers zur Analyse von Abfragen in einer Arbeitsauslastung.Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • Empfehlen von ausgerichteten oder nicht ausgerichteten Partitionen für Datenbanken, auf die in einer Arbeitsauslastung verwiesen wird.Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • Empfehlen von indizierten Sichten für Datenbanken, auf die in einer Arbeitsauslastung verwiesen wird.Recommend indexed views for databases referenced in a workload.

  • Analysieren der Auswirkungen vorgeschlagener Änderungen, einschließlich Indexverwendung, Verteilung von Abfragen auf Tabellen und Leistung von Abfragen in der Arbeitsauslastung.Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • Empfehlen von Verfahren zur Optimierung der Datenbank für eine kleine Gruppe problematischer Abfragen.Recommend ways to tune the database for a small set of problem queries.

  • Ermöglichen der Anpassung der Empfehlungen durch die Angabe weiterer Optionen, wie z. B. Datenträgereinschränkungen.Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • Bereitstellen von Berichten, in denen die Auswirkungen der Implementierung von Empfehlungen für eine bestimmte Arbeitsauslastung zusammengefasst sind.Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • Berücksichtigen von Alternativen, bei denen Sie mögliche Entwurfsoptionen in Form von hypothetischen Konfigurationen liefern, die vom Datenbankoptimierungsratgeber bewertet werden sollen.Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • Optimieren von Arbeitsauslastungen aus einer Vielzahl von Quellen, wie SQL Server-Abfragespeicher, Plancache, SQL Server Profiler-Ablaufverfolgungsdatei oder -tabelle oder SQL-Datei.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.

Der Datenbankoptimierungsratgeber ist für die folgenden Typen von Abfragearbeitsauslastungen ausgelegt:The Database Engine Tuning Advisor is designed to handle the following types of query workloads.

  • Nur OLTP-Abfragen (Online Transaction Processing, Onlinetransaktionsverarbeitung)Online transaction processing (OLTP) queries only

  • Nur OLAP-Abfragen (Online Analytical Processing, analytische Onlineverarbeitung)Online analytical processing (OLAP) queries only

  • Gemischte OLTP- und OLAP-AbfragenMixed OLTP and OLAP queries

  • Abfrageintensive Arbeitsauslastungen (mehr Abfragen als Datenänderungen)Query-heavy workloads (more queries than data modifications)

  • Updateintensive Arbeitsauslastungen (mehr Datenänderungen als Abfragen)Update-heavy workloads (more data modifications than queries)

DTA-Komponenten und -KonzepteDTA Components and Concepts

Grafische Benutzeroberfläche des DatenbankoptimierungsratgebersDatabase Engine Tuning Advisor Graphical User Interface
Eine benutzerfreundliche Schnittstelle, über die Sie die Arbeitsauslastung angeben und verschiedene Optimierungsoptionen aktivieren können.An easy-to-use interface in which you can specify the workload and select various tuning options.

dta -Hilfsprogrammdta Utility
Die Befehlszeilenversion des Datenbankoptimierungsratgebers.The command prompt version of Database Engine Tuning Advisor. Mit dem Hilfsprogramm dta soll es Ihnen ermöglicht werden, die Funktionalität des Datenbankoptimierungsratgebers in Anwendungen und Skripts zu verwenden.The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

Arbeitsauslastungworkload
Eine Transact-SQL-Skriptdatei, Ablaufverfolgungsdatei oder Ablaufverfolgungstabelle, die eine repräsentative Arbeitsauslastung für die zu optimierenden Datenbanken enthält.A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. Ab SQL Server 2012SQL Server 2012können Sie den Plancache als Arbeitsauslastung angeben.Beginning with SQL Server 2012SQL Server 2012, you can specify the plan cache as the workload. Ab SQL Server 2016SQL Server 2016 können Sie den Abfragespeicher als Arbeitsauslastung angeben.Beginning with with SQL Server 2016SQL Server 2016, you can specify the Query Store as the workload.

XML-EingabedateiXML input file
Eine Datei im XML-Format, mit der der Datenbankoptimierungsratgeber Arbeitsauslastungen optimieren kann.An XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. Die XML-Eingabedatei unterstützt erweiterte Optimierungsoptionen, die weder über die GUI noch im Hilfsprogramm dta verfügbar sind.The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

EinschränkungenLimitations and Restrictions

Für den Datenbankoptimierungsratgeber gelten die folgenden Einschränkungen:The Database Engine Tuning Advisor has the following limitations and restrictions.

  • Er kann keine eindeutigen Indizes bzw. Indizes, die PRIMARY KEY- oder UNIQUE-Einschränkungen erzwingen, hinzufügen oder löschen.It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • Er kann keine Datenbank analysieren, die auf den Einzelbenutzermodus festgelegt ist.It cannot analyze a database that is set to single-user mode.

  • Wenn der angegebene maximale Datenträgerspeicher für Optimierungsempfehlungen den tatsächlichen verfügbaren Speicherplatz überschreitet, verwendet der Datenbankoptimierungsratgeber den von Ihnen angegebenen Wert.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. Wenn Sie jedoch das Empfehlungsskript ausführen, um die Empfehlungen zu implementieren, erzeugt das Skript möglicherweise einen Fehler, wenn nicht vorher zusätzlicher Datenträgerspeicher hinzugefügt wird.However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. Sie können den maximalen Datenträgerspeicher über die Option -B des dta -Hilfsprogramms angeben oder indem Sie einen Wert in das Dialogfeld Erweiterte Optimierungsoptionen eingeben.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.

  • Aus Sicherheitsgründen kann der Datenbankoptimierungsratgeber eine Arbeitsauslastung in einer Ablaufverfolgungstabelle, die sich auf einem Remoteserver befindet, nicht optimieren.For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. Zum Umgehen dieser Einschränkung können Sie eine Ablaufverfolgungsdatei anstelle einer Ablaufverfolgungstabelle verwenden oder die Ablaufverfolgungstabelle auf den Remoteserver kopieren.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.

  • Wenn Sie Einschränkungen festlegen, indem Sie z.B. (über die Option -B oder das Dialogfeld Erweiterte Optimierungsoptionen ) den maximalen Datenträgerspeicher für Optimierungsempfehlungen angeben, ist der Datenbankoptimierungsratgeber u.U. gezwungen, bestimmte vorhandene Indizes zu löschen.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. In diesem Fall enthält die Empfehlung des Datenbankoptimierungsratgebers möglicherweise eine negative erwartete Verbesserung.In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • Wenn Sie eine Einschränkung hinsichtlich der Optimierungszeit angeben (indem Sie die Option -A des dta -Hilfsprogramms verwenden oder das Kontrollkästchen Optimierungszeit begrenzen auf der Registerkarte Optimierungsoptionen aktivieren), überschreitet der Datenbankoptimierungsratgeber möglicherweise das Zeitlimit für eine akkurate erwartete Verbesserung, und die Analyse gibt einen Bericht für den bislang verarbeiteten Teil der Arbeitsauslastung aus.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.

  • In den folgenden Situationen erstellt der Datenbankoptimierungsratgeber möglicherweise keine Empfehlungen:Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. Die Tabelle, die optimiert werden soll, umfasst weniger als 10 Datenseiten.The table being tuned contains less than 10 data pages.

    2. Die empfohlenen Indizes würden gegenüber dem aktuellen physischen Datenbankentwurf nicht genügend Verbesserungen bei der Abfrageleistung bieten.The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    3. Der Benutzer, der den Datenbankoptimierungsratgeber ausführt, ist kein Mitglied der Datenbankrolle db_owner oder der festen Serverrolle 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. Die Abfragen in der Arbeitsauslastung werden im Sicherheitskontext des Benutzers analysiert, der den Datenbankoptimierungsratgeber ausführt.The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. Der Benutzer muss ein Mitglied der Datenbankrolle db_owner sein.The user must be a member of the db_owner database role.

  • Der Datenbankoptimierungsratgeber speichert Daten zu Optimierungssitzungen und andere Informationen in der msdb -Datenbank.Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Wenn an der msdb -Datenbank Änderungen vorgenommen werden, besteht das Risiko, dass Optimierungssitzungsdaten verloren gehen.If changes are made to the msdb database you may risk losing tuning session data. Um dieses Risiko zu umgehen, müssen Sie für die msdb -Datenbank eine geeignete Sicherungsstrategie implementieren.To eliminate this risk, implement an appropriate backup strategy for the msdb database.

LeistungsaspektePerformance Considerations

Der Datenbankmodul-Optimierungsratgeber kann bei der Analyse signifikante Prozessor- und Arbeitsspeicherressourcen belegen.Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. Um zu vermeiden, dass die Leistung des Produktionsservers beeinträchtigt wird, wenden Sie eine der folgenden Strategien an:To avoid slowing down your production server, follow one of these strategies:

  • Optimieren Sie die Datenbanken, wenn der Server frei ist.Tune your databases when your server is free. Der Datenbankoptimierungsratgeber kann sich auf Wartungstasks auswirken.Database Engine Tuning Advisor can affect maintenance task performance.

  • Verwenden Sie die Funktion für Testserver und Produktionsserver.Use the test server/production server feature. Weitere Informationen finden Sie unter Reduzieren der Optimierungsauslastung des Produktionsservers.For more information, see Reduce the Production Server Tuning Load.

  • Geben Sie nur die Strukturen für den physischen Datenbankentwurf an, die der Datenbankoptimierungsratgeber analysieren soll.Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. Der Datenbankoptimierungsratgeber stellt zahlreiche Optionen zur Verfügung, gibt jedoch nur die wirklich erforderlichen an.Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Abhängigkeit von der erweiterten gespeicherten Prozedur xp_msverDependency on xp_msver Extended Stored Procedure

Der Datenbankoptimierungsratgeber ist von der erweiterten gespeicherten Prozedur xp_msver abhängig, um vollständige Funktionalität bereitzustellen.Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. Diese erweiterte gespeicherte Prozedur wird standardmäßig aktiviert.This extended stored procedure is turned on by default. Der Datenbankoptimierungsratgeber verwendet diese erweiterte gespeicherte Prozedur, um die Anzahl der Prozessoren und den verfügbaren Speicher auf dem Computer abzurufen, auf dem sich die zu optimierende Datenbank befindet.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. Wenn xp_msver nicht verfügbar ist, übernimmt der Datenbankoptimierungsratgeber die Hardwaremerkmale des Computers, auf dem er ausgeführt wird.If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. Wenn die Hardwaremerkmale des Computers, auf dem der Datenbankmodul-Optimierungsratgeber ausgeführt wird, nicht zur Verfügung stehen, geht der Ratgeber von einem Prozessor und 1024 MB (Megabyte) Speicher aus.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.

Diese Abhängigkeit hat Auswirkungen auf die Partitionierungsempfehlungen, da die Anzahl der empfohlenen Partitionen von diesen beiden Werten (Anzahl der Prozessoren und verfügbarer Speicher) abhängt.This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). Diese Abhängigkeit hat auch Auswirkungen auf die Optimierungsergebnisse, wenn Sie einen Testserver verwenden, um Ihren Produktionsserver zu optimieren.The dependency also affects your tuning results when you use a test server to tune your production server. In diesem Szenario verwendet der Datenbankoptimierungsratgeber xp_msver , um Hardwareeigenschaften vom Produktionsserver abzurufen.In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. Nach dem Optimieren der Arbeitsauslastung auf dem Testserver verwendet der Datenbankoptimierungsratgeber diese Hardwareeigenschaften dazu, eine Empfehlung zu generieren.After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. Weitere Informationen finden Sie unter xp_msver (Transact-SQL).For more information, see xp_msver (Transact-SQL).

Tasks des DatenbankoptimierungsratgebersDatabase Engine Tuning Advisor Tasks

In der folgenden Tabelle sind allgemeine Datenbankoptimierungsratgeber-Aufgaben und die Themen aufgeführt, in denen sie beschrieben werden.The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

Datenbankoptimierungsratgeber-AufgabeDatabase Engine Tuning Advisor Task ThemaTopic
Initialisieren und Starten des DatenbankoptimierungsratgebersInitialize and start the Database Engine Tuning Advisor.

Erstellen einer Arbeitsauslastung durch Angeben des Plancache, Erstellen eines Skripts oder Generieren einer Ablaufverfolgungsdatei oder AblaufverfolgungstabelleCreate a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

Optimieren einer Datenbank mithilfe der grafischen Benutzeroberfläche des DatenbankoptimierungsratgebersTune a database by using the Database Engine Tuning Advisor graphical user interface tool.

Erstellen von XML-Eingabedateien zum Optimieren von ArbeitsauslastungenCreate XML input files to tune workloads.

Anzeigen von Beschreibungen für die Benutzeroberflächenoptionen des DatenbankoptimierungsratgebersView descriptions of the Database Engine Tuning Advisor user interface options.
Starten und Verwenden des DatenbankoptimierungsratgebersStart and Use the Database Engine Tuning Advisor
Anzeigen der Ergebnisse des DatenbankoptimierungsvorgangsView the results of the database tuning operation.

Auswählen und Implementieren von OptimierungsempfehlungenSelect and implement tuning recommendations.

Durchführen einer Was-wäre-wenn-Analyse für die ArbeitsauslastungPerform what-if exploratory analysis against the workload.

Überprüfen vorhandener Optimierungssitzungen, Klonen von Sitzungen auf Grundlage vorhandener SitzungenReview existing tuning sessions, clone sessions based on existing ones
oder Bearbeiten vorhandener Optimierungsempfehlungen zur weiteren Auswertung oder Implementierungor edit existing tuning recommendations for further evaluation or implementation.

Anzeigen von Beschreibungen für die Benutzeroberflächenoptionen des DatenbankoptimierungsratgebersView descriptions of the Database Engine Tuning Advisor user interface options.
Anzeigen und Verwenden der Ausgabe des DatenbankoptimierungsratgebersView and Work with the Output from the Database Engine Tuning Advisor