Utilizzo di GUID: pro e contro

L'obbiettivo è che questo sia il post definitivo sulla diatriba GUID vs INT: in molti sostengono che l'uso di un GUID come Primary Key sia una cosa buona e giusta, da usare nella maggior parte dei casi al posto del "solito" numero intero (tipicamente generato tramite IDENTITY) perchè più comodo e senza particolari controindicazioni. Altri invece si schierano a favore dell'INT perchè più leggero e quindi apparentemente con meno problemi di performance.

Chi ha ragione? Con quale criterio è bene usare un GUID o un INT? Dato che da troppo tempo si sentono diverse campane in merito e mai nessuno si è preso la briga di dimostrare scientificamente che impatto può avere l'uso di GUID come Primary Key al posto dei "classici" INT, è giunto il momento di farlo, in modo da porre fine alla diatriba in modo rigoroso ed inconfutabile. In questo modo anche coloro che non conoscono in profondità l'archittettura di SQL Server (se la conoscesero la risposta sarebbe già chiara) potranno avere un riscontro oggettivo, chiaro ed inequivocabile.

Premetto che sto ovviamente parlando dal punto di vista strettamente tecnico. Dal punto di vista architetturale e di design del database i motivi per cui è meglio usare l'uno piuttosto che l'altro dipendono dai requisiti funzionali che si devono soddisfare, sui quali - in certa misura - non si può discutere. Si può però discutere su cosa sia meglio scegliere se dal punto di vista funzionale non ci sono problemi e si tratta quindi di una scelta puramente tecnica.

Non sto in questa sede a ripetere le motivazioni riportate in un articolo già pubblicato su MSDN, e passo quindi subito a concentrarmi sulla simulazione di una situazione reale. Ipotizziamo di usare un GUID come Primary Key delle nostre tabelle, e quindi ipotizziamo di avere una tabella del genere:

create table dbo.TestGuid
(
    id uniqueidentifier not null primary key default(newid()),
    dt1 datetime not null,
    dt2 datetime not null,
    txt1 varchar(16) not null,
    txt2 varchar(24) not null,
    int1 int not null,
    int2 int not null,
    fk uniqueidentifier not null
);
go

create nonclustered index uix__TestGuid__fk on TestGuid(fk)
go

Com'è possibile notare si sta anche prendendo in considerazione il fatto che un Primary Key esiste per essere usata, e quindi la suddetta tabella ha anche un Foreign Key - anch'essa GUID - che fa riferimento alla Primary Key di un'altra ipotetica tabella usata nel nostro DB di prova. Come spesso accade (o dovrebbe accadere), su tale FK è creato un indice Non-Cluster.

Questo approccio di permette di vedere in una situazione reale, seppur semplificata (in quanto la tabella contiene poche colonne e solo un indice Cluster ed uno Non-Cluster, quando invece la realtà è tipicamente molto più complessa) come i GUID impattino sul nostro sistema.

Ma che tipo di impatto vogliamo controllare? Dobbiamo tenere in considerazione tutte  le problematiche tecniche che gravitano attorno ad una tabella, e quindi dobbiamo misurare il grado di performance e la quantità di manutenzione indotta. In questo caso manutenzione significa necessità di re-indicizzare una tabella in modo da mantenere gli indici e quindi le operazioni di I/O sempre efficienti.

La tabella che utilizza la soluzione basata su Integer - utilizzata come confronto - è fatta invece in questo modo:

create table dbo.TestInt
(
    id int identity not null primary key,
    dt1 datetime not null,
    dt2 datetime not null,
    txt1 varchar(16) not null,
    txt2 varchar(16) not null,
    int1 int not null,
    int2 int not null,
    fk int not null
);
go

create nonclustered index uix__TestInt__fk on TestInt(fk)
go

Com'è possibile notare le uniche cose che cambiamo sono i tipi di dato usati per la PK e per l'ipotetica FK utilizzata.

I test sono fatti su SQL Server 2008 in modo da poter usare i nuovi tipi di dato che sono molto più precisi dei precedenti, permettendoci una risoluzione di 100ns. Il test consiste nel caricamento di 100.000 righe, misurando la velocità dello stesso e la frammentazione dei dati alla fine del processo.

Questo lo script per caricare la tabella con i GUID:

set nocount on;

truncate table dbo.TestGuid;

declare @ts as time(7) = sysdatetime();

insert into
    dbo.TestGuid with (tablockx) (dt1, dt2, txt1, txt2, int1, int2, fk)
select
    dt1 = '20000101',
    dt2 = '20080101',
    txt1 = 'ABCDEFGHILMNOPRS',
    txt2 = 'ABCDEFGHILMNOPRS',
    int1 = 123456789,
    int2 = 123456789,
    fk = '0D5C533A-A351-4D71-8DF6-120A464CA4CE'
from
    dbo.fn_Nums(100000)
option (maxdop 1)

declare @te as time(7) = sysdatetime();

select datediff(ms, @ts, @te);
go

e questo quello per caricare la tabella con gli INT:

set nocount on;

truncate table dbo.TestInt;

declare @ts as time(7) = sysdatetime();

insert into
    dbo.TestInt with (tablockx) (dt1, dt2, txt1, txt2, int1, int2, fk)
select
    dt1 = '20000101',
    dt2 = '20080101',
    txt1 = 'ABCDEFGHILMNOPRS',
    txt2 = 'ABCDEFGHILMNOPRS',
    int1 = 123456789,
    int2 = 123456789,
    fk = 1
from
    dbo.fn_Nums(100000)
option (maxdop 1)

declare @te as time(7) = sysdatetime();

select datediff(ms, @ts, @te);
go

L'hint option (maxdop 1) è utilizzato per essere al 100% sicuri che in nessun caso SQL Server possa scegliere di parallelizzare in modo differente le due query, mentre l'hint tablockx ci assicura che i lock vengano acquisiti sull'intera tabella si dall'inizio e quindi non intervenga il meccanismo di lock-escalation che potrebbe inficiare le prestazione della query nel caso della presenza di altre query concorrenti nel nostro sistema di test.

Effettuanto per 50 volta ciascuna query, le performance sono le seguenti:

Query di insert di 100.000 righe nella tabella TestGuid: 2342ms

Query di insert di 100.000 righe nella tabella TestInt: 1497ms

i test sono stati eseguiti sia utilizzando semplicemente il Management Studio (tramite il comando go 50), sia tramite SQLQueryStress, un tool fondamentale in questi casi:

TestInt TestGuid

Nell'inserimento massivo di dati, la tabella TestInt risulta più performante di oltre il 50% (!!!!!!) Non stiamo quindi parlando di noccioline.....

Bene, ok per l'inserimento di un certa quantità di righe in un sol colpo, ma per quanto riguarda l'inserimento di una singola riga? Tra le due soluzioni non c'è nessuna differenza apprezzabile. Il che è piuttosto semplice da spiegare: l'overhead necessario per il compimento della transazione maschera il peso (piccolo rispetto al peso della transazione) dell'operazione di insert, che quindi non è percepibile, mentre tale differenza si manifesta molto bene quando ci sono molti inserimenti ed una sola transazione, in quanto si verifica la condizione opposta, ossia il peso della transazione diventa trascurabile rispetto al peso delle insert.

Detto ciò, passiamo ad analizzare la frammentazione dei dati? Utilizzando la DMF sys.dm_db_index_physical_stats è possibile analizzare la struttura fisica degli indici presenti sulle tabelle:

select table_name = object_name([object_id]), index_id, index_type_desc, index_depth, avg_fragmentation_in_percent, page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') where index_level = 0 and object_name([object_id]) in ('TestGuid', 'TestInt')
go

IndexInfo

Come potete notare su questi dati è bene riflettere:

La frammentazione degli indici sulla tabella TestGuid è pari al 99%. (Terrificante)

Per contenere la stessa quantità di dati, sulla tabella TestGuid l'indice cluster necessità di 1882 pagine anziche di 576 mentre l'indice non cluster di 981 invece di 136 (!!!!!!)...il che implica che l'efficienza degli indici nella tabella TestInt è rispettivamente pari al 92% in più per l'indice cluster e del 348%  in più per quanto concerne l'indice non-cluster.

E' bene anche notare che l'indice Non-Cluster nella tabella TestInt ha un profondità di due livelli anzichè tre, e ciò implica che l'operazione di Bookmark Lookup, per tutte le query che ne dovessero fare uso, sia meno onerosa rispetto all'indice non-cluster sulla tabella TestGuid, abbassando quindi il valore soglia di selettività necessaria per usare tale indice. Detto in altre parole, ma come già si dovrebbe sapere, un indice non-cluster più magro è più "appetibile" per SQL Server, che quindi lo userà con più probabilità.

La tabella TestGuid ha quindi bisogno di molta più manutenzione, dato che si frammenta molto più velocemente che la tabella TestInt: il che significa dover re-indicizzare molto più spesso, cosa che può essere problematica a meno di non avere una licenza Enterprise e quindi poter fare l'online re-indexing (il che significa spedere molti più soldi...)

Dal punto di vista delle performance, inoltre, la pagine sono piene in media solo al 68% nella tabella TestGuid, mentre sono piene al 99% nel caso della TestInt. Ciò significa che una qualsiasi operazione di lettura è più dispendiosa nella tabella TestGuid, in quanto per leggere lo stesso numero di righe devo fare più I/O...il che può significare andare a creare colli di bottiglia proprio al sistema di I/O, che può quindi diventare un problema per tutto il sistema.

Sempre dal punto di vista delle performance, è bene sottolineare che il valore di "avg_fragmentation_in_percent" rappresenta la quantità di pagine il cui ordine fisico è diverso dall'ordine logico. In parole più semplici: la sequenza in cui le pagine sono state scritte sul disco, per ragioni di performance di scrittura, è diversa da quella in cui l'indice cluster le vorrebbe organizzate (ordine logico). Insomma, è la cara vecchia "frammentazione" che il dottor Norton ci ha sempre detto essere male. Questo comporta un superlavoro per il sistema I/O che ne risente ed infatti porta ad avere una latenza di accesso ai dati più alta (sul mio notebook di test, l'aumento era di un ordine di grandezza...il che significa passare da una situazione buona, ad es 30msec ad una semplicemente pessima, ossia 300msec) con un impatto decisamente negativo sulle performance di lettura (si passa da 0,3 sec per una lettura di tutte le righe ad 1,6 sec....ossia 5 volte di più, ovviamente per una lettura "a freddo" ossia senza l'utilizzo della cache)

Ma non è finita qui. Dato che come ogni database serio SQL Server fa abbondante uso di cache, dobbiamo fare i conti anche con la memoria disponibile sul sistema. Quanto "pesano" quindi gli indici? Dato che finiranno in cache è bene saperlo, perchè la cache è ordini di grandezza inferiore allo spazio su disco disponibile, ed un abuso della cache per indici "grassi" comporta un sovraccarico del sistemo di I/O che come detto prima può diventare un bel collo di bottiglia. Vediamo i dati, utilizzando sempre la DMF di prima:

select
    index_name = name,
    type_desc,  
    space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes i
inner join
    sys.dm_db_index_physical_stats(db_id(), null, null, null, null) P on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]
order by
    i.index_id
go

ecco i risultati:

IndexWeight

L'indice cluster sulla tabella TestGuid pesa il doppio di quello sulla tabella TestInt, mentre l'indice non-cluster pesa  quasi il quadruplo!!!!

In totale stiamo usando circa 19 MB per la tabella TestGuid al posto dei 9 MB della tabella TestInt. Stiamo in pratica abusando della cache del 50% o, se volete, la stiamo usando al 50% dell'efficenza, il che si traduce nel fatto che per avere le stesse performance dobbiamo usare il doppio della memoria.

Con conclusioni possiamo tirare quindi? Che il GUID ha un costo non idifferente, in termini di performance e di manutenzione, che prima o poi impatteranno sul budget hardware, rendendo necessario spendere più soldi per avere performance che altrimenti potremmo aver avuto gratis.

Sono quindi di evitare, in favore di un uso assolutistico di INT IDENTITY come Primary Key? NO, NO, e poi NO. Questo post non vuole mettere paura, ma mira a promuovere un utilizzo consapevole. Se per esigenze funzionali è necessario usare un GUID facciamolo, sapendo che però ha un costo. Quello che non bisogna mai fare è usare qualcosa senza sapere che impatto può avere nella nostra soluzione, sia ora sia in futuro. Ricordate infatti che un db vive molto più a lungo di un'applicazione, e quindi come "database architect" (o dba o muratori del db o ingegneri dei dati, chiamatevi un pò come volete, ma ricordate che disegnare un database è un'operazione di grande responsabilità), abbiamo il dovere di essere più lungimiranti di altri.

Ma quindi se è necessario usare un GUID non c'è modo di salvare capra e cavoli? Posto che sull'articolo su MSDN citato in precedenza è dimostrato come non sempre l'utilizzo di GUID è necessario, anche se in prima battuta può sembrarlo, se state usando SQL Server 2005 o superiori, un buon compromesso può essere l'uso di SEQUENTIALGUID, in modo che il problema della frammentazione dei dati sia quantomeno evitato, in quanto vengono generati valori GUID sequenziali, monotonici crescenti, e quindi al riparo dal problema della frammentazione. Rimane il fatto che un GUID rende gli indici "cicciotti" e quindi poco efficienti in termini di caching e I/O, ma se non ci può fare altrimenti, questo rappresenta un buon compromesso. Il migliore attualmente, IMHO.

Credo che ora la diatriba "GUID si / GUID no" possa avere termine...ognuno con questo post può giudicare se la spesa in termini di prestazioni e manutenzione vale la candela nel proprio gioco oppure no. Tutto il resto sono ipotesi, illazioni, castelli di sabbia. L'unico modo per poter verificare una cosa è usare un ben noto metodo: quello scentifico. Lasciate stare chi parla semplicemente per supposizioni: in ambito tecnico, il campo del database non è solo quello informatico ma anche e soprattutto quello matematico, in cui non c'e spazio per altro che non una dimostrazione rigorosa. Fidatevi solo di coloro che ve la forniscono o provatela voi stessi. Per tutto il resto Fabian Pascal ha già detto tutto in merito: http://www.dbdebunk.com/page/page/3161496.htm.

Published martedì 23 dicembre 2008 23.59 by dmauri

Comments

# re: Utilizzo di GUID: pro e contro

mercoledì 24 dicembre 2008 17.05 by orsocurioso

Grande !!! Misurare, misurare e misurare e quello che non e' misurabile.. renderlo tale !!

# re: Utilizzo di GUID: pro e contro

domenica 28 dicembre 2008 10.43 by theswra

Ciao Davide.

Non capisco dove hai usato tablockx (pbmente lo hai omesso).

Inoltre hai riportato male un paio di dati (anche se non lede la comprensione dell'articolo). Ad esmepio: "981 invece di 136", anche se poi riporti le percentuali corrette.

Ora veniamo alle domande serie :)

Nel post di Raffaele, Silvano dice che "l'indice cluster per la ricerca di un valore univoco come la chiave primaria è tipicamente meno efficiente di un indice non clustered". Questo sarebbe causato dal fatto che, in genere, un indice clustered aumenta la frammentazione?

# re: Utilizzo di GUID: pro e contro

domenica 28 dicembre 2008 10.45 by theswra

Altro dubbio: quante volte capita di dover inserire 100k record in un solo botto? D'accordissimo con te sulle performance della singola select o l'uso e lo spazio occupato dagli indici, ma dell'insert mi preoccuperei di meno.

# re: Utilizzo di GUID: pro e contro

domenica 28 dicembre 2008 11.09 by theswra

Rieccomi ;)

Ho letto il tuo tip su MSDN. Usi una pk composta. Ma molti criticano la scelta di usare una chiave composta, perché troppo "legacy". Tu lo fai di frequente? Ad esempio in tabelle di correlazione (classico: utenti-ruoli) useresti come pk un int identity o una composta pk_utente-pk_ruolo?

Grazie, a.

# re: Utilizzo di GUID: pro e contro

domenica 28 dicembre 2008 17.52 by dmauri

@theswra

Grazie mille per le segnalazioni, ho fatto le correzioni che mi hai segnalato :-)

Sul primo dubbio: in funzione del progetto che stai seguendo può essere o meno frequente che tu debba importare quantità di dati non indifferenti, spostandole da una tabella ad un'altra. Non è cmq questo il problema. Come sottolineo nell'articolo sulla singola insert i due approci hanno praticamente le stesse performance. L'utilizzo dell'importazione massiva è semplicemente un modo per evidenziare che su grosse quantità di dati - invece - non è possibile ignorare le differenze.

Il punto importante però, non è tanto la performance dell'insert, quanto l'enorme frammentazione generata.

Siamo quindi daccordo, se guardiamo solo e solamente singola insert non ci sono problemi :-) Il fatto è che un database è come il gioco dello shangai....ogni cosa che facciamo a ripercussione sulle altre, ed in questo caso la frammentazione in fase di accesso ai dati gioca un ruolo molto importante.

Sulla chiave composta: non credo si possa avere una regola generale in questi casi, tutto dipende dalla modellazione del database (e quindi della eseguenze funzionali e dalla rappresentazione della realtà che dobbiamo fare). E' sicuramente vero che l'uso di chiavi composte sia "scomodo", visto che complica la scrittura delle query. L'articolo mostra semplicemente che non è vero come (come molti credono) che l'unico modo per non avere conflitti in applicazioni disconesse o parzialmente connesse sia quello di usare GUID. Questo significa che bisogna semplicemente tenere presente che non c'è solo una strada ma più di una, e quindi di volta in volta potremo scegliere quella più comoda da seguire. Insomma: siamo *liberi* di scegliere e non costretti dalla tecnologia (come invece accadeva di frequente in passato).

Sull'uso frequente di chiavi composte: dato che non c'è una regola, non è una cosa che faccio di "frequente", ma è una cosa che faccio ogniqualvolta il tradeoff tra complessità - tempi di sviluppo - integrità dei dati - facilità di manutenzione - performance mi sembra favorevole.

Soprattutto questo discorso è importante: a parte le evidenze tecniche, tutto il resto (ossia l'architettura ed il design) è ovviamente un trade-off, quindi non ci possono essere regole sempre valide, poichè ogni business ed ogni requisito è diverso.

# re: Utilizzo di GUID: pro e contro

domenica 28 dicembre 2008 18.07 by theswra

tx