Datenbankwartungsprüfungen erkunden

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.

Durch gesunde Indizes und Statistiken wird sichergestellt, dass ein bestimmter Plan optimal effizient ausgeführt wird. 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.

Neu erstellen und neu organisieren

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) in ungeordneter Reihenfolge 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 Neuorganisation eines Indexes ist ein Onlinevorgang, der die Blattebene des Indexes (sowohl clustered als auch nonclustered) 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 Sie dies online tun können, wird parallel zum vorhandenen Index ein neuer Index erstellt. Nachdem der neue Index erstellt wurde, wird das vorhandene abgelegt, und dann wird der neue so umbenannt, dass er dem alten Indexnamen entspricht. Beachten Sie, dass die Onlineversion mehr Platz benötigt, da der neue Index parallel zum vorhandenen Index erstellt wird.

Allgemeine Faustregel für die Indexwartung:

• > 5%, aber < 30% – Neuorganisieren des Indexes

• > 30% – Index neu erstellen

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

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-Baum-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. Die Option für fortsetzbare Indexneuerstellungsvorgänge bietet eine flexiblere Steuerung der ggf. benötigten Zeit, die einer bestimmten Instanz möglicherweise auferlegt wird. 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 statistische 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 Scan) 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

Statistiken erstellen

Wenn Sie die Option AUTO_CREATE_STATISTICS auf ON festlegen, 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 Statistiken für Abfrage-Prädikatspalten automatisch 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.
• Die Abfrage wählt aus einer Teilmenge von Daten aus.
• Die Abfrage weist fehlende Statistiken auf.

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 Computer in Azure

Sie haben Zugriff auf Aufgabenplanungsdienste, 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 muss ein Gleichgewicht zwischen einem Neuaufbau und einer Reorganisation von Indizes gefunden werden, um eine optimale Leistung zu gewährleisten. Dank der Flexibilität, die Ihnen der SQL Agent oder der Task Scheduler 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 durchgeführt werden. Es gibt drei Möglichkeiten, um Wartungsvorgänge für SQL-Datenbank zu verwalten:

• Azure Automation-Runbooks

• SQL-Agent-Auftrag aus SQL Server in einer Azure-VM (Remoteaufruf)

• Elastische Azure SQL-Jobs

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.