Strategie di ottimizzazione dell’indice SQL Server

La manutenzione dell’indice è un componente critico dell’amministrazione del database in quanto aiuta a garantire l’efficienza e le prestazioni in corso di a Server SQL (Structured Question Language) ambiente. Nel tempo, quando i dati vengono aggiunti, aggiornati e cancellati, può verificarsi frammentazione dell’indice, in cui l’ordinamento logico e fisico delle pagine dell’indice viene disallineato. Questa frammentazione può portare advert un aumento dell’I/O del disco, alla riduzione delle prestazioni delle question e all’inefficienza complessiva del sistema. L’esecuzione di lavori di manutenzione dell’indice, come quelli forniti dalla soluzione di manutenzione del server SQL OLA HALLENGREN, consentono a DBA di affrontare in modo proattivo questa frammentazione e ottimizzare gli indici per prestazioni migliori.

Monitorando regolarmente i livelli di frammentazione dell’indice ed eseguendo operazioni di manutenzione come riorganizzazioni e ricostruzioni di indici, i DBA possono mantenere i loro database in esecuzione al massimo dell’efficienza. Ciò è particolarmente importante per i database di grandi dimensioni e mission-critical, in cui qualsiasi degrado delle prestazioni può avere un impatto commerciale significativo. Il mantenimento della salute ottimale dell’indice aiuta a garantire un accesso rapido e affidabile dei dati, un consumo di risorse ridotto e un miglioramento complessivo dell’esperienza dell’utente. Di conseguenza, l’implementazione di una strategia di manutenzione dell’indice ben progettata è una responsabilità cruciale per qualsiasi DBA che gestisce un complesso Server SQL ambiente.

Soluzione di manutenzione del server SQL di Ola Hallengren

La soluzione di manutenzione SQL Server, sviluppata da OLA Hallengren, è un insieme di script ampiamente adottato e affidabile utilizzati dagli amministratori del database in tutto il mondo. Questa soluzione completa automatizza varie attività di manutenzione, tra cui l’ottimizzazione dell’indice, i controlli di integrità del database e gli aggiornamenti delle statistiche. Gli script di OLA sono diventati lo commonplace del settore per la manutenzione proattiva del database.

La procedura IndexOptimize dalla soluzione di manutenzione fornisce ampie opzioni di personalizzazione e configurazione per adattare il processo di manutenzione dell’indice per ambienti e requisiti specifici. Molti amministratori di database si basano su questi script come base per la loro strategia di gestione degli indici, in quanto offrono un modo robusto ed efficiente per mantenere gli indici in uno stato ottimale.

È possibile scaricare l’ultima versione della soluzione di manutenzione SQL Server da Il sito internet di Ola Hallengren. Gli script vengono rilasciati sotto la licenza MIT, consentendo agli utenti di utilizzare, modificarli e distribuirli liberamente se necessario.

Core IndexOptimizzare i parametri e il loro impatto

IL `IndexOptimize` La procedura memorizzata fornisce un’ampia personalizzazione attraverso numerosi parametri. Comprendere questi è fondamentale per un’implementazione efficace:

Parametri essenziali

Parametro	Descrizione	Impatto
`@Database`	Database di destinazione	Controlla l’ambito operativo
`@Frammentationlow`	Azione per bassa frammentazione	Tipicamente null (nessuna azione)
`@Frammentationmedium`	Azione per frammentazione media	Di solito riorganizza
`@Frammentationhigh`	Azione per alta frammentazione	Ricostruire o riorganizzare
`@Frammentationlevel1`	Soglia bassa/media (%)	In genere 5-15%
`@Frammentationlevel2`	Soglia media/alta (%)	In genere il 30-40%
`@PageCounTlevel`	Dimensione minima dell’indice da elaborare	Esclude piccoli indici
`@SortIntempdb`	Usa tempdb per l’ordinamento	Riduce I/O del database di produzione
`@Maxdop`	Grado di parallelismo	Controlla l’utilizzo della CPU
`@FILLFACTOR`	Fattore di riempimento dell’indice	Controlla lo spazio libero nelle pagine
`@Padindex`	Applicare il fattore di riempimento a livelli non foglie	Influisce sulla dimensione complessiva dell’indice
`@Lobcompaction`	Dati LOB compatti	Riduce l’archiviazione per le colonne del lob
`@Updatestatistics`	Aggiorna le statistiche dopo la ricostruzione	‘All’, ‘colonne’, ‘indice’, null
`@OnlyModifiedStatistics`	Aggiorna solo le statistiche modificate	Riduce aggiornamenti inutili
`@Timelimit`	Tempo di esecuzione massima (secondi)	Previene i lavori in fuga
`@Delay`	Pausa tra le operazioni (secondi)	Riduce la pressione continua delle risorse
`@Indici ‘	Indici specifici da mantenere	Consente la manutenzione mirata
`@MinnumberOfPages`	Soglia di dimensioni minime	Alternativa a PageCountlevel
`@Maxnumberofpages`	Soglia di dimensioni massime	Limita il funzionamento a indici più piccoli
`@Blocktimeout`	Timeout di blocco (secondi)	Impedisce il blocco
`@Logtotable`	Operazioni di registro alla tabella	Abilita il monitoraggio/risoluzione dei problemi
Parametro	Descrizione	Impostazione consigliata
`@DisponibilityGroups`	Ags specifico per goal	Limitare l’ambito quando necessario
`@DisponibilityGrouPreplicas`	Repliche specifiche goal	“Primario” per limitare l’impatto di Ag
`@DisponibilityGroupDatabase`	Database specifici di destinazione	Concentrati su database critici

Parametri specifici del gruppo di disponibilità

Parametro	Descrizione	Impostazione consigliata
`@DisponibilityGroups`	Ags specifico per goal	Limitare l’ambito quando necessario
`@DisponibilityGrouPreplicas`	Repliche specifiche goal	“Primario” per limitare l’impatto di Ag
`@DisponibilityGroupDatabase`	Database specifici di destinazione	Concentrati su database critici

Strategie di implementazione per dimensione dell’indice

Indici di grandi dimensioni (> 10 GB)

EXECUTE dbo.IndexOptimize
    @Databases="PRODUCTION_DB",
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh="INDEX_REORGANIZE,INDEX_REBUILD_ONLINE",
    @FragmentationLevel1 = 15,
    @FragmentationLevel2 = 40,
    @PageCountLevel = 10000, -- Solely course of substantial indexes
    @MaxDOP = 4,            -- Restrict CPU utilization
    @TimeLimit = 7200,      -- 2-hour restrict per operation
    @Delay = '00:00:45',    -- 45-second pause between operations
    @SortInTempdb = 'Y',    -- Scale back database file I/O
    @MaxNumberOfPages = NULL, -- No higher restrict
    @MinNumberOfPages = 10000,
    @LockTimeout = 300,     -- 5-minute lock timeout
    @LogToTable="Y",
    @Execute="Y";

Considerazioni speciali:

Preferisci riorganizzarsi per indici di grandi dimensioni per ridurre al minimo la crescita del registro delle transazioni
Utilizzare ricostruire selettivamente quando riorganizzare è insufficiente
Implementare più grande `@Delay`Per consentire l’elaborazione del registro delle transazioni
Programma durante i periodi di bassa attività
Prendi in considerazione i lotti più piccoli usando `@Indexes` parametro

Indici medi (1 GB-10 GB)

EXECUTE dbo.IndexOptimize
    @Databases="PRODUCTION_DB",
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh="INDEX_REBUILD_ONLINE",
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @PageCountLevel = 1000,
    @MaxDOP = 2,
    @TimeLimit = 3600,      -- 1-hour restrict
    @Delay = '00:00:20',    -- 20-second pause
    @SortInTempdb = 'Y',
    @MinNumberOfPages = 1000,
    @MaxNumberOfPages = 10000,
    @LockTimeout = 180,     -- 3-minute lock timeout
    @LogToTable="Y",
    @Execute="Y";

Considerazioni speciali:

Equilibrio tra riorganizzare e ricostruire le operazioni
Moderare `@Delay` Valore per gestire l’impatto delle risorse
Può funzionare più frequentemente della manutenzione dell’indice grande

Piccoli indici (

EXECUTE dbo.IndexOptimize
    @Databases="PRODUCTION_DB",
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh="INDEX_REBUILD_ONLINE",
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @PageCountLevel = 100,
    @MaxDOP = 0,            -- Use server default
    @TimeLimit = 1800,      -- 30-minute restrict
    @Delay = '00:00:05',    -- 5-second pause
    @SortInTempdb = 'Y',
    @MaxNumberOfPages = 1000,
    @MinNumberOfPages = 100,
    @LockTimeout = 60,      -- 1-minute lock timeout
    @LogToTable="Y",
    @Execute="Y";

Considerazioni speciali:

Può essere più aggressivo con le operazioni di ricostruzione.
Minimo `@Delay` necessario tra le operazioni.
Può funzionare durante le normali ore lavorative con un impatto minimo.

Configurazioni specifiche del gruppo di disponibilità

Ambiente: Database OLTP di grandi dimensioni e mission-critical con più repliche in un gruppo di disponibilità (AG) configurato per il commit sincrono.

Obiettivi di manutenzione:

Ridurre al minimo l’impatto sul carico di lavoro di produzione e sulla spedizione di registro.
Evita di esaurire le risorse di archiviazione a causa della crescita dei registri.
Garantire un’alta disponibilità e tempi di inattività minimi.

Ambiente AG sincrono

EXECUTE dbo.IndexOptimize
    @Databases="PRODUCTION_DB",
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh="INDEX_REORGANIZE",  -- Keep away from rebuilds in sync AGs
    @FragmentationLevel1 = 15,
    @FragmentationLevel2 = 40,
    @PageCountLevel = 5000,
    @MaxDOP = 2,
    @TimeLimit = 3600,
    @Delay = '00:01:00',    -- Longer delay for sync replicas
    @AvailabilityGroupReplicas="PRIMARY",
    @LockTimeout = 300,
    @LogToTable="Y",
    @Execute="Y";

Considerazioni sull’AG sincrono:

Ridurre al minimo le ricostruzioni – I registri delle transazioni devono essere sincronizzati prima del completamento dell’operazione.
Implementare ritardi più lunghi tra le operazioni per consentire la sincronizzazione.
Monitorare il ritardo di duplicate e sospendere i lavori se il ritardo supera le soglie.
Aumenta la frequenza di backup del registro Durante le finestre di manutenzione.
Manutenzione divisa in più giorni per ambienti molto grandi.

Ambiente AG asincrono

Ambiente: Database di information warehouse di grandi dimensioni con repliche AG asincrone.

Obiettivi di manutenzione:

Eseguire la manutenzione completa dell’indice e delle statistiche
Ridurre al minimo l’impatto sul carico di lavoro di reporting durante la finestra di manutenzione
Garantire prestazioni ottimali per il prossimo trimestre

EXECUTE dbo.IndexOptimize
    @Databases="PRODUCTION_DB",
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh="INDEX_REBUILD_ONLINE",  -- Rebuilds extra acceptable
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @PageCountLevel = 2000,
    @MaxDOP = 4,
    @TimeLimit = 5400,
    @Delay = '00:00:30',    -- Average delay
    @AvailabilityGroupReplicas="PRIMARY",
    @LockTimeout = 240,
    @LogToTable="Y",
    @Execute="Y";

Considerazioni asincrone AG:

Più liberale con le ricostruzioni – Le operazioni non aspettano la sincronizzazione secondaria.
Monitorare comunque l’invio della coda per prevenire travolgenti secondari.
Prendi in considerazione la larghezza di banda di rete e regolare `@Delay` di conseguenza.
Implementare avvisi di dimensioni della coda di invio durante la manutenzione.

Prevenire la pressione di conservazione e IOPS

Preparazione pre-manutenzione

Espandi i file di registro delle transazioni in modo proattivo:

ALTER DATABASE (YourDatabase) MODIFY FILE (NAME = LogFileName, SIZE = ExpandedSizeInMB);

Configurare il tempdb correttamente:

-- Confirm TempDB configuration
   SELECT identify, measurement/128.0 AS (Size_MB)
   FROM tempdb.sys.database_files;

Implementare controlli pre-manutenzione:

-- Create helper process to validate setting readiness
   CREATE PROCEDURE dbo.ValidateMaintenanceReadiness
   AS
   BEGIN
       DECLARE @IssuesFound BIT = 0;
       
       -- Verify log house
       IF EXISTS (
           SELECT 1 FROM sys.databases d
           CROSS APPLY sys.dm_db_log_space_usage() l
           WHERE d.database_id = DB_ID()
           AND l.log_space_used_percent > 30
       )
       BEGIN
           RAISERROR('Log utilization exceeds 30%. Backup logs earlier than continuing.', 16, 1);
           SET @IssuesFound = 1;
       END
       
       -- Verify AG well being
       IF EXISTS (
           SELECT 1 FROM sys.dm_hadr_availability_replica_states ars
           JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
           WHERE ars.is_local = 0
           AND ars.synchronization_health  2 -- Not HEALTHY
       )
       BEGIN
           RAISERROR('Availability Group replicas not in wholesome state.', 16, 1);
           SET @IssuesFound = 1;
       END
       
       RETURN @IssuesFound;
   END;
   GO

Tecniche operative

Implementare la selezione dinamica dell’indice in base all’impatto del enterprise:

-- Create index precedence classes
   CREATE TABLE dbo.IndexMaintenancePriority (
       SchemaName NVARCHAR(128),
       TableName NVARCHAR(128),
       IndexName NVARCHAR(128),
       Precedence INT, -- 1=Excessive, 2=Medium, 3=Low
       MaintenanceDay TINYINT -- Day of week (1-7)
   );
   
   -- Use with dynamic execution
   DECLARE @IndexList NVARCHAR(MAX);
   SELECT @IndexList = STRING_AGG(CONCAT(DB_NAME(), '.', SchemaName, '.', TableName, '.', IndexName), ',')
   FROM dbo.IndexMaintenancePriority
   WHERE Precedence = 1 AND MaintenanceDay = DATEPART(WEEKDAY, GETDATE());
   
   EXEC dbo.IndexOptimize
       @Databases="PRODUCTION_DB",
       @Indexes = @IndexList,
       -- different parameters

Implementa tecniche di thottling I/O:

Usa il governatore delle risorse per limitare l’I/O (SQL Server Enterprise).
Impostare più in basso `@MaxDOP` valori durante le ore lavorative.
Implementare più a lungo `@Delay` valori durante i periodi di picco.

Tuning I/O a livello di database:

-- Contemplate hint flag 1117 for uniform file development
   DBCC TRACEON(1117, -1);
   
   -- Contemplate hint flag 1118 for lowering SGAM rivalry
   DBCC TRACEON(1118, -1);
   
   -- For SQL Server 2016+, use correct tempdb configuration
   ALTER DATABASE (tempdb) MODIFY FILE (NAME = 'tempdev', SIZE = 8GB);

Strategie di pianificazione avanzate

Batching workload consapevole

-- Create helper process for sensible batching
CREATE PROCEDURE dbo.ExecuteIndexMaintenanceBatch
    @BatchSize INT = 5,
    @MaxRuntime INT = 7200  -- 2 hours in seconds
AS
BEGIN
    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @EndTime DATETIME = DATEADD(SECOND, @MaxRuntime, @StartTime);
    DECLARE @CurrentTime DATETIME;
    DECLARE @IndexBatch NVARCHAR(MAX);
    
    WHILE (1=1)
    BEGIN
        SET @CurrentTime = GETDATE();
        IF @CurrentTime > @EndTime BREAK;
        
        -- Get subsequent batch of indexes based mostly on precedence and fragmentation
        SELECT TOP (@BatchSize) @IndexBatch = STRING_AGG(CONCAT(DB_NAME(), '.', s.identify, '.', t.identify, '.', i.identify), ',')
        FROM sys.indexes i
        JOIN sys.tables t ON i.object_id = t.object_id
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps 
            ON ps.object_id = i.object_id AND ps.index_id = i.index_id
        WHERE i.type_desc="NONCLUSTERED"
        AND ps.avg_fragmentation_in_percent > 30
        AND ps.page_count > 1000
        AND NOT EXISTS (
            -- Skip indexes we have already processed
            SELECT 1 FROM dbo.CommandLog
            WHERE DatabaseName = DB_NAME()
            AND SchemaName = s.identify
            AND ObjectName = t.identify
            AND IndexName = i.identify
            AND StartTime > DATEADD(DAY, -7, GETDATE())
        )
        ORDER BY ps.avg_fragmentation_in_percent DESC;
        
        IF @IndexBatch IS NULL BREAK;  -- No extra work to do
        
        -- Execute upkeep for this batch
        EXEC dbo.IndexOptimize
            @Databases = DB_NAME(),
            @Indexes = @IndexBatch,
            @FragmentationLow = NULL,
            @FragmentationMedium = 'INDEX_REORGANIZE',
            @FragmentationHigh="INDEX_REORGANIZE",
            @FragmentationLevel1 = 10,
            @FragmentationLevel2 = 30,
            @MaxDOP = 2,
            @TimeLimit = 1800,  -- half-hour per batch
            @Delay = '00:00:30',
            @LogToTable="Y",
            @Execute="Y";
            
        -- Pause between batches
        WAITFOR DELAY '00:01:00';
    END
END;
GO

Framework di monitoraggio

-- Create monitoring saved process
CREATE PROCEDURE dbo.MonitorIndexMaintenance
AS
BEGIN
    -- Verify transaction log utilization
    SELECT DB_NAME(database_id) AS DatabaseName,
           log_space_in_use_percentage
    FROM sys.dm_db_log_space_usage
    WHERE log_space_in_use_percentage > 50;
    
    -- Verify AG ship queue measurement
    SELECT ar.replica_server_name,
           drs.database_name,
           drs.log_send_queue_size,
           drs.log_send_rate,
           drs.redo_queue_size,
           drs.redo_rate
    FROM sys.dm_hadr_database_replica_states drs
    JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
    WHERE drs.log_send_queue_size > 10000
    OR drs.redo_queue_size > 10000;
    
    -- Verify ongoing index operations
    SELECT r.session_id,
           r.command,
           r.standing,
           r.wait_type,
           r.wait_time,
           OBJECT_NAME(p.object_id) AS ObjectName,
           p.index_id,
           i.identify AS IndexName
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    LEFT JOIN sys.partitions p ON p.hobt_id = r.statement_id
    LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
    WHERE t.textual content LIKE '%INDEX_REBUILD%' OR t.textual content LIKE '%INDEX_REORGANIZE%';
END;
GO

Riepilogo delle migliori pratiche

Per ambienti AG sincroni:

Priorità RIORGANIZZARE Sopra RICOSTRUIREspecialmente per indici di grandi dimensioni.
Implementare ritardi più lunghi tra le operazioni (45-90 secondi).
Pianificare la manutenzione durante i periodi meno attivi.
Prendi in considerazione il partizionamento di tavoli molto grandi per la manutenzione incrementale.

Per ambienti AG asincroni:

Uso più liberale di RICOSTRUIRE per indici critici.
Implementare ritardi moderati (15-45 secondi).
Monitorare inviare la coda e rifare da vicino le dimensioni della coda.

Tecniche di riduzione degli IOP generali:

Leva `@Sortintempdb = ‘Y’` per diffondere il carico I/O.
Usa `@Maxdop`Per controllare il parallelismo (valori più bassi riducono l’I/O).
Implementa `@Ritardo`Parametri appropriati per il tuo ambiente.
Usa `@Timelimit`per prevenire operazioni in fuga.

Mitigazione della pressione di stoccaggio:

Pre-allocare lo spazio del registro delle transazioni prima della manutenzione.
Aumenta la frequenza di backup del registro durante la manutenzione (ogni 5-15 minuti).
Usa il governatore delle risorse per limitare I/o impatto.
Implementare approcci in batch con pause acceptable.

Approccio di manutenzione globale:

Strategie various per dimensioni di indici various.
Configurazioni di attività commerciale e off-ora.
Priorità basata sull’impatto del enterprise.
Verifica regolare dei livelli di frammentazione dopo la manutenzione.

Implementando queste linee guida e adattando gli script forniti al tuo ambiente specifico, è possibile mantenere ottimale SQL Server Indice Prestazioni riducendo al minimo l’impatto della produzione, anche in complesse configurazioni del gruppo di disponibilità.