Come posso creare attività di manutenzione per ricostruire gli indici nella mia istanza Amazon RDS per SQL Server?

Breve descrizione

La frammentazione degli indici è un aspetto critico che, in assenza di una ricostruzione tempestiva, può causare problemi di prestazioni nei database SQL Server. È consigliabile monitorare il livello di frammentazione, nonché ricostruire e riorganizzare gli indici periodicamente. La frammentazione degli indici si presenta come discontinuità nelle pagine di dati e frammentazione logica.

Amazon RDS per SQL Server non prevede piani di manutenzione che creano automaticamente piani o processi di ricostruzione o riorganizzazione degli indici.

Tuttavia, puoi ricostruire gli indici utilizzando uno dei seguenti metodi:

• Crea un processo dell'Agente SQL Server per ricostruire gli indici e aggiornare le statistiche.
• Utilizza script e pianificazioni per creare manualmente un processo di ricostruzione.

Nota: quando ricostruisci un indice, il motore di database elimina e ricrea l'indice. A seconda del tipo di indice e della versione del motore di database, puoi creare un'operazione di ricostruzione offline o online. Quando riorganizzi un indice, il motore di database non elimina né ricrea l'indice, ma ristruttura le informazioni nelle pagine.

Risoluzione

Crea un processo dell'Agente SQL Server per ricostruire gli indici e aggiornare le statistiche

Completa i seguenti passaggi:

1. Avvia il client Microsoft SQL Server Management Studio (SSMS) ed effettua l’accesso.

2. Nel pannello destro, fai are clic con il pulsante destro del mouse su SQL Server Agent (Agente SQL Server), quindi scegli New Job (Nuovo processo).

3. Inserisci un nome per il processo dell'agente in Name (Nome) e inserisci una descrizione in Description (Descrizione).

4. Scegli OK.

5. Scegli Steps (Passaggi), quindi scegli New (Nuovo).

6. Per Step name (Nome fase), inserisci un nome per la fase.

7. Seleziona Database e aggiungi il comando che desideri eseguire periodicamente.
   Di seguito è riportato un esempio di comando SQL per la ricostruzione degli indici:

   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

   Nota: sostituisci DBNAME con il nome del tuo database. Puoi utilizzare l'esempio di comando precedente per ricostruire gli indici frammentati di tutte le tabelle del database specificato che superano il 30% di frammentazione. Se esegui lo stesso comando SQL per tutti i database, modifica il comando di conseguenza o crea un processo separato per ogni database.

8. Scegli OK.

9. Scegli Schedules (Pianificazioni), quindi New (Nuovo) per pianificare l'esecuzione del processo di ricostruzione dell'indice.

10. Inserisci i dettagli della pianificazione, quindi scegli OK.

11. Fai clic con il pulsante destro del mouse sul processo, quindi scegli Start Job at Step (Inizia processo dalla fase) per verificare che il processo possa essere eseguito.

Nota: prima di distribuire l'indice in un database di produzione, testa gli script dell'esempio precedente in un database RDS di sviluppo. Il tempo di ricostruzione degli indici varia in base alla dimensione e al numero di indici.

Per generare piani di esecuzione ottimali, l'ottimizzatore deve disporre di informazioni aggiornate sulla distribuzione dei valori chiave (statistiche) delle colonne delle tabelle. È consigliabile aggiornare regolarmente le statistiche per tutte le tabelle. Non aggiornare le statistiche nei giorni in cui ricostruisci gli indici.

Il comando update statistics funziona su una tabella alla volta. Il comando sp_updatestats a livello di database non è disponibile in Amazon RDS. Per ulteriori informazioni, consulta sp_updatestats sul sito web di Microsoft.

Per aggiornare le statistiche su tutti gli oggetti di un database, utilizza update statistics per scrivere un cursore. Oppure crea un wrapper attorno a sp_updatestats e pianificalo.

Per utilizzare un wrapper attorno a sp_updatestats, completa i seguenti passaggi:

1. Esegui questo comando per creare una procedura archiviata:

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

2. Concedi l'autorizzazione execute a un utente per la nuova procedura:

   grant execute on myRDS_updatestats to user

3. Pianifica i processi di aggiornamento delle statistiche.

Utilizza script e pianificazioni per creare manualmente i processi di ricostruzione

Per controllare gli indici frammentati ed eseguirne la ricostruzione in base a una pianificazione, crea manualmente script o procedure. Puoi utilizzare gli script per creare un codice personalizzato e configurare processi di manutenzione manuali.

Puoi anche utilizzare gli script SQL aws-rds-indexmaintenance-job-example del sito web di GitHub. Gli script ricostruiscono e riorganizzano gli indici settimanalmente a seconda del livello di frammentazione. Gli script creano un database (IndexStats) e oggetti (tabelle) per archiviare informazioni su tutti i database dell'istanza, tra cui tabelle, indici e percentuali di frammentazione dei database.

aws-rds-indexmaintenance-job-example contiene gli script CreateDatabaseAndObjects.sql e CreateWeeklyMaintenanceJob.sql.

Lo script CreateDatabaseAndObjects.sql crea le seguenti tabelle:

• Una tabella Messages che contiene il messaggio per l'azione REBUILD o REORGANIZE eseguita sull'indice.
  Nota: puoi copiare ed eseguire manualmente il messaggio, se necessario.
• Una tabella ServerDatabases che registra i database dell'istanza corrente.
  Nota: la tabella esclude i database di sistema (Master, Model, TempDB e msdb). La tabella esclude abche i database creati dai componenti di SQL Server, ad esempio SSIS e SSRS (rdsadmin_ReportServer e rdsadmin_ReportServerTempDB).
• Una tabella ServerTables che raccoglie le tabelle per tutti i database nella tabella ServerDatabases.

Lo script CreateWeeklyMaintenanceJob.sql crea la seguente procedura archiviata:

• La procedura sp_PopulateDatabases registra tutti i database dell'istanza nella tabella ServerDatabases.
  Nota: la tabella esclude i database di sistema o i database creati dai componenti di SQL Server, ad esempio SSAS e SSRS. La tabella include SSIDB per SSIS.
• La procedura sp_PopulateTables registra le tabelle di ogni database in ServerTables.
  Nota: dopo aver registrato le tabelle, la procedura verifica lo schema a cui appartiene la tabella e quindi cerca gli indici contenuti nello schema. Dopodiché cerca negli indici le informazioni più frammentate dell'indice e le registra.
• La procedura sp_ReindexTables legge le informazioni da ServerTables e utilizza le seguenti regole per avviare il processo di ricostruzione o deframmentazione:
  Frammentazione pari a 0-9% = NOTHING
  Frammentazione pari a 10-30% = REORGANIZE
  Frammentazione pari a 31-100% = REBUILD

Per utilizzare gli script aws-rds-indexmaintenance-job-example scripts di GitHub, completa i seguenti passaggi:

1. Copia ed esegui lo script CreateDatabaseAndObjects.sql.
2. Esegui lo script CreateWeeklyMaintenanceJob.sql.

Nota: a causa delle restrizioni dei servizi gestiti, puoi creare processi dell'Agente SQL Server solo in un account AWS che ha effettuato l'accesso al client SSMS.