Wie erstelle ich Wartungsaufgaben, um Indizes in meiner Amazon 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, die Indizes regelmäßig neu zu erstellen und zu reorganisieren. Indexfragmentierung tritt in Form von Lücken auf Datenseiten und logischer Fragmentierung auf.

Amazon RDS-für-SQL-Server bietet keine Wartungspläne, die automatisch Pläne oder Jobs zum Neuerstellen oder Reorganisieren von Indizes erstellen.

Du kannst jedoch eine der folgenden Methoden verwenden, um die Indizes neu zu erstellen:

• Erstelle einen Auftrag für SQL Server Agent, um Indizes neu zu erstellen und Statistiken zu aktualisieren.
• Verwende Skripts und Zeitpläne, um einen Neuerstellungsauftrag manuell zu erstellen.

Hinweis: Wenn du einen Index neu erstellst, löscht das Datenbankmodul den Index und erstellt ihn neu. Je nach Indextyp und Version der Datenbank-Engine kannst du ein Neuerstellungsvorgang offline oder online erstellen. Wenn du einen Index neu organisierst, löscht das Datenbankmodul den Index nicht und erstellt ihn nicht neu. Stattdessen strukturiert das Datenbankmodul die Informationen auf den Seiten um.

Lösung

Erstelle einen Auftrag für SQL Server Agent, um Indizes neu zu erstellen und Statistiken zu aktualisieren

Führe die folgenden Schritte aus:

1. Starte den Client von Microsoft SQL Server Management Studio (SSMS) und melde dich an.

2. Klicke im rechten Bereich mit der rechten Maustaste auf SQL Server Agent und wähle dann Neuer Auftrag.

3. Gib unter Name einen Namen für den Agent-Auftrag ein und gib unter Beschreibung eine Beschreibung ein.

4. Klicke auf OK.

5. Wähle Schritte und dann Neu.

6. Gib unter Schrittname einen Namen für den Schritt ein.

7. Wähle Datenbank und füge dann den Befehl hinzu, den du regelmäßig ausführen möchtest.
   Im Folgenden findest du ein Beispiel für einen SQL-Befehl zur Indexneuerstellung:

   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

   Hinweis: Ersetze DBNAME durch den Namen der Datenbank. Du kannst den vorherigen Beispielbefehl verwenden, um fragmentierte Indizes aller Tabellen in der angegebenen Datenbank neu zu erstellen, deren Fragmentierung 30 % übersteigt. Wenn du denselben SQL-Befehl für alle Datenbanken ausführst, ändere den Befehl entsprechend oder erstelle für jede Datenbank einen separaten Job.

8. Klicke auf OK.

9. Wähle Zeitpläne und dann Neu, um einen Zeitplan für die Ausführung des Indexneuerstellungsauftrag hinzuzufügen.

10. Gib die Details für den Zeitplan ein und wähle dann OK.

11. Klicke mit der rechten Maustaste auf den Job und wähle dann Auftrag bei Schritt starten um zu überprüfen, ob der Auftrag ausgeführt werden kann.

Hinweis: Bevor du den Index in einer Produktionsdatenbank bereitstellst, teste die Skripts im vorherigen Beispiel in einer Entwicklungs-RDS-Datenbank. Die Zeit für die Indexneuerstellung ist je nach Größe und Anzahl der Indizes unterschiedlich.

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

Die Statistikaktualisierung funktionieren jeweils an einer Tabelle. Der Befehl sp_updatestats auf Datenbankebene ist in Amazon RDS nicht verfügbar. Weitere Informationen findest du unter sp_updatestats auf der Microsoft-Website.

Um Statistiken für alle Objekte in einer Datenbank zu aktualisieren, verwende Statistikaktualisierungen, um einen Cursor zu schreiben. Erstelle alternativ einen Wrapper für sp_updatestats und plane ihn.

Gehe wie folgt vor, um einen Wrapper für sp_updatestats zu verwenden:

1. Führe den folgenden Befehl aus, um ein gespeichertes Verfahren zu erstellen:

   create procedure myRDS_updatestatswith execute as ‘dbo’  
   as  
   exec sp_updatestats  
   go

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

   grant execute on myRDS_updatestats to user

3. Plane Aufträge zur Aktualisierung von Statistiken.

Scripts und Zeitpläne verwenden, um Neuerstellungsaufträge manuell zu erstellen

Um fragmentierte Indizes zu überprüfen und die Indexneuerstellung anhand dieser nach einem Zeitplan durchzuführen, erstelle Skripts oder Verfahren manuell. Du kannst Skripts verwenden, um den eigenen Code zu erstellen und manuelle Wartungsaufträge zu konfigurieren.

Du kannst zudem die aws-rds-indexmaintenance-job-example-SQL-Skripts auf der GitHub-Website verwenden. Die Skripte erstellen Indizes wöchentlich neu und organisieren sie neu, abhängig von der Fragmentierungsstufe. Die Skripte erstellen eine Datenbank (IndexStats) und Objekte (Tabellen), um Informationen über alle Datenbanken in der Instance zu speichern, einschließlich der Tabellen, Indizes und Fragmentierungsprozentsätze der Datenbanken.

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

Das Skript CreateDatabaseAndObjects.sql erstellt die folgenden Tabellen:

• Eine Nachrichten-Tabelle, die die Nachricht für die Aktion REBUILD oder REORGANIZE enthält, die für den Index ausgeführt wird.
  Hinweis: Du kannst die Nachricht kopieren und bei Bedarf manuell ausführen.
• Eine ServerDatabases-Tabelle, die die Datenbanken auf der aktuellen Instance aufzeichnet.
  Hinweis: Die Tabelle schließt Systemdatenbanken (Master, Model, TempDB und msdb) aus. Die Tabelle schließt auch Datenbanken aus, die von SQL Server-Komponenten erstellt werden, wie SSIS und SSRS (rdsadmin_ReportServer und rdsadmin_ReportServerTempDB).
• Eine ServerTables-Tabelle, die Tabellen für alle Datenbanken in der ServerDatabases-Tabelle sammelt.

Das Skript CreateWeeklyMaintenanceJob.sql erstellt die folgenden gespeicherten Verfahren:

• Das Verfahren sp_PopulateDatabases zeichnet alle Datenbanken auf der Instance in der ServerDatabases-Tabelle auf.
  Hinweis: Die Tabelle schließt Systemdatenbanken oder Datenbanken aus, die von SQL Server-Komponenten erstellt werden, wie SSAS und SSRS. Die Tabelle enthält SSIDB für SSIS.
• Das Verfahrensp_PopulateTables zeichnet die Tabellen jeder Datenbank in ServerTables auf.
  Hinweis: Nachdem das Verfahren die Tabellen aufgezeichnet hat, überprüft es das Schema, zu dem die Tabelle gehört und sucht dann nach Indizes, die das Schema enthält. Das Verfahren durchsucht dann die Indizes nach den am stärksten fragmentierten Indexinformationen und zeichnet sie auf.
• Das Verfahren sp_ReindexTables liest die Informationen aus ServerTables und verwendet die folgenden Regeln, um das Neuerstellungs- oder Defragmentierungverfahren zu starten:
  Die Fragmentierung beträgt 0-9 % = NOTHING
  Die Fragmentierung beträgt 10-30 % = REORGANIZE
  Die Fragmentierung beträgt 31-100 % = REBUILD

Gehe wie folgt vor, um die aws-rds-indexmaintenance-job-example-skripts von GitHub zu verwenden:

1. Kopiere das Skript CreateDatabaseAndObjects.sql und führe es aus.
2. Führe das Skript CreateWeeklyMaintenanceJob.sql aus.

Hinweis: Aufgrund von Einschränkungen für verwaltete Service kannst du SQL Server-Agent-Aufträge nur in einem AWS-Konto erstellen, das beim SSMS-Client angemeldet ist.