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

Ultimo aggiornamento: 08/12/2022

Desidero creare attività di manutenzione per la ricostruzione dell'indice nella mia istanza Amazon Relational Database Service (Amazon RDS) per Microsoft SQL Server. Come posso fare?

Breve descrizione

La frammentazione dell'indice è un problema fondamentale che può causare problemi di prestazioni nei database di SQL Server se non vengono ricostruiti tempestivamente. È consigliabile monitorare il livello di frammentazione e ricostruire e riorganizzare gli indici regolarmente. La frammentazione dell'indice si verifica sotto forma di lacune nelle pagine di dati e di frammentazione logica.

Nota: la ricostruzione di un indice elimina e poi ricrea l'indice. A seconda del tipo di indice e della versione del motore di database, un'operazione di ricostruzione può essere eseguita offline oppure online. Riorganizza e ristruttura le informazioni sulle pagine anziché eliminare e poi ricreare gli indici.

RDS per SQL Server non dispone di piani di manutenzione che creano automaticamente piani e processi per ricostruire o riorganizzare gli indici. Tuttavia, puoi utilizzare i seguenti metodi per ricostruire gli indici:

  • Metodo 1: creare un processo agente SQL Server per ricostruire gli indici e aggiornare le statistiche.
  • Metodo 2: creare manualmente un processo di ricostruzione utilizzando script e pianificato in base alle esigenze.

Risoluzione

Metodo 1: creare un processo agente SQL Server per ricostruire gli indici e aggiornare le statistiche

1.    Avvia il client Microsoft SQL Server Management Studio e poi accedi.

2.    Nel pannello di destra, fai clic con il pulsante destro del mouse su SQL Server Agent (Agente del server SQL). Quindi, scegli New (Nuovo), Job (Processo) per creare un processo di agente SQL.

3.    Immetti un Nome e una Descrizione per il processo agente, quindi seleziona OK.

Esempio:

  • Nome: Indexrebuild_job
  • Descrizione: processo dell'agente RDS per SQL Server per la ricostruzione dell'indice.

4.    Seleziona Steps (Fasi), quindi seleziona New (Nuova) per aggiungere una fase di esecuzione. Viene visualizzata una nuova finestra.

5.    Inserisci il Step name (Nome della fase).

6.    Seleziona Database, quindi aggiungi il comando che desideri eseguire periodicamente.

Di seguito è riportato un esempio di comando SQL di ricostruzione dell’indice. Puoi utilizzare questo comando di esempio per ricostruire gli indici frammentati di tutte le tabelle nel database specificato che superano una frammentazione del 30%. Cambia il valore di [DBNAME] nella prima riga con il nome corretto per il tuo database. Se esegui lo stesso comando SQL per tutti i database, modifica il comando di conseguenza o crea un processo distinto per ogni 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, quindi scegli Nuovo per aggiungere una pianificazione in cui indicare quando eseguire il processo di ricostruzione dell'indice.

9.    Immetti il Name (Nome) della pianificazione, il Schedule type (Tipo di pianificazione) e così via, quindi seleziona OK.

10.    Visualizza il processo appena creato e fai clic con il pulsante destro del mouse. Quindi, scegli Start Job at Step (Avvia processo alla fase) per eseguire manualmente il processo e verificare che possa essere eseguito correttamente.

Nota: testa gli script forniti in questo esempio in un database RDS di sviluppo prima di distribuirli su un database RDS di produzione. Il tempo di ricostruzione dell'indice varia notevolmente in base alla dimensione e al numero degli indici.

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

Ricorda che le statistiche di aggiornamento funzionano su una tabella alla volta. Il comando a livello di database sp_updatestats non è disponibile in Amazon RDS. Scrivi un cursore utilizzando le statistiche di aggiornamento per aggiornare le statistiche su tutti gli oggetti in un database. Oppure, crea un wrapper attorno a sp_updatestats e pianificalo.

Per utilizzare un wrapper intorno a sp_updatestats, effettua le seguenti operazioni:

1.    Esegui il comando per creare una procedure archiviata:

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

2.    Concedi il permesso di esecuzione a un utente sulla nuova procedura:

grant execute on myRDS_updatestats to user

3.    Segui le fasi precedenti del Metodo 1 per pianificare i processi di aggiornamento delle statistiche.

Metodo 2: crea manualmente i processi di ricostruzione utilizzando gli script e pianificali in base alle esigenze

Puoi creare manualmente script o procedure per controllare gli indici frammentati ed eseguire la ricostruzione dell'indice in base a una pianificazione. Puoi creare il tuo codice e configurare i processi di manutenzione manuale utilizzando degli script.

Inoltre, puoi utilizzare 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 le informazioni relative a tutti i database sull'istanza. Queste informazioni includono le tabelle, gli indici e le percentuali di frammentazione dei database.

L'esempio 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. Questa tabella 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. Questa tabella raccoglie le tabelle per tutti i database nella tabella ServerDatabases.
  • Crea una tabella denominata Messages (Messaggi). Questa tabella contiene il messaggio che è stato eseguito sull'indice (REBUILD o REORGANIZE). Puoi copiare il messaggio ed eseguirlo manualmente, se necessario.

Lo script CreateWeeklyMaintenanceJob.sql crea la seguente procedura archiviata:

  • sp_PopulateDatabases: questa procedura esamina tutti i database dell'istanza e li registra nella tabella ServerDatabases. Non include i database di sistema o i database creati da componenti di SQL Server come SSAS e SSRS. SSIDB per SSIS è incluso.
  • sp_PopulateTables: questa procedura esamina ciascun database e registra le relative tabelle in ServerTables. Dopo avere registrato le tabelle, verifica lo schema a cui appartiene la tabella e quindi cerca gli eventuali indici che contiene. La procedura archiviata esamina gli indici e cerca le informazioni sull'indice più frammentate e le registra.
  • Sp_ReindexTables: questa procedura legge le informazioni dalle ServerTables e avvia il processo di ricostruzione o deframmentazione utilizzando le seguenti regole:
    La frammentazione è di 0-9% = NOTHING
    La frammentazione è di 10-30% = REORGANIZE
    La frammentazione è di 31-100% = REBUILD

Per utilizzare gli script aws-rds-indexmaintenance-job-example disponibili su GitHub, copia ed esegui prima CreateDatabaseAndObjects.sql. Quindi, esegui lo script CreateWeeklyMaintenanceJob.sql.

Nota: per via delle restrizioni dei servizi gestiti, i processi di SQL Server Agent possono essere creati solo dall'account attualmente connesso. Non sono ammessi altri account come titolari dei processi.


Questo articolo è stato utile?


Hai bisogno della fattura o di supporto tecnico?