• Articolo

Ottimizzazione delle prestazioni di tempdb

Le dimensioni e la posizione fisica del database tempdb possono influire sulle prestazioni di un sistema. Se, ad esempio, le dimensioni definite per tempdb sono eccessivamente ridotte, il carico di elaborazione del sistema può essere in parte dovuto alla necessità di aumentare automaticamente le dimensioni del database tempdb fino a raggiungere quelle necessarie per supportare il carico di lavoro a ogni riavvio dell'istanza di SQL Server. Per ovviare a questo overhead, aumentare le dimensioni del file di log e di dati di tempdb. Per informazioni su come determinare la quantità di spazio su disco necessaria per tempdb, vedere Pianificazione delle capacità per tempdb.

Indicazioni relative alla posizione e alle dimensioni di tempdb

Per ottenere prestazioni ottimali, è consigliabile adottare la configurazione indicata di seguito per tempdb in un ambiente di produzione:

  • Impostare il modello di recupero di tempdb su SIMPLE. Con questo modello viene automaticamente liberato lo spazio di log per mantenere ridotti i requisiti di spazio.

    Per ulteriori informazioni, vedere ALTER DATABASE (Transact-SQL) o Procedura: Visualizzazione o modifica del modello di recupero di un database (SQL Server Management Studio).

  • Consentire ai file di tempdb di crescere automaticamente in base alle necessità, in modo che le dimensioni dei file aumentino finché il disco non risulterà pieno.

    Nota

    Se l'ambiente di produzione non è in grado di tollerare i potenziali timeout dell'applicazione che potrebbero verificarsi durante le operazioni di aumento automatico delle dimensioni, preallocare lo spazio necessario per il carico di lavoro previsto.

  • Impostare un valore di aumento delle dimensioni del file tale da evitare aumenti troppo ridotti delle dimensioni dei file del database tempdb. Se l'aumento delle dimensioni dei file è troppo ridotto in confronto alla quantità di dati scritti nel database tempdb, è possibile che tempdb si espanda continuamente. Questo comportamento ha un impatto negativo sulle prestazioni. Per l'impostazione dell'incremento FILEGROWTH per i file di tempdb, è consigliabile applicare le linee guida generali seguenti:

    Dimensioni dei file di tempdb

    Incremento FILEGROWTH

    da 0 a 100 MB

    10 MB

    da 100 a 200 MB

    20 MB

    200 MB o più

    10%*

    * Potrebbe essere necessario regolare questo valore percentuale in base alla velocità del sottosistema I/O in cui sono presenti i file di tempdb. Per evitare potenziali timeout di latch, è consigliabile limitare l'operazione di aumento automatico delle dimensioni a circa due minuti. Ad esempio, se il sottosistema I/O può inizializzare un file a 50 MB al secondo, l'incremento FILEGROWTH può essere impostato su un massimo di 6 GB, indipendentemente dalle dimensioni dei file di tempdb. Se possibile, utilizzare l'inizializzazione immediata dei file di database per migliorare le prestazioni delle operazioni di aumento automatico delle dimensioni.

  • Preallocare lo spazio per tutti i file di tempdb impostando le relative dimensioni su un valore adeguato per il carico di lavoro tipico nell'ambiente. In questo modo, il database tempdb non si espanderà con una frequenza eccessiva e le prestazioni non subiranno alterazioni. È opportuno impostare il database tempdb per l'aumento automatico delle dimensioni, tuttavia questa funzionalità dovrebbe essere utilizzata per aumentare lo spazio su disco per le eccezioni non pianificate.

  • Creare la quantità di file necessaria per ottimizzare la larghezza di banda del disco. L'utilizzo di più file comporta una riduzione dei conflitti del database tempdb a livello di archiviazione e conferisce una maggiore scalabilità. È tuttavia consigliabile non creare troppi file per non ridurre le prestazioni e aumentare l'overhead di gestione. Come linea guida generale, creare un file di dati per ogni CPU nel server, tenendo conto di eventuali impostazioni della maschera di affinità, quindi aumentare o ridurre il numero di file in base alle esigenze. Si noti che una CPU dual core viene considerata come due CPU.

  • Fare in modo che ogni file di dati presenti le stesse dimensioni, in modo da garantire prestazioni ottimali a livello di riempimento proporzionale.

  • Posizionare il database tempdb in un sottosistema I/O veloce. In presenza di molti dischi collegati direttamente, utilizzare lo striping del disco.

  • Posizionare il database tempdb in dischi diversi da quelli utilizzati dai database utente.

Modifica dei parametri di dimensione e aumento delle dimensioni del database tempdb

È possibile modificare i parametri di dimensione e aumento delle dimensioni dei file di log o di dati del database tempdb mediante uno dei metodi seguenti:

I valori dei parametri di dimensione e aumento delle dimensioni dei file vengono utilizzati ogni volta che viene creato il database tempdb. Ad esempio, se si aumentano le dimensioni del file di dati di tempdb a 20 MB e si aumenta l'incremento dell'aumento automatico delle dimensioni dei file del 15%, i nuovi valori avranno effetto immediato. Se attività transazionali successive causano l'aumento delle dimensioni di tempdb, le dimensioni del file di dati verranno reimpostate automaticamente su 20 MB a ogni riavvio dell'istanza di SQL Server.

Visualizzazione dei parametri di dimensione e aumento delle dimensioni del database tempdb

È possibile visualizzare i parametri di dimensione e aumento delle dimensioni dei file di log o di dati del database tempdb mediante uno dei metodi seguenti:

  • SQL Server Management Studio

  • Esecuzione della query seguente

    SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Rilevamento di errori di percorso di I/O su disco

Se impostata su CHECKSUM, l'opzione PAGE_VERIFY individua le pagine del database danneggiate a causa di errori di percorso di I/O su disco e segnala gli errori, ad esempio MSSQLSERVER_823, MSSQLSERVER_824 o MSSQLSERVER_825, nel log degli errori di SQL. Gli errori di percorso di I/O su disco possono essere la causa di danni del database e sono in genere provocati da interruzioni dell'alimentazione o da errori hardware a livello di disco che si verificano durante la scrittura della pagina su disco. Per ulteriori informazioni sugli errori di I/O, vedere la pagina Web Microsoft SQL Server I/O Basics, Chapter 2.

Nelle versioni precedenti di SQL Server l'opzione di database PAGE_VERIFY è impostata su NONE per il database tempdb e non può essere modificata. In SQL Server 2008 il valore predefinito per il database tempdb è CHECKSUM per le nuove installazioni di SQL Server. Quando si aggiorna un'installazione di SQL Server, viene mantenuto il valore predefinito NONE. È consigliabile impostare l'opzione PAGE_VERIFY per il database tempdb su CHECKSUM.