Wie erstelle ich Wartungsaufgaben, um Indizes in meiner RDS-für-SQL-Server-Instance neu zu erstellen?

Kurzbeschreibung

Die Indexfragmentierung ist ein kritisches Problem, das zu Leistungsproblemen in SQL-Server-Datenbanken führen kann, wenn sie nicht umgehend neu erstellt werden. Es hat sich bewährt, den Grad der Fragmentierung zu überwachen und Indizes regelmäßig neu zu erstellen und zu reorganisieren. Indexfragmentierung tritt in Form von Lücken auf Datenseiten und logischer Fragmentierung auf.

Hinweis: Beim Neuerstellen eines Index wird der Index gelöscht und dann neu erzeugt. Je nach Indextyp und Version der Datenbank-Engine kann ein Neuerstellungsvorgang offline oder online durchgeführt werden. Reorganisieren strukturiert die Informationen auf den Seiten neu, anstatt die Indizes zu löschen und dann neu zu erstellen.

RDS-für-SQL-Server bietet keine Wartungspläne, die automatisch Pläne und Jobs zum Neuerstellen oder Reorganisieren von Indizes erstellen. Sie können jedoch die folgenden Methoden verwenden, um Ihre Indizes neu zu erstellen:

• Methode 1: Erstellen Sie einen Auftrag für SQL Server Agent, um Indizes neu zu erstellen und Statistiken zu aktualisieren.
• Methode 2: Erstellen Sie den Neuerstellungsauftrag manuell mithilfe von Skripten und planen Sie ihn nach Bedarf.

Behebung

Methode 1: Erstellen Sie einen Auftrag für SQL Server Agent, um Indizes neu zu erstellen und Statistiken zu aktualisieren

1.    Starten Sie den Client von Microsoft SQL Server Management Studio und melden Sie sich an.

2.    Klicken Sie im rechten Bereich mit der rechten Maustaste auf SQL Server Agent. Wählen Sie dann Neu, Job aus, um einen SQL-Agent-Job zu erstellen.

3.    Geben Sie einen Namen und eine Beschreibung für den Agent-Job ein, und wählen Sie anschließend OK aus.

Beispiel:

• Name: Indexrebuild_job
• Beschreibung: Auftrag von RDS für SQL Server Agent für die Indexneuerstellung.

4.    Wählen Sie Schritte und dann Neu aus, um einen Ausführungsschritt hinzuzufügen. Es erscheint ein neues Fenster.

5.    Geben Sie den Namen des Schritts ein.

6.    Wählen Sie Datenbank aus, und fügen Sie dann den Befehl hinzu, den Sie regelmäßig ausführen möchten.

Im Folgenden finden Sie ein Beispiel für einen SQL-Befehl zur Indexneuerstellung. Sie können diesen Beispielbefehl verwenden, um fragmentierte Indizes aller Tabellen in der angegebenen Datenbank neu zu erstellen, deren Fragmentierung 30 % übersteigt. Ändern Sie den Wert von [DBNAME] in der ersten Zeile zum richtigen Namen für Ihre Datenbank. Wenn Sie denselben SQL-Befehl für alle Datenbanken ausführen, ändern Sie den Befehl entsprechend oder erstellen Sie für jede Datenbank einen separaten Job.

Use [DBNAME]
SET NOCOUNT ON
DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fragment float,@command VARCHAR(4000)
DECLARE AWS_Cusrsor CURSOR FOR
SELECT A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fragment FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B
ON A.object_id=B.object_id and A.index_id=B.index_id
INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id
INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id
WHERE B.avg_fragmentation_in_percent>30  AND A.index_id>0 AND A.IS_DISABLED<>1
ORDER BY tablename,ixname
OPEN AWS_Cusrsor
FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment
WHILE @@FETCH_STATUS=0
BEGIN
IF @avg_fragment>=30.0
BEGIN
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
--Can add following line for index reorganization. Else remove following line.
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE';
END
--PRINT @command
EXEC(@command)
FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment
END
CLOSE AWS_Cusrsor
DEALLOCATE AWS_Cusrsor

7.    Wählen Sie OK aus.

8.    Wählen Sie Zeitpläne und dann Neu aus, um einen Zeitplan für die Ausführung des Indexneuerstellungsauftrag hinzuzufügen.

9.    Füllen Sie den Namen des Zeitplans, den Zeitplantyp und ähnliche Felder aus und wählen Sie anschließend OK.

10.    Sehen Sie sich den Job an, den Sie gerade erstellt haben, und klicken Sie mit der rechten Maustaste darauf. Wählen Sie dann Job bei Schritt starten aus, um den Job manuell auszuführen und zu überprüfen, ob er korrekt ausgeführt werden kann.

Hinweis: Testen Sie die in diesem Beispiel bereitgestellten Skripte in einer RDS-Entwicklungsdatenbank, bevor Sie sie in einer RDS-Produktionsdatenbank bereitstellen. Die Zeit für die Indexneuerstellung ist je nach Größe und Anzahl der Indizes sehr unterschiedlich.

Der Optimizer muss über aktuelle Informationen zur Verteilung der Schlüsselwerte (Statistiken) der Tabellenspalten verfügen, um optimale Ausführungspläne zu erstellen. Es hat sich bewährt, die Statistiken für alle Tabellen regelmäßig zu aktualisieren. Vermeiden Sie es, Statistiken an den Tagen zu aktualisieren, an denen Sie Indizes neu erstellen.

Beachten Sie, dass die Statistikaktualisierung jeweils an einer Tabelle funktionieren. Der Befehl sp_updatestats auf Datenbankebene (auf der Microsoft-Website) ist in Amazon RDS nicht verfügbar. Schreiben Sie mithilfe von Statistikaktualisierungen einen Cursor, um Statistiken für alle Objekte in einer Datenbank zu aktualisieren. Erstellen Sie alternativ einen Wrapper für sp_updatestats und planen Sie ihn.

Um einen Wrapper für sp_updatestats zu verwenden, gehen Sie wie folgt vor:

1.    Führen Sie den Befehl aus, um ein gespeichertes Verfahren zu erstellen:

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

2.    Erteilen Sie einem Benutzer die Ausführungsberechtigung für das neue Verfahren:

grant execute on myRDS_updatestats to user

3.    Gehen Sie wie in Methode 1 beschrieben vor, um Aufgaben zur Aktualisierung von Statistiken zu planen.

Methode 2: Erstellen Sie den Neuerstellungsauftrag manuell mithilfe von Skripten und planen Sie ihn nach Bedarf

Sie können manuell Skripts oder Verfahren erstellen, um fragmentierte Indizes zu überprüfen und die Indexneuerstellung anhand dieser nach einem Zeitplan durchzuführen. Sie können Ihren eigenen Code erstellen und manuelle Wartungsaufträge mithilfe von Skripten konfigurieren.

Sie können zudem die aws-rds-indexmaintenance-job-example-SQL-Skripts von GitHub verwenden. Diese Skripte erstellen Indizes wöchentlich neu und organisieren sie neu, abhängig von der Fragmentierungsstufe. Das Skript erstellt eine Datenbank (IndexStats) und Objekte (Tabellen), um Informationen zu allen Datenbanken auf der Instance zu speichern. Zu diesen Informationen gehören die Tabellen, Indizes und Fragmentierungsprozentsätze der Datenbanken.

Das aws-rds-indexmaintenance-job-example enthält zwei Skripte, CreateDatabaseAndObjects.sql und CreateWeeklyMaintenanceJob.sql.

Das Skript CreateDatabaseAndObjects.sql macht Folgendes:

• Erstellt eine Tabelle mit dem Namen ServerDatabases. In dieser Tabelle werden die Datenbanken auf der aktuellen Instance aufgezeichnet. Systemdatenbanken (Master, Model, TempDB und msdb) sind ausgeschlossen. Datenbanken, die von SQL-Server-Komponenten wie SSIS und SSRS (rdsadmin_ReportServer, rdsadmin_ReportServerTempDB) erstellt wurden, sind ebenfalls ausgeschlossen.
• Erstellt eine Tabelle mit dem Namen ServerTables. Diese Tabelle sammelt Tabellen für alle Datenbanken in der ServerDatabases-Tabelle.
• Erstellt eine Tabelle mit dem Namen Nachrichten. Diese Tabelle enthält die Nachricht, auf die im Index reagiert wurde (REBUILD oder REORGANIZE). Sie können die Nachricht kopieren und bei Bedarf manuell ausführen.

Das Skript CreateWeeklyMaintenanceJob.sql erstellt das folgende gespeicherte Verfahren:

• sp_PopulateDatabases: Dieses Verfahren durchläuft alle Datenbanken auf der Instance und zeichnet sie in der Tabelle ServerDatabases auf. Systemdatenbanken oder Datenbanken, die von SQL-Server-Komponenten wie SSAS und SSRS erstellt wurden, sind nicht enthalten. SSIDB für SSIS ist enthalten.
• sp_PopulateTables: Dieses Verfahren durchläuft jede Datenbank und zeichnet seine Tabellen in ServerTables auf. Nach dem Aufzeichnen der Tabellen überprüft es das Schema, zu dem die Tabelle gehört, und sucht dann nach vorhandenen Indizes. Das gespeicherte Verfahren durchläuft die Indizes, sucht nach den Indexinformationen, die am stärksten fragmentiert sind, und zeichnet sie auf.
• Sp_ReindexTables: Dieses Verfahren liest die Informationen aus den ServerTables und startet den Neuerstellungs- oder Defragmentierungsprozess unter Verwendung der folgenden Regeln:
  Die Fragmentierung beträgt 0-9 % = NOTHING
  Die Fragmentierung beträgt 10-30 % = REORGANIZE
  Die Fragmentierung beträgt 31-100 % = REBUILD

Um die aws-rds-indexmaintenance-job-example-Skripts von GitHub zu verwenden, kopieren und führen Sie zunächst die Datei CreateDatabaseAndObjects.sql aus. Führen Sie dann das Skript CreateWeeklyMaintenanceJob.sql aus.

Hinweis: Aufgrund von Beschränkungen für verwaltete Dienste können einen Auftrag für SQL Server Agent nur von dem Konto aus erstellt werden, das gerade angemeldet ist. Als Auftragsinhaber sind keine anderen Konten zulässig.