Ottimizzazione delle prestazioni di tempdb

Data aggiornamento: 5 dicembre 2005

Le dimensioni e la posizione fisica del database tempdb possono influire sulle prestazioni di un sistema. Ad esempio, se le dimensioni definite per tempdb sono troppo 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 ogni volta che viene riavviata l'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 di tempdb, è consigliabile adottare la configurazione seguente 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.

Per ulteriori suggerimenti, vedere Working with tempdb in SQL Server 2005 (informazioni in lingua inglese).

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:

• Istruzione ALTER DATABASE
• SQL Server Management Studio

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 causeranno l'aumento delle dimensioni di tempdb, le dimensioni del file di dati torneranno sul valore di 20 MB ogni volta che verrà riavviata l'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
  

Vedere anche

Attività

Risoluzione dei problemi relativi allo spazio su disco insufficiente in tempdb

Concetti

Database tempdb
Pianificazione delle capacità per tempdb

Altre risorse

Ottimizzazione dei database
ALTER DATABASE (Transact-SQL)
sys.database_files (Transact-SQL)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche