Tempdb, data files & performance

  • Article

Un’istanza SQL Server ha un solo tempdb e, per questo e per il fatto che molte attività lo possono utilizzare in maniera intensiva, potrebbe diventare un collo di bottiglia.

Per verificare problemi di performance posso iniziare controllando la contesa delle risorse tramite la DMV sys.dm_os_waiting_tasks, quindi con uno script simile a:

 USE tempdb;
GO

SET NOCOUNT ON;
SELECT session_id , 
       wait_type , 
       wait_duration_ms , 
       resource_description
  FROM sys.dm_os_waiting_tasks
  WHERE 
    wait_type LIKE 'PAGELATCH_%' AND 
    (
    resource_description LIKE '2:%:1' OR 
    resource_description LIKE '2:%:2' OR 
    resource_description LIKE '2:%:3'
    );

Nel caso in cui riscontrassimo problemi possiamo:

  • considerare di avere più di un unico file dati (vedi sotto)
  • verificare la necessità di aumentare RAM
  • avere i file dati già preallocati (non ho frammentazione e risparmio il tempo di autoincremento)
  • posizionare il tempdb in un’unità dedicata
  • posizionare il tempdb su un sottosistema dischi performante
  • abilitare il Trace Flag 1118
  • verificare di avere abilitato l’instant database file inizialization

Per decidere quanti file dati avere possiamo utilizzare questa formula:

  • se abbiamo meno di 8 core, allora utilizziamo un file per core
  • se abbiamo più di 8 core, utilizziamo 8 file
    • se dovessimo ancora notare della contesa di risorse aggiungiamo altri 4 file (ed altri 4 se necessario, ed altri 4, …)

 

Di seguito un esempio per verificare e confrontare il numero di files con il numero di core presenti:

 USE tempdb;
     GO
SET NOCOUNT ON;

DECLARE @cpuCount tinyint;
DECLARE @tempdbFilesCount tinyint;

SET @cpuCount = ( SELECT COUNT( * )
                    FROM sys.dm_os_schedulers
                    WHERE status = 'VISIBLE ONLINE' );

SET @tempdbFilesCount = ( SELECT COUNT( 1 )
                            FROM sys.dm_db_file_space_usage f 
                       JOIN sys.master_files AS m 
                          ON m.database_id = f.database_id AND m.file_id = f.file_id 
                     WHERE m.state = 0)
                     

SELECT @tempdbFilesCount AS tempdbFilesCount , 
       @cpuCount AS cpuCount , 
       CASE
       WHEN @tempdbFilesCount != @cpuCount THEN 'nr files != nr cpu'
           ELSE 'nr files = nr cpu'
       END AS info;

image

Best practice: tutti i files devono essere creati (e mantenuti) con le stesse caratteristiche (stessa dimensione, stesso incremento)

Di seguito un esempio per capire, al volo, se ho data file di differenti dimensioni:

 WITH nrFileSize
    AS ( SELECT DISTINCT ROW_NUMBER( )OVER( ORDER BY size )AS n , 
                         CONVERT( bigint , size ) * 8 / 1024 AS size
           FROM tempdb.sys.database_files
           WHERE type_desc = 'Rows' )
    SELECT TOP 1 '[!] different size on tempdb data file' as info
      FROM nrFileSize
      WHERE n > 1;

 

In ultima analisi è sempre bene fare in modo che il valore di autoincremento dei file sia compatibile con il reale utilizzo del db.

Per fare una valutazione sensata può essere presa in considerazione questa formula:

  • se il file è compreso tra 0 e 100 MB : incremento di 10 MB
  • se il file è compreso tra 100 e 200 MB: incremento di 20 MB
  • se il file è maggiore di 200 MB: incremento del 10% o superiore

Se volessimo fare una verifica al volo:

 SET NOCOUNT ON;
USE tempdb; 
GO

SELECT name AS 'File' , 
       CAST( CAST( SIZE * 1.0 / 128 AS decimal( 9 , 2 ))AS varchar( 12 )) + ' Mb' AS 'File Size' , 
       CASE
       WHEN CAST( SIZE * 1.0 / 128 AS decimal( 9 , 2 ))BETWEEN 0 AND 100 THEN '10 MB'
       WHEN CAST( SIZE * 1.0 / 128 AS decimal( 9 , 2 ))BETWEEN 101 AND 200 THEN '20 MB'
       WHEN CAST( SIZE * 1.0 / 128 AS decimal( 9 , 2 )) > 200 THEN '10% (or higher)'
       END AS [Increment Suggested (!)]
  FROM tempdb.sys.database_files
  ORDER BY name;

image

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
image