Erkunden von Datenbankwartungsprüfungen

  • 7 Minuten

Der Abfrageoptimierer verwendet statistische Daten aus den Indizes, um damit den optimalen Ausführungsplan zu erstellen.

In Azure SQL werden Wartungsaufgaben wie Sicherungen und Integritätsüberprüfungen automatisch durchgeführt. Obwohl automatische Aktualisierungen, die Ihre Statistiken auf dem neuesten Stand halten, möglicherweise ausreichend sind, ist dies manchmal zu wenig.

Fehlerfreie Indizes und Statistiken stellen sicher, dass die Leistung eines bestimmten Plans auch optimale Effizienz aufweist. Die Indexwartung sollte regelmäßig ausgeführt werden, da sich die Daten in Ihren Datenbanken im Laufe der Zeit ändern. Sie können Ihre Indexwartungsstrategie z. B. auf Basis der Häufigkeit der Datenänderungen anpassen.

Neuerstellen und Neusortieren

Indexfragmentierung tritt auf, wenn die logische Reihenfolge innerhalb der Indexseiten nicht mit der physischen Reihenfolge übereinstimmt. Seiten können bei routinemäßigen Datenänderungsanweisungen (z. B. UPDATE, DELETE und INSERT) unsortiert vorliegen. Durch Fragmentierung kann es aufgrund der zusätzlichen E/A-Vorgänge, die für die Suche nach den Daten erforderlich sind, auf die die Zeiger in den Indexseiten verweisen, zu Leistungsproblemen kommen.

Wenn Daten in Indizes eingefügt, aktualisiert und daraus gelöscht werden, entspricht die logische Reihenfolge im Index nicht mehr der physischen Reihenfolge in und zwischen den Seiten, die die Indizes bilden. Außerdem können Datenänderungen im Laufe der Zeit dazu führen, dass die Daten in der Datenbank verstreut oder fragmentiert werden. Fragmentierung kann die Abfrageleistung beeinträchtigen, wenn die Datenbank-Engine zusätzliche Seiten lesen muss, um die benötigten Daten zu finden.

Die Neusortierung eines Indexes ist ein Onlinevorgang, der die Blattebene des Indexes (sowohl gruppiert als auch nicht gruppiert) defragmentiert. Bei dieser Defragmentierung werden die Seiten auf Blattebene physisch neu angeordnet, sodass sie der logischen Reihenfolge der Knoten von links nach rechts entsprechen. Während dieses Vorgangs werden die Indexseiten außerdem auf Grundlage des konfigurierten Füllfaktorwerts komprimiert.

Eine Neuerstellung kann je nach ausgeführtem Befehl oder verwendeter SQL Server-Edition online oder offline erfolgen. Bei einer Offlineneuerstellung wird der Index selbst gelöscht und neu erstellt. Wenn dieser Prozess auch online möglich ist, wird ein neuer Index parallel zum vorhandenen Index erstellt. Nachdem der neue Index erstellt wurde, wird der vorhandene gelöscht, und der neue Index wird umbenannt und erhält den Namen des alten Indexes. Beachten Sie, dass der Onlinevorgang zusätzlichen Speicherplatz erfordert, da der neue Index parallel zum vorhandenen Index erstellt wird.

Allgemeine Faustregel für die Indexwartung:

  • > 5 % aber < 30 % = Index neu sortieren

  • > 30% = Index neu erstellen

Verwenden Sie diese Zahlen als allgemeine Empfehlungen. Je nach Workload und Ihren Daten müssen Sie unter Umständen einen ausdrücklicheren Ansatz wählen oder können die Indexwartung in einigen Fällen für Datenbanken hinausschieben, die hauptsächlich Abfragen für bestimmte Seiten ausführen.

Die Plattformen SQL Server und Azure SQL umfassen DMVs, mit denen Sie eine Fragmentierung in Ihren Objekten erkennen können. Die dafür am häufigsten verwendeten DMVs sind sys.dm_db_index_physical_stats für B-Struktur-Indizes und sys.dm_db_column_store_row_group_physical_stats für Columnstore-Indizes.

Des Weiteren ist anzumerken, dass die Neuerstellung von Indizes dazu führt, dass die Statistiken zum Index aktualisiert werden, was die Leistung wiederum verbessern kann. Bei einer Neusortierung des Indexes werden die Statistiken nicht aktualisiert.

Von Microsoft wurden mit SQL Server 2017 fortsetzbare Indexneuerstellungsvorgänge eingeführt. Fortsetzbare Indexneuerstellungsvorgänge ermöglichen eine flexiblere Steuerung der ggf. benötigten Zeit für eine Neuerstellung auf eine bestimmte Instanz. Seit SQL Server 2019 kann zudem ein zugeordneter maximaler Grad an Parallelität festgelegt werden, was Datenbankadministratoren noch präzisere Steuerungsmöglichkeiten einräumt.

Statistik

Für die Leistungsoptimierung in Azure SQL ist es unerlässlich, die Bedeutung von Statistiken zu verstehen.

Statistiken werden in der Benutzerdatenbank als Binary Large Objects (Blobs) gespeichert. Diese Blobs enthalten statische Informationen über die Verteilung von Datenwerten auf eine oder mehrere Spalten einer Tabelle oder indizierten Sicht.

Statistiken enthalten Informationen zur Verteilung von Datenwerten innerhalb einer Spalte. Der Abfrageoptimierer verwendet Spalten- und Indexstatistiken, um die Kardinalität zu bestimmen. Dabei handelt es sich um die Anzahl der Zeilen, die erwartungsgemäß von einer Abfrage zurückgegeben werden.

Anschließend generiert der Abfrageoptimierer einen Ausführungsplan mithilfe von Kardinalitätsschätzungen. Kardinalitätsschätzungen helfen dem Optimierer auch zu ermitteln, mit welcher Art von Vorgang (z. B. Indexsuche oder -überprüfung) die angeforderten Daten abgerufen werden sollen.

Führen Sie die folgende Abfrage aus, um die Liste der benutzerdefinierten Statistiken mit dem Datum der letzten Aktualisierung anzuzeigen:

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

Erstellen von Statistiken

Wenn die Option AUTO_CREATE_STATISTICS auf ON festgelegt wurde, erstellt der Abfrageoptimierer standardmäßig Statistiken für die indizierte Spalte. Der Abfrageoptimierer erstellt Statistiken für einzelne Spalten in Abfrageprädikaten.

Diese Methoden bieten hochwertige Abfragepläne für die meisten Abfragen. Manchmal müssen Sie möglicherweise mit der CREATE STATISTICS-Anweisung mehr Statistiken erstellen, um bestimmte Abfragepläne zu verbessern.

Es wird empfohlen, die AUTO_CREATE_STATISTICS-Option aktiviert zu lassen, da der Abfrageoptimierer dann für Abfrageprädikatspalten automatisch Statistiken erstellen kann.

In den folgenden Situationen sollten Sie die Erstellung von Statistiken erwägen:

  • Der Datenbankoptimierungsratgeber schlägt vor, Statistiken zu erstellen.
  • Das Abfrageprädikat enthält mehrere Spalten, die sich noch nicht im gleichen Index befinden.
  • Bei der Abfrageausführung wird aus einer Teilmenge von Daten ausgewählt.
  • Statistiken für eine Abfrage fehlen.

Automatisierung von Wartungsaufgaben

Azure SQL bietet native Tools zum Durchführen von Datenbankwartungsaufgaben für Automatisierungszwecke. Abhängig von der Plattform, auf der die Datenbank ausgeführt wird, sind verschiedene Tools verfügbar.

SQL Server auf einem virtuellen Azure-Computer

Sie haben Zugriff auf Planungsdienste, z. B. SQL-Agent oder Windows-Taskplaner. Diese Automatisierungstools können hilfreich sein, um den Umfang einer Indexfragmentierung auf ein Minimum zu begrenzen. Bei größeren Datenbanken müssen sich Neuerstellungen und Neusortierungen der Indizes die Waage halten, um eine optimale Leistung zu gewährleisten. Dank der Flexibilität, die Ihnen der SQL-Agent oder der Taskplaner bieten, können Sie benutzerdefinierte Aufträge ausführen.

Azure SQL-Datenbank

Aufgrund der Beschaffenheit von Azure SQL-Datenbank bietet der Dienst keinen Zugriff auf den SQL Server-Agent und den Windows-Taskplaner. Ohne diese Dienste muss die Indexwartung mit anderen Methoden erstellt werden. Es gibt drei Möglichkeiten, um Wartungsvorgänge für SQL-Datenbank zu verwalten:

  • Azure Automation-Runbooks

  • SQL-Agent-Aufträge aus SQL Server in einer Azure-VM (Remoteaufruf)

  • Elastische Azure SQL-Aufträge

Verwaltete Azure SQL-Instanz

Wie in einer SQL Server-Instanz auf einer Azure-VM können Sie auch in SQL Managed Instance Aufträge über den SQL Server-Agent planen. Mit dem SQL Server-Agent kann Code, der die Indexfragmentierung in der Datenbank verringern soll, flexibel ausgeführt werden.