SQL Server 2005

  • Articolo

Implementazione di indici efficaci

di Davide Mauri - Microsoft MVP

In questa pagina

Indici: cluster e non cluster Indici: cluster e non cluster
Indici: univoci e non univoci Indici: univoci e non univoci
Creazione di un indice Creazione di un indice
Criteri di scelta per la creazione di un indice Criteri di scelta per la creazione di un indice
Considerazioni generali Considerazioni generali
Conclusioni Conclusioni

In questi ultimi anni due degli argomenti di cui mi trovo sempre più spesso a parlare sono gli indici e la modellazione dei database. Di quest’ultimo argomento ne ho già parlato un po’ negli articoli precedenti, e quindi ora non può mancare all’appello un bell’articolo sugli indici.

Quest’articolo è dedicato a tutti coloro che vogliono capire come e quando usare gli indici, senza scendere nei dettagli tecnici dell’architettura interna degli stessi: per questo un articolo non basterebbe, e inoltre ci sono già diversi libri che ne parlano in modo molto approfondito. In particolare l’obiettivo è quello di destare l’interesse riguardo l’argomento: per chi sarà interessato ad approfondire in conclusione all’articolo ho messo diversi riferimenti in grado di soddisfare nel dettaglio tale interesse!

Il fatto che due argomenti quali indici e modellazione dei database vadano a braccetto è naturale, e questo diventa immediatamente comprensibile se ci si sofferma quel tanto che basta ad analizzare l’immagine sottostante, che rappresenta la piramide delle performance:

*

La scelta della piramide come simbologia non è casuale: la dimensione di un’area colorata lascia intendere anche quanto il tuning della stessa permetta di migliorare le performance.

Per questo motivo la base della piramide è formata dal “Database Model”, a indicare che l’impatto più grosso sulle perfomance è dato dalla struttura (ossia lo schema) del database stesso. Migliore sarà il disegno del database, migliori saranno le performance già in partenza e più ampia sarà l’area dove sarà possibile ottimizzarle in futuro.

Sopra lo strato del “Database Model” si trovano gli strati “Query” e “Index Tuning”. I due strati sono collegati tra di loro (come mostrano le frecce) da un legame davvero molto stretto: una query ben fatta è performante perché riesce a sfruttare al meglio gli indici, e un indice è ben fatto quando riesce ad essere di aiuto alle query.

Gli ultimi due strati identificano la gestione della concorrenza (e quindi del locking) e il tuning hardware e software del server che ospita il database. Sono messi in cima alla piramide perché questi due elementi, che rappresentano una complessità notevole, sono sicuramente di grande aiuto quando un database è ben fatto, ma da soli non possono fare quasi nulla se il database è disegnato male, programmato peggio e pure senza indici.

Perché sono così importanti gli indici? E’ presto detto: provate a prendere la guida telefonica e cercate tutte le persone che abitano al numero civico 7. Impresa quasi impossibile? Vero. Il motivo è legato al fatto che per soddisfare questa richiesta, non si può far altro che leggere pagina per pagina, riga per riga tutta la guida telefonica. Se invece volete eseguire una ricerca dei dati postali e telefonici di Mario Rossi è molto più facile: sfruttando l’ordinamento della guida si può in breve tempo arrivare al dato cercato.

Nel primo caso non c’è stato nessun indice ad aiutarci e quindi abbiamo dovuto leggere tutte le pagine della guida, nel secondo abbiamo usato l’indice (e quindi abbiamo sfruttato l’ordinamento) della guida telefonica per trovare immediatamente il nominativo di riferimento.

Tanto per fare un esempio pratico e chiarire quanto appena detto vi porto l’esperienza di alcuni casi reali. Molte volte mi capita di vedere database che sono definiti dagli utilizzatori “lenti” o che si “bloccano”, pur essendo messi su macchina più che soddisfacente dal punto di vista hardware.

Il database è “lento” perché, data l’assenza d’indici corretti, non può fare altro che leggere tutte le righe per capire quali sono quelle che interessano una certa query. Questa sovrabbondante lettura provoca anche un utilizzo eccessivo della memoria che non può effettuare un caching corretto dei dati. Non potendo sfruttare il caching al meglio si genera un altro traffico da e verso il sottosistema I/O che è quindi più stressato del necessario.

Se il sottosistema I/O è sovraccarico, la lettura stessa delle righe dei dati per risolvere qualsiasi query diventa più lenta, andando quindi a impattare molto negativamente sulle performance dell’intero database server, rendendo perciò le query di modifica più lunghe (in termini temporali) di quanto invece potrebbero essere, tenendo così i dati bloccati più a lungo (per poter garantire le proprietà ACID di una transazione) e aumentando in modo sensibile la probabilità di deadlock, oltre che dare all’utente finale l’impressione di aver a che fare con un sistema particolarmente lento.

Credo di aver reso bene l’idea di quanto di negativo può accadere se non si conoscono e se non si utilizzano correttamente gli indici. Parlando invece del loro utilizzo, l’aumento di prestazioni che si ottiene è davvero impressionante e, fatto molto interessante e fondamentale, è tanto più alto quanto più numerosi sono i dati sui quali dobbiamo operare! Giusto per dare una quantità spanno metrica non è infrequente osservare query che migliorano le loro performance del 300% o 400%! In termini temporali e pratici mi è recentemente capitato di ottimizzare una software di fatturazione: semplicemente agendo sugli indici si è passati a elaborare la stessa quantità di dati che prima elaborava in 18 ore in meno di 6!

Indici: cluster e non cluster

Non è scopo di questo articolo scendere nel dettaglio dell’architettura interna degli indici, per questi argomenti vi rimando ai libri indicati nel paragrafo conclusivo, ma è comunque necessario capire almeno all’atto pratico che tipologie di indici esistono in SQL Server, e quali sono le loro caratteristiche tecniche, in modo da poter sapere quale usare in funzione dei pro e dei contro che ciascuno offre.

SQL Server mette a disposizione due tipi d’indici: cluster e non-cluster.

Per comprendere il funzionamento del primo tipo d’indice è sufficiente pensare a una enciclopedia. Quando vogliamo ricercare un termine in un’enciclopedia la prima cosa che facciamo è trovare il volume che contiene il range di valori che a sua volta contiene la parola cercata. Se ad esempio cerchiamo “Database” prenderemo il tomo adatto (nel mio caso quello che contiene i termini da “Corri” a “Disc”), e successivamente andremo a trovare la pagina che contiene il termine cercato utilizzando, per evitare di leggere tutte le pagine una a una, i valori di riferimento posti come header di ogni pagina. Nell’esempio in questione trovo una pagina che contiene i valori da “data base” a “datisca”. A questo punto posso andare a leggere tutti i termini presenti in questa pagina fino a quando non trovo l’oggetto della mia ricerca, ossia “Database”. Trovato quest’ultimo ho trovato anche tutti i dati aggiuntivi a esso associati, nell’esempio preso in considerazione trovo la definizione del termine stesso.

Com’è possibile notare, l’utilizzo di un indice rende molto agevole la ricerca di un termine tra innumerevoli altri. Una caratteristica da sottolineare – che normalmente passa inosservata in quanto data per scontata – è che un indice per essere efficace deve essere ordinato. E’ grazie a questa caratteristica che è stato possibile limitare in modo considerevole il campo di ricerca, passando molto rapidamente da insiemi grossi (i volumi dell’enciclopedia) a insiemi via via sempre più piccoli (le pagine all’interno dei volumi) fino al dettaglio massimo (il dato stesso).

Per quanto riguarda gli indici non-cluster il discorso è molto simile. La grande differenza è data dal fatto che a livello di dettaglio massimo non si trovano le informazioni del dato cercato ma un puntatore a esse. Ancora una volta il paragone con il mondo reale aiuta: in questo caso è necessario pensare all’indice analitico di un libro. In questo caso la ricerca avviene andando a limitare il campo di azione utilizzando la lettera con la quale il termine che stiamo cercando inizia. Se stiamo cercando “Normalizzazione” andremo a prendere l’insieme dei termini che iniziano con “N”. Trovato questo insieme cercheremo la parola che ci interessa e troveremo così uno o più puntatori alle pagine del libro che contengono i dati dell’argomento cercato.

Con questi esempi in mente è quindi più chiara la differenza tra le due tipologie d’indici: l’indice cluster va a modificare fisicamente i dati, ordinandoli secondo il criterio di costruzione dell’indice stesso (per fare un altro esempio, nel caso di una rubrica telefonica, ad esempio, per “Cognome” e “Nome”), mentre l’indice non-cluster crea una struttura ordinata separata che non contiene tutti i dati originali ma dei puntatori a essi.

Da questa differenza architetturale ne deriva che su una tabella può esistere un solo indice cluster mentre questo limite non si applica per gli indici non-cluster (il numero max di questi ultimi è di 249).

Un indice non-cluster inoltre, richiede un’operazione aggiuntiva rispetto all’indice cluster: una volta trovati i puntatori alle pagine dati che interessano è necessario recuperare tali pagine e leggerne il contenuto al fine di trovare i dati associati all’oggetto della ricerca. Questa operazione detta “lookup” comporta un overhead di cui è necessario tenere conto per capire se e quando un indice non-cluster può far comodo oppure no. E’ anche vero, però, che un indice non-cluster è molto più piccolo di un indice cluster in quanto nelle sue righe di dettaglio non ci sono i dati ma solo dei puntatori; in effetti, la comodità di cercare nell’indice analitico di un libro (che è molto piccolo rispetto al libro stesso) è data proprio dalla sua piccola dimensione. Per capire se un indice non-cluster ci sarà utile oppure no, dovremo quindi tenere conto di tutti questi fattori.

 

Indici: univoci e non univoci

Una piccola precisazione prima di passare all’analisi pratica: un indice può essere costruito su un insieme di dati non univoci (e quindi contenenti dati duplicati) senza alcun problema. In diversi casi mi è capitato di dover chiarire questo concetto e colgo quindi l’occasione per farlo una volta per tutte.

Di solito questa convinzione si matura perché SQL Server utilizza degli indici per poter implementare i vincoli di univocità richiesti dalla Primary Key è dal vincolo Unique Key. Questo però è solo un artificio applicativo per ottenere delle performance ottimali, ma non c’è nessuna relazionetra vincoli e indici! Semplicemente SQL Server utilizza gli indici per implementare i vincoli. Tutto qui. Questo significa quindi che nel momento in cui desideriamo utilizzare un indice a prescindere dall’esistenza di un vincolo (e quindi con l’unico scopo di migliorare le performance) possiamo farlo tranquillamente anche su dati duplicati.

 

Creazione di un indice

La creazione di un indice avviene utilizzando il comando CREATE INDEX:

create nonclustered index IX_NomeCognome 
on dbo.Utenti 
(
Nome, 
Cognome
)

Per creare un indice non cluster sulle colonne “Nome” e “Cognome”, oppure:

create clustered index UIX_DataRegistrazione
on dbo.Utenti
(
DataRegistrazione
)

Per creare un indice cluster sulla colonna DataRegistrazione.

Per vedere quali indici sono presenti in una tabella potete usare la stored procedure sp_helpindex

exec sp_helpindex 'dbo.Utenti'

che è presente per mantenere la retro compatibilità con SQL Server 2000. In SQL Server 2005 ci sono diverse viste di sistema per avere informazioni sugli indici. Per comodità potete utilizzare uno script che ho creato e reso disponibile che permette di avere in modo molto comodo le informazioni richieste:

select * from sys2.indexes('dbo.Utenti')

Questo e altri script sono reperibili all’indirizzo http://www.davidemauri.it/SqlScripts.aspx, mentre il database GuidaTelefonica con la tabella Utenti per eseguire dei test lo si può prelevare da qui: http://www.davidemauri.it/files/SQL/guidatelefonica.zip (64Mb compressi, la tabella dbo.Utenti contiene poco più di 1.000.000 di righe).

Nel caso della creazione di un vincolo, come già detto in precedenza, viene implicitamente creato un indice. Per questo motivo è possibile specificare il tipo d’indice desiderato nella clausola di creazione del vincolo. Per creare una situazione tipica (e non sempre corretta), una volta effettuato il restore del database GuidaTelefonica utilizzato come esempio è necessario creare una Primary Key clustered sulla colonna Id:

alter table dbo.Utenti 
add constraint PK_Utenti primary key clustered  
(
Id
)

 

Criteri di scelta per la creazione di un indice

Clustered Indexes

Un indice cluster rappresenta, per la sua caratterista di ordinare i dati presenti nella tabella sulla quale è creato, una scelta ideale in quei casi in cui sia frequente l’utilizzo di query del tipo:

  • range search: query che effettuano ricerche in un range di valori (ad esempio “DataRegistrazione tra … e …” oppure “Cognome inizia per …”)

  • ordered resultset: query che hanno nel loro corpo la clausola order by

  • group by query: query che utilizzano la clausola group by

Perchè un indice cluster è ideale per questo tipo di query? Per la sua caratteristica di ordinare i dati: grazie a questo tutti i dati compresi nel range che interessa prendere in considerazione sono “vicini” e quindi richiedono uno sforzo minimo per essere recuperati.

Un esempio pratico si può vedere eseguendo questa semplice query, avendo cura di eseguire in prima battuta il comando SET STATISTICS IO ON, in modo da poter vedere il numero di operazioni I/O richieste per completare la query. Questo valore è fondamentale per capire se un indice è efficace o no: lo scopo dell’indice è di rendere più efficiente la ricerca, e quindi se l’indice è costruito correttamente il numero di I/O rispetto all’assenza (o al non utilizzo) dell’indice sarà indubbiamente minore.

select * from [dbo].[Utenti] where DataRegistrazione >= '20050101' and [DataRegistrazione] < '20050201'

La query richiede 14493 I/O e il piano di esecuzione, visibile attivando la seguente icona:

*

mostra che l’indice costruito su “Id” non è stato minimamente utilizzato (ovviamente in quanto la nostra ricerca è tramite “DataRegistrazione”):

*

Infatti SQL Server ha fatto uno “scan” ossia la lettura di tutte le righe della tabella.

Creando un indice cluster adatto si ottengo prestazioni di tutt’altro livello. Per prima cosa è necessario eliminare l’indice cluster già presente. In questo caso per rimuovere l’indice dobbiamo rimuovere anche la Primary Key, poiché l’indice cluster è stato costruito implicitamente per onorare questo vincolo:

alter table dbo.[Utenti] drop constraint PK_Utenti

Ovviamente la cosa corretta da fare ora sarebbe quella di ricreare nuovamente il vincolo della Primary Key utilizzando un indice non cluster, in quanto una tabella senza una Primary Key è un abominio , e a tutti gli effetti non è una tabella intesa in senso relazionale. Per amor della semplicità dell’esempio, però, posticiperemo la creazione della Primary Key a dopo, quando entreremo nel merito degli indici non-cluster.

Fatto e chiarito il tutto è ora di creare l’indice cluster:

create clustered index CIX_DataRegistrazione on dbo.[Utenti] (DataRegistrazione)

Rieseguendo la stessa query precedente, il piano di manutenzione mostra l’utilizzo dell’indice, in quanto effettua una ricerca mirata (Seek):

*

le prestazioni sono enormemente diverse: il numero di I/O ora richiesto è pari a 251! Le prestazioni in termini di I/O sono aumentate di 57 volte!!!

Non-Clustered Indexes

Per quanto riguarda invece gli indici non-cluster la regola generale è molto semplice: essi sono tanto più utili quanto le righe che la query deve restituire sono poche rispetto al numero totale di righe. In termini più tecnici un indice non-cluster è ideale quando opera in condizioni di alta selettività.

Ricordando che un indice non-cluster ha un overhead dovuto all’operazione di Lookup che deve essere fatta per ogni riga trovata nell’indice, ecco che diventa importante far si che le righe restituite dalla query siano poche rispetto al totale. Per capire quali colonne sono potenzialmente candidate per la creazione di un indice, bisogna verificare che i dati al loro interno siano molto selettivi, ossia il rapporto tra numero di valori distinti e numero totale di righe sia il più alto possibile, cioè 1, ovvero il 100% per selettività cosi calcolata.

Il caso perfetto è quindi rappresentato dalle colonne che formano la Primary Key o una qualsiasi Candidate Key (in quanto, per definizione, non ci potranno essere valori uguali). Nel nostro caso possiamo ricreare il suddetto vincolo usando il seguente comando:

alter table dbo.Utenti add constraint PK_Utenti primary key nonclustered 
(
Id
)

Fatto ciò possiamo verificare la bontà dell’indice facendo una query di prova:

select * from dbo.Utenti where id = 706018

che necessità di solamente 6 operazioni di I/O per recuperare le informazioni. In particolare le 6 operazioni di I/O sono cosi suddivise: 3 sono necessarie per esaminare l’indice non-cluster alla ricerca del puntatore che specifica in quale pagina sono contenuti i dati della riga con Id 706018. Le altre 3 operazioni sono necessarie per recuperare la suddetta pagina (per inciso in questo caso c’è una sola pagina contenente i dati ricercati).

E’ chiaro che se i puntatori trovati dalla prima operazione di ricerca nell’indice non-cluster fossero stati 4000, il numero di I/O sarebbe stato 3 + 4000 * 3 = 12003. Un numero sicuramente troppo alto per rendere efficiente, da parte di SQL Server, l’utilizzo dell’indice; l’operazione di scansione di tutte le pagine dei dati è comunque abbastanza performante (grazie all’uso della cache e di meccanismi di read-head dei dati) e quindi il database potrebbe preferire quest’ultima soluzione rispetto alla più computazionalmente complessa ricerca tramite l’indice non-cluster.

Appare pertanto evidente che se la selettività della colonna è quindi molto importante per iniziare a capire su quali colonne ha senso o meno mettere un indice non-cluster, è altrettanto importante, però, tenere conto della selettività della query.

SQL Server, in effetti, lavora molto su quest’ultimo parametro, che rappresenta il numero di righe restituite dalla query in esame rispetto al numero di righe totali. Per questo motivo, nel caso in cui le colonne sulle quali è stato costruito un indice non siano molto selettive l’utilizzo dello stesso dipenderà dai valori specificati nella clausola where, e dalla distribuzione dei valori all’interno della tabella.

Se ad esempio andiamo a creare un indice su Cognome e Nome:

create nonclustered index NCIX_CognomeNome on dbo.Utenti (Cognome, Nome)

e poi proviamo a eseguire le seguenti query:

select * from dbo.Utenti where Cognome = 'Monfore'

select * from dbo.Utenti where Cognome = 'Fenoff'

la prima utilizzerà l’indice appena creato, mentre la seconda no. Questo differente approccio è dovuto alla diversa distribuzione dei dati all’interno del database: ci sono molti più “Fenoff” che “Monfore”, e quindi nel primo caso SQL Server fa prima a effettuare una scansione di tutta la tabella piuttosto che usare l’indice.

E’ evidente quindi come un indice non-cluster venga utilizzato solo nel caso in cui i dati sulla quale opera sono molto selettivi, ed è quindi molto importante tenere presente questo requisito al momento della creazione dello stesso.

Nel caso in cui si crei un indice su una colonna che non può essere considerata selettiva per definizione (lo sono tutte le colonne che contengono valori univoci: pensiamo al codice fiscale, numeri di telefono, indirizzi email, ecc. ecc.) o di cui si è sicuri di un’alta selettività è bene verificare attentamente l’effettivo utilizzo degli indici, non solo al momento della loro creazione ma anche durante la vita del database in quanto i dati, se continuamente aggiunti o modificati, potrebbero subire dei cambiamenti nella loro distribuzione e quindi rendere virtualmente inutile gli indici posti su di essi.

Una volta creati, gli indici non-cluster vengono utilizzati da SQL Server anche per rendere più performanti query che a prima vista non beneficerebbero della presenza di un indice. Un esempio tipico è quello della query:

select count(*) from dbo.Utenti

Senza indici non-cluster SQL Server deve per forza fare una scansione di tutte le pagine della tabella (per un totale di 14000 operazioni di I/O circa nel caso in questione), ma con un indice non-cluster può invece effettuare la scansione delle pagine di quest’ultimo che sono sicuramente meno delle pagine dati. Il piano di esecuzione che si ottiene, infatti, prevede la scansione del non-clustered index costruito sulla colonna Id:

*

Producendo un numero di I/O pari a 2234, ossia un sesto dell’originale.

Foreign Key e Indici

Un’ottimizzazione molto importante è quella riguardante l’indicizzazione delle colonne che compongono le Foreign Key di una tabella. E’ piuttosto ovvio che un indice sulle Foreign Key sia di aiuto durante le operazioni di join; cerchiamo piuttosto di capire se esso è utile anche in altre situazioni.

Per fare un esempio concreto ipotizziamo una tabella delle promozioni:

create table dbo.Promozioni
(
IdPromozione int not null identity(1,1) primary key,
IdUtente int not null,
DataContatto datetime not null,
Note varchar(1000) not null
)

Tale tabella contiene la data, l’ora e le note relative al colloquio intercorso tra l’operatore che ha contattato l’utente e l’utente stesso a proposito di una vantaggiosissima promozione in corso nella nostra azienda.

E’ evidente che la colonna IdUtente deve poter contenere dei valori che sono coerenti con quelli presenti nella tabella dbo.Utenti; per questo motivo si crea il vincolo Foreign Key:

alter table dbo.Promozioni add constraint FK_Promozioni_Utenti 
foreign key (IdUtente) references dbo.Utenti (Id)

Ora è sufficiente creare un po’ di dati di esempio:

insert into
dbo.Promozioni
select 
IdUtente = Id,
DataContatto = getdate(),
Note = replicate('ABCDE', 200)
from 
[dbo].[Utenti] 
where 
DataRegistrazione >= '20050101' and [DataRegistrazione] < '20050601

Se ora tentiamo di cancellare una riga da dbo.Utenti, a causa della Foreign Key, per essere sicuro non di portare i dati in una situazione di inconsistenza, SQL Server deve controllare che la riga in questione non sia referenziata nella tabella dbo.Promozioni (altrimenti si genererebbero righe orfane):

delete from  dbo.Utenti where id = 1131416

Il risultato è questo:

*

Con questi dati di I/O:

Table 'Promozioni'. Scan count 1, logical reads 11782, ...
Table 'Utenti'. Scan count 0, logical reads 12, ...

Come si può notare c’è un alto carico di lavoro sulla tabella dbo.Promozioni. Per poter avere delle prestazioni ottimali anche nella suddetta tabella è quindi necessario evitare di effettuare lo “Scan” sulla stessa. Come ormai è noto per aiutare SQL Server in questo compito dobbiamo creare un indice adeguato:

create nonclustered index NCIX_IdUtente on dbo.Promozioni
(
IdUtente
)

Fatto ciò si passa da 11782 I/O sulla tabella dbo.Promozioni a 2!!!

Un’ultima nota riguardo all’uso d’indici sulle Foreign Key. Nel caso tipico in cui la Foreign Key sia parte di una relazione uno a molti, e i valori della stessa siano poco selettivi, diventa molto interessante valutare di utilizzare un indice cluster al posto di un indice non-cluster: data la sua particolare natura esso garantirebbe la “vicinanza” di tutti i valori della chiave, aiutando di parecchio le performance di tutte le query che prevedono l’operazioni di join tra le tabelle in uso.

Calculated Columns Indexes

Una funzionalità importante introdotta con SQL Server 2005 è la capacità di usufruire d’indici costruiti su colonne calcolate per risolvere con più efficienza query che altrimenti non beneficerebbero della presenza di indici.

Un esempio pratico è questo:

select * from [dbo].[Utenti] where datediff(year, [DataNascita], [DataRegistrazione]) = 13

La query mira a estrarre tutte gli utenti che al tempo della registrazione avevano tredici anni. Pure avendo un indice su “DataNascita, DataRegistrazione”:

create nonclustered index NCIX_DataNascReg on dbo.[Utenti] (DataNascita, [DataRegistrazione])

l’utilizzo di una funzione (in questo caso “datediff”) su una colonna rende di difficile utilizzo l’indice posto sulla stessa. SQL Server 2005 riesce comunque a fare un gran bel lavoro: andando a leggere tutte le righe dell’indice appena creato e prelevando da quest’ultimo i valori di DataNascita e DataRegistrazione, può comunque beneficiare della sua esistenza. Questo è più piccolo dell’intero insieme di dati (contiene infatti solo i dati delle colonne sulla quale è stato costruito), e fornisce comunque le informazioni necessarie per fare il calcolo della funzione “datediff”. A conti fatti, quindi, è più rapido da leggere che l’intera tabella (infatti nel piano di esecuzione si trova un “Index Scan” e non un “Clustered Index Scan”) e quindi SQL Server utilizza comunque l’indice anche senza fare una ricerca mirata. Fatto ciò calcola il valore risultante dalla funzione “datediff” e verifica se la riga soddisfa la condizione della clausola where. Fatto ciò utilizza il valore di “DataRegistrazione” per andare sull’indice cluster creato in precedenza proprio su “DataRegistrazione” per andare a recuperare tutte le altre colonne necessarie per risolvere la query (tutte n questo caso perchè abbiamo specificato l’operatore “star” * ).

*

Alla fine di tutto il numero di I/O necessario per eseguire la query è pari a 2734. Se non avessimo creato l’indice SQL Server avrebbe dovuto effettuare uno scan della tabella, e quindi dell’indice cluster per un totale delle “solite” 14755 I/O: non male visto che siamo 6 volte più performanti ma si può fare di meglio. Possiamo provare a creare una colonna calcolata che contenga la funzione presente nella clausola where:

alter table dbo.Utenti
add EtaRegistrazione as datediff(year,[DataNascita],[DataRegistrazione])
go

e procedere poi a creare un indice sulla stessa:

create nonclustered index  [NCIX_EtaRegistrazione] on [dbo].[Utenti] 
( [EtaRegistrazione] )
go

eseguendo ora la query definita in precedenza si ottiene un Index Seek e un piano di esecuzione più compatto e ideale:

*

Con un numero di I/O pari a 6. Lo scrivo per esteso: s-e-i. Siamo passati da circa 14000 I/O a meno di 10. Si parla di oltre 3 ordini di grandezza di differenza. Riuscite a immaginare qualche altra tecnica per avere performance migliori? Io francamente no.

 

Considerazioni generali

Prima di passare alla conclusione, devo e voglio porre l’attenzione sul fatto che gli indici hanno lo scopo di rendere molto veloce la ricerca delle righe all’interno delle tabelle di un database. Sono quindi perfetti per aumentare le prestazioni di lettura ma anche quelle di modifica: tutte le query che prevedono nel loro corpo una clausola where (i comandi update e delete) possono beneficiare della presenza di un indice ideoneo.

Gli indici, però, per essere utili devono essere ordinati, e quindi ogni modifica effettuata sui dati (insert, update o delete) deve necessariamente aggiornare anche l’indice! La cosa è fatta in automatico da SQL Server ma ha comunque un impatto potenzialmente non indifferente sulle prestazioni delle modifiche sui dati.

E bene quindi sottolineare che mettere troppi indici su una tabella è controproducente, quindi attenzione a non esagerare e, in ogni caso, prima di mettere in produzione qualsiasi tipo di modifica verificate l’impatto dell’aggiunta di un nuovo indice sull’intero sistema.

 

Conclusioni

Come già detto nel paragrafo iniziale, e come dimostrato lungo tutto l’articolo, la corretta implementazione di uno o più indici è in grado di “fare la differenza”, e di garantire a un database ed alle applicazioni che vi fanno affidamento prestazioni eccellenti, senza dover mettere mano all’hardware né al software.

E’ bene quindi investire la giusta quantità di tempo per analizzare, capire e progettare quali sono gli indici da applicare al database: il tempo cosi investito sarà ampiamente recuperato durante tutta la vita dell’applicazione, dando grandi soddisfazioni agli sviluppatori ed anche agli utenti finali.

In questo articolo abbiamo purtroppo dovuto saltare tutto ciò che riguarda la manutenzione degli indici, dell’utilizzo del Database Tuning Advisor, dei Missing Indexes e degli altri innumerevoli aspetti tecnici avanzati (Covering Indexes e Included Columns) ed architetturali legati agli stessi. Per approfondire tutto ciò e oltre, visto che l’argomento è davvero ampio oltre che importante e interessante, potete far riferimento a UGISS (www.ugiss.org) e ai seguenti testi:

Pro SQL Server 2005 Database Design & Optimization, Louis Davidson, ISBN 1-59059-529-7

Inside SQL Server 2005: T-SQL Programming, Itzik Ben-Gan, ISBN 0-7356-2197-7

Inside SQL Server 2005 – The Storage Engine, Kalen Delaney, ISBN 0-7356-2105-5