Sottovalutare questo aspetto potrebbe avere conseguenze nefaste per le nostre applicazioni che reperiscono i dati grazie a SQL server.
Ogni tipo di dato, sia a lunghezza fissa che a lunghezza variabile, ha una propria allocazione in termini di byte scritti sul disco.
Le righe contenute nelle nostre tabelle vengono archiviate in strutture di 8K chiamate data page ed ogni riga ( salvo casi particolari ) dovrà essere contenuta interamente in una singola data page.
La quantità di righe che riusciamo a contenere in una singola data page determina il numero di data page che costituiranno la nostra tabella.
All'aumentare delle data page aumenteranno le operazioni di I/O per evadere le nostre richieste e di conseguenza potrebbero calare drasticamente le performance....
Come fare allora ?
In fase di creazione/modifica delle colonne nelle tabelle dobbiamo sempre "selezionare il tipo di dato più piccolo per contenere il valore più grande".
prendiamo per esempio il tipo di dato Char.
Char è un tipo di dato a lunghezza fissa. Se dichiariamo una colonna come char(25) significa che ogni riga, per quella colonna, allocherà SEMPRE 25 byte a prescindere dai dati che abbiamo inserito. Se nella nostra colonna char(25) inseriamo la stringa '12345' la sua allocazione sarà comunque 25 byte in quanto SQL Server riempirà con spazi i restanti byte. Quindi ci troveremmo nella colonna la stringa '12345 ' (12345 seguito da 20 spazi)
20 byte sprecati in pratica....
Immaginate che la tabella in questione contenga un miliardo di righe '12345 '.
Significa che solo per storicizzare i dati della colonna in questione SQL deve usare 20 Miliardi di Byte..... 20 GB !!!
Se invece avessimo utilizzato un tipo di dato a lunghezza variabile varchar(25) l'allocazione della stringa '12345' sarebbe stata di 5 byte + 2 byte di Overhead. Solo 7 byte quindi.
Immaginate che la tabella in questione contenga un miliardo di righe '12345'.
Significa che solo per storicizzare i dati della colonna in questione SQL deve usare 7 Miliardi di Byte..... 7 GB !!!
Abbiamo ottenuto un risparmio enorme di spazio ma quello che più importa è che abbiamo ridotto, con questa semplicissima operazione, il numero di operazioni di I/O per evadere le query che lavorano sulla nostra tabella.
Pensiamo ad una tabella contenente una colonna "id" numerica autoincrementante che dovrà contenere 4 miliardi di righe.
Che tipo di dato scegliamo per la nostra colonna id ?
Vediamo quali tipi di dato numerici interi abbiamo a disposizione :
Tinyint che copre da 0 a 255 con una allocazione di 1 byte.
Smallint che copre da -32,768 a 32,767 con una allocazione di 2 byte.
Int che copre da -2,147,483,648 a 2,147,483,647 con una allocazione di 4 byte.
Bigint che copre da -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807 con una allocazione di 8 byte.
I primi 2 non coprono il range richiesto quindi li scartiamo a priori.
Restano Int e Bigint.
Se scegliamo BigInt (8 byte) la nostra colonna allocherà 8byte * 4miliardi di righe = 32 miliardi di byte. 32 GB !!
Se scegliamo Int (4 byte) la nostra colonna allocherà 4byte * 4miliardi di righe = 16 miliardi di byte. 16 GB !!
Abbiamo dimezzato lo spazio necessario e dimezzato il numero di operazioni di I/O per evadere le nostre query.
Vediamo un esempio pratico
Use tempDb
Go
/*
Creo una tabella scegliendo il tipo
di dato errato e con una dimensione errata.
*/
Create table dbo.WrongDatatypeTab
(
id bigint identity, --8 Byte
Nome Char(800), --800 Byte
Cognome Char(800), --800 Byte
Indirizzo Char(6000) --6000 Byte
)
/*
Creo una tabella scegliendo il tipo
di dato adeguato e con una dimensione.
*/
Create table dbo.BestDatatypeTab
(
id smallint identity (-32768,1), -- 2 Byte
Nome varchar(25), -- da 2 a 27 byte
Cognome varchar(40), -- da 2 a 42 byte
Indirizzo varchar(500) -- da 2 a 502 byte
)
/*
Popolo con le stesse 1000 righe le tabelle
*/
Set nocount On
Declare @count int = 1
While @count <= 1000
Begin
Insert into dbo.WrongDatatypeTab (Nome,Cognome,Indirizzo)
values('Nome ' + CAST(@count as varchar(25))
,'Cognome ' + CAST(@count as varchar(25))
,'Indirizzo ' + CAST(@count as varchar(25))
)
Insert into dbo.BestDatatypeTab (Nome,Cognome,Indirizzo)
values('Nome ' + CAST(@count as varchar(25))
,'Cognome ' + CAST(@count as varchar(25))
,'Indirizzo ' + CAST(@count as varchar(25))
)
Set @count += 1
end
/*
forzo il flush
e ripulisco il buffer dalle dirty pages
*/
Checkpoint
dbcc dropcleanbuffers()
Guardiamo ora quante pagine compongono le nostre tabelle
e quante righe sono contenute
/*
Guardiamo quante pagine compongono le nostre tabelle
*/
SELECT OBJECT_NAME( object_id ) ,
index_depth ,
page_count
FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'dbo.WrongDatatypeTab' ) , NULL , NULL , 'DETAILED' )s
UNION
SELECT OBJECT_NAME( object_id ) ,
index_depth ,
page_count
FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'dbo.BestDatatypeTab' ) , NULL , NULL , 'DETAILED' )s;
GO
/*
Guardiamo quante righe contengono le nostre pagine
*/
SELECT OBJECT_NAME( i.object_id )AS 'tableName' ,
--i.name AS 'indexName' ,
i.type_desc ,
--MAX( p.partition_number )AS 'partitions' ,
SUM( p.rows )AS 'rows' ,
SUM( au.data_pages )AS 'dataPages' ,
SUM( p.rows ) / CASE
WHEN(SUM( au.data_pages ) = 0)THEN 1
ELSE SUM( au.data_pages )
END AS 'rowsPerPage'
FROM sys.indexes AS i JOIN sys.partitions AS p ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
WHERE OBJECT_NAME( i.object_id )NOT LIKE 'sys%'
AND au.type_desc = 'IN_ROW_DATA'
AND p.object_id IN(
SELECT object_id
FROM sys.objects
WHERE type = 'U' )
GROUP BY OBJECT_NAME( i.object_id ) ,
i.name ,
i.type_desc
ORDER BY rowsPerPage;
Checkpoint dbcc dropcleanbuffers() Set statistics io on Select * from dbo.WrongDatatypeTab where Nome = 'Nome 1000' and Cognome = 'Cognome 1000' Set statistics io off Checkpoint dbcc dropcleanbuffers() Set statistics io on Select * from dbo.BestDatatypeTab where Nome = 'Nome 1000' and Cognome = 'Cognome 1000' Set statistics io off
Ecco la differenza in termini di I/O :
Table 'WrongDatatypeTab'.Scan count 1, logical reads 1000, physical reads 15, read-ahead reads 1006, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BestDatatypeTab'.Scan count 1, logical reads 7, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
MENO I/O facciamo e più veloci andiamo !!!!! Di conseguenza 'BestDatatypeTab' è più veloce di 'WrongDatatypeTab'.
Se su 1000 righe otteniamo questi risultati pensate su basi dati veramente molto corpose, magari con milioni o miliardi di righe....
La differenza in performance sarebbe abissale tra le 2 tabelle.
Occhio quindi quando selezionate i tipi di dato!!
Ciao
Nessun commento:
Posta un commento