Come posso creare attività di manutenzione per ricostruire gli indici nell’istanza 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 e ricostruire e riorganizzare gli indici periodicamente. La frammentazione degli indici si presenta discontinuità nelle pagine di dati e di frammentazione logica.

Nota: La ricostruzione di un indice elimina e quindi ricrea l'indice. È possibile eseguire un'operazione di ricostruzione offline o online, a seconda del tipo di indice e della versione del motore del database. La riorganizzazione ristruttura le informazioni sulle pagine invece di eliminare e ricreare gli indici.

RDS per SQL Server non prevede piani di manutenzione che creano automaticamente piani e processi di ricostruzione o riorganizzazione degli indici. Tuttavia, è possibile ricostruire gli indici utilizzando i seguenti metodi:

• Metodo 1: Crea un processo SQL Server Agent per ricostruire gli indici e aggiornare le statistiche.
• Metodo 2: Crea manualmente un processo di ricostruzione con l’impiego di script, pianificato in base alle esigenze.

Soluzione

Metodo 1: Crea un processo SQL Server Agent per ricostruire gli indici e aggiornare le statistiche

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

2.    Nel pannello di destra, fai clic con il pulsante destro del mouse su SQL Server Agent. Seleziona Nuovo, Processo per creare un processo SQL Agent.

3.    Compila i campi Nome e Descrizione per il processo dell'agente, quindi seleziona OK.

Esempio:

• Nome: Indexrebuild_job
• Descrizione: Processo RDS per SQL Server Agent per la ricostruzione degli indici.

4.    Seleziona Fasi e successivamente Nuovo per aggiungere una fase di esecuzione. Viene visualizzata una nuova finestra.

5.    Compila il campo Nome della fase.

6.    Seleziona Database e aggiungi il comando che desideri eseguire periodicamente.

Di seguito è riportato un esempio di comando SQL per la ricostruzione degli indici. È possibile utilizzare questo comando di esempio per ricostruire gli indici frammentati di tutte le tabelle del database specificato che superano il 30% di frammentazione. Sostituisci il valore [DBNAME] nella prima riga con il nome corretto del database. Se esegui lo stesso comando SQL per tutti i database, modifica il comando di conseguenza oppure crea un processo separato per ciascun database.

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.    Seleziona OK.

8.    Seleziona Pianificazioni e successivamente Nuova per pianificare l’esecuzione del processo di ricostruzione degli indici.

9.    Compila i campi Nome, Tipo di pianificazione e campi simili, quindi seleziona OK.

10.    Visualizza il processo creato e fai clic con il pulsante destro del mouse. Quindi seleziona Avvia processo dalla fase per eseguire manualmente il processo e verificarne la corretta esecuzione.

Nota: Testa gli script forniti in questo esempio in un database RDS di sviluppo prima dell’implementazione in un database RDS di produzione. Il tempo di ricostruzione degli indici varia notevolmente in base alla dimensione e al numero di indici.

Per poter generare piani di esecuzione ottimali, l'ottimizzatore deve avere accesso a informazioni aggiornate sulla distribuzione dei valori chiave (statistiche) delle colonne della tabella. È consigliabile aggiornare periodicamente le statistiche di tutte le tabelle. È preferibile evitare di aggiornare le statistiche nei giorni in cui viene eseguita la ricostruzione degli indici.

Tieni presente che l’aggiornamento delle statistiche avviene su una tabella alla volta. Il comando a livello di database sp_updatestats (sul sito web Microsoft) non è disponibile in Amazon RDS. Scrivi un cursore utilizzando le statistiche di aggiornamento per aggiornare le statistiche su tutti gli oggetti di un database. Oppure crea un wrapper per sp_updatestats e pianificalo.

Per utilizzare un wrapper per sp_updatestats, completa la seguente procedura:

1.    Esegui il comando per creare una procedura archiviata:

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

2.    Concedi l'autorizzazione di esecuzione a un utente sulla nuova procedura:

grant execute on myRDS_updatestats to user

3.    Completa i passaggi precedenti del Metodo 1 per pianificare i processi di aggiornamento delle statistiche.

Metodo 2: Crea manualmente i processi di ricostruzione con l’impiego di script, pianificato in base alle esigenze

È possibile creare manualmente script o procedure per controllare gli indici frammentati ed eseguire la ricostruzione degli indici in base a una pianificazione. È possibile utilizzare gli script per creare un codice personale e configurare i processi di manutenzione manuale.

È anche possibile usare gli script SQL aws-rds-indexmaintenance-job-example disponibili su GitHub. Questi script ricostruiscono e riorganizzano gli indici settimanalmente in base al livello di frammentazione. Lo script crea un database (IndexStats) e oggetti (tabelle) per archiviare informazioni relative a tutti i database dell'istanza. Queste informazioni includono le tabelle, gli indici e le percentuali di frammentazione dei database.

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

Lo script CreateDatabaseAndObjects.sql esegue le seguenti operazioni:

• Crea una tabella denominata ServerDatabases, che registra i database sull'istanza corrente. I database di sistema (Master, Model, TempDB e msdb) sono esclusi. Sono esclusi anche i database creati da componenti di SQL Server come SSIS e SSRS (rdsadmin_ReportServer, rdsadmin_ReportServerTempDB).
• Crea una tabella denominata ServerTables, che raccoglie le tabelle per tutti i database nella tabella ServerDatabases.
• Crea una tabella denominata Messages, che contiene il messaggio applicato per l'indice (REBUILD o REORGANIZE). È possibile copiare il messaggio ed eseguirlo manualmente, se necessario.

Lo script CreateWeeklyMaintenanceJob.sql crea la seguente procedura archiviata:

• sp_PopulateDatabases: Questa procedura analizza tutti i database dell'istanza e li registra nella tabella ServerDatabases. Non include database di sistema o database creati da componenti di SQL Server come SSAS e SSRS. SSIDB per SSIS è incluso.
• sp_PopulateTables: Questa procedura analizza ciascun database e registra le relative tabelle in ServerTables. Dopo la registrazione delle tabelle, la procedura controlla lo schema a cui appartiene la tabella e ricerca gli indici presenti. La procedura archiviata analizza gli indici e ricerca le informazioni degli indici più frammentate, registrandole.
• Sp_ReindexTables: Questa procedura legge le informazioni in ServerTables e avvia il processo di ricostruzione o deframmentazione utilizzando le seguenti regole:
  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 disponibili su GitHub, per prima cosa copia ed esegui CreateDatabaseAndObjects.sql. Quindi esegui lo script CreateWeeklyMaintenanceJob.sql.

Nota: A causa delle restrizioni dei servizi gestiti, i processi SQL Server Agent possono essere creati soltanto dall'account corrente connesso. Nessun altro account è autorizzato a svolgere il ruolo di titolare del processo.