WHITE PAPER

10 SUGGERIMENTI PER AUMENTARE LE PERFORMANCE DI SQL SERVER SENZA SPENDERE 1 EURO COME MASSIMIZZARE LE PRESTAZIONI DI SQL CON L’HARDWARE ESISTENTE

QUALI SONO LE SOLUZIONI DI SUCCESSO? .................................................................................................. 2

IN QUESTO WHITE PAPER ......................................................................................................................... 4

LA RAM ...................................................................................................................................................... 5

IL DISCO ..................................................................................................................................................... 5

1. BEST PRACTICE CONFIGURATION SQL SERVER MEMORY ..................................................................... 6

2. OFFSET PARTITION ................................................................................................................................ 8

3. DIMENSIONI DELLE UNITA’ NTFS .......................................................................................................... 9

4. CONFIGURAZIONI RAID DEI DISCHI ..................................................................................................... 10

5. SCELTE OTTIMALI DEI DISCHI .............................................................................................................. 12

6. JUMBO FRAME .................................................................................................................................... 13

7. AGGIORNAMENTO STATISTICHE ......................................................................................................... 15

8. DEFRAMMENTAZIONE INDICI ............................................................................................................. 16

9. RIMOZIONE DEGLI INDICI INUTILIZZATI .............................................................................................. 17

10. CREAZIONE E OTTIMIZZAZIONE DEGLI INDICI ................................................................................... 19

L’AUTORE

Mi chiamo Renzo Sotgia e lavoro nel settore informatico dal 1998. In qualità di consulente informatico

ho seguito progetti di media o lunga durata che mi hanno coinvolto sia per la mia esperienza di System

Engineer e di DBA su SQL Server. Per quest’ultimo nutro una passione che, negli anni mi ha permesso di

approfondire molti aspetti e nuove caratteristiche che questo prodotto ha negli anni aumentato e

migliorato.

Le tecniche qui riportate mi hanno permesso di avere buone soddisfazioni nel gestire i progetti di

implementazione e ottimizzazione in cui sono stato coinvolto.

Il lavoro che svolgo spesso non viene riconosciuto per gli sforzi e la preparazione tecnica che servono a

portare a termine un progetto. Questo perché, a volte i clienti con cui ho a che fare non hanno

background tecnico, altre volte perché, in qualità di cliente, chi si rivolge al professionista si aspetta il

miglior risultato richiesto con il minor costo (della serie poca spesa tanta resa…).

Un aneddoto che ho sempre impresso in mente e descrive meglio il concetto è il seguente:

Questa è la fattura di un consulente per aver risolto un problema bloccate di una grossa azienda che a

causa di un guasto ad un macchinario ha dovuto interrompere la produzione da diversi giorni

INTERVENTO PER MANUTENZIONE

STRAORDINARIA

CLIENTE:

CONIGLIETTO ENTERPRISE

FATTURA N. xx

DETTAGLIO COSTI E MATERIALE UTILIZZATO

1 VITE PREZZO UNITARIO € 1

SAPERE QUALE VITE SOSTITUIRE PREZZO UNITARIO € 1000

TOTALE € 1001

QUALI SONO LE SOLUZIONI DI SUCCESSO?

Le soluzioni di successo hanno successo solo se sistemi, utenti e applicazioni dipendono dal buon

funzionamento di queste.

Non necessariamente perché ben realizzate o progettate intelligentemente in grado di soddisfare le best

practice teoriche consigliate.

In sostanza, le performance diventano un problema quando il successo di una soluzione comincia a

sopraffare la sua capacità portante.

Alcune applicazioni raggiungono questo punto quasi subito: o perché hanno più successo del previsto, o

perché orribilmente progettate.

Di conseguenza, le applicazioni veramente di successo sono il tipo che richiedono attenzione perché

sono in continua evoluzione e adattamento per soddisfare le esigenze di business senza interruzioni di

servizio.

Una componente vitale per avere e mantenere soluzioni di successo è quella di stare al passo con i requisiti

di prestazione. Mentre un modo per farlo è quello di migliorare sempre più l’ hardware, un altro (migliore)

è quello di sfruttare al meglio l’hardware esistente, aumentando così le prestazioni di SQL Server.

Come consulente SQL Server e DBA ho messo mano a soluzioni progettate, realizzate e gestite in maniera

“discutibile” e poco performanti ma, dopo diversi accorgimenti sono diventate mission-critical.

Con queste soluzioni il problema successivo, così come con soluzioni di successo progettate

intelligentemente, è che quando il successo attira troppi utenti e/o applicazioni, queste non sono

dimensionate per un alto carico di lavoro.

IN QUESTO WHITE PAPER In questo white paper verranno descritti quegli accorgimenti per poter arrivare a tale traguardo,

sfruttando cioè al massimo una infrastruttura hardware e di conseguenza gli investimenti economici

affrontati.

Quasi tutte le applicazioni di successo possono beneficiare di prestazioni tecniche descritte in questo

documento. Tuttavia, le tecniche descritte qui dovrebbero servire come lista di controllo per determinare

se è il momento di prendere in considerazione l’aggiunta o cambio di hardware o altre risorse (quali

consulenti, soluzioni di terze parti e così via).

I temi trattati in questo white paper sono descritti in numerosi libri, articoli e post di blog.

Di conseguenza, questo documento non fornisce una panoramica esaustiva, ma contribuisce ad

accrescere la consapevolezza delle potenziali performance che possono essere messe a punto, fornisce

informazioni di base e di contesto per valutare l'efficacia di queste tecniche. Delinea qualche approccio

chiave e i vantaggi prestazionali che ne derivano, da attuare se in grado di migliorare le performance.

LA RAM Mentre è comune avere dei database superiori a 100GBs di dimensione, non è così comune trovare

questi database ospitati su server con più di 100 GB di RAM ( ). Ancora più probabile è trovare dei

database performanti che girano su sistemi con poca RAM rispetto alla quantità di gigabyte totale di dati

e che quindi non può stare tutta in memoria.

Se la RAM fosse più conveniente, allora molti problemi di prestazioni cesserebbero di esistere

semplicemente perché SQL Server potrebbe contare su tutta la RAM di cui necessita invece di restare sul

disco fisso (che è esponenzialmente più lento di RAM).

Di conseguenza, un buon modo per mantenere i server performanti è quello di assicurarsi che stiano

utilizzando quanta più RAM possibile.

IL DISCO Il disco, o sotto-sistema di I/O, gioca un ruolo fondamentale sia nella disponibilità del database che di

prestazioni. Pertanto, assicurandosi che il sottosistema di I/O sia correttamente configurato si potranno

offrire vantaggi prestazionali enormi. Purtroppo, correggere i problemi con l'I/O può essere laborioso,

oltre al fatto che modificare i dischi mal configurati richiederà la rimozione dei dati da tali dischi in modo

che le correzioni possono essere fatte. In altre parole, i dati hanno bisogno di essere sottoposti a backup

e spostati in altra posizione o supporto (temporaneo) di archiviazione. Quindi la configurazione dei

dischi può essere ottimizzata e i dati possono essere ripristinati nella posizione originaria.

Questa ottimizzazione potrebbe non essere possibile in alcuni ambienti, in cui il tempo di inattività

necessaria potrebbe non essere accettabile. Ma in ambienti in cui il 'lusso' di tempo di inattività di

programmazione è disponibile, correzione di questi problemi possono migliorare sostanzialmente le

prestazioni senza costi aggiuntivi di nuovo hardware.

1. BEST PRACTICE CONFIGURATION SQL SERVER MEMORY Gestione della RAM su sistemi a 32 bit

Uno dei problemi con i sistemi x86 è che i puntatori a 32 bit possono arrivare ad indirizzare solo 4GB di

memoria fisica. Inoltre, nei sistemi 32 bit, le applicazioni sono limitate a utilizzare solo 2 GB di spazio di

indirizzamento per l'utente così come il sistema operativo rivendica per sé altri 2GB.

Per i sistemi a 32 bit, Microsoft ha previsto il Physical Address Extensions (PAE) che consente alle

applicazioni di arrivare fino a 3GB di RAM (costringendo il sistema operativo ad utilizzarne solo 1 GB).

Sistemi con più di 4GB di RAM fisica non possono essere gestiti dal sistema operativo. Di conseguenza,

per sistemi più grandi, è stato creato il Windows Address Extensions (AWE) per 'estendere' la quantità, o

dimensione, di memoria indirizzabile per consentire alle applicazioni a 32 bit di sfruttare un massimo di

128GB di RAM su versioni di Windows Enterprise e Data Center a 32 bit. L'unico inconveniente per la

memoria AWE è che non è gestita dinamicamente da Windows e la memoria sarebbe usata come spazio

di paging. Di conseguenza, per applicazioni che richiedono più di 2 GB di RAM è necessario concedere il

'lock pages in user memory' attraverso i criteri di protezione locali.

Purtroppo, la procedura guidata di installazione per le versioni a 32 bit di SQL Server non mette in

evidenza la possibilità di configurare AWE durante l'installazione. Inoltre, anche se gli amministratori di

istanza o di database concedessero l'accesso di SQL Server a più di 2GB di RAM tramite Enterprise

Manager, SQL Server Management Studio, o T-SQL, tali direttive non potrebbero essere onorate da SQL

Server finché all’ account del servizio non viene autorizzato all’ utilizzo dell’opzione ‘lock pages in

memory’.

Per verificare questo problema su host a 32-bit, aprire il Task Manager di Windows per determinare se il

sistema dispone di più di 2 GB di memoria fisica e vedere se il processo di SQL Server usa solo 2GB di

memoria. Questo è indicativo del problema e sarà piuttosto facile da correggere. Basterà concedere,

tramite local policy, al servizio di SQL Server 'lock pages in memory', riconfigurare SQL Server per

utilizzare più RAM tramite l’opzione di AWE (se non lo si ha ancora fatto), e riavviare il servizio SQL

Server in modo che possa cominciare a sfruttare tutta la RAM aggiuntiva. Ho fornito le istruzioni passo-

passo per correggere questo problema, e porre rimedio a questo problema vi fornirà enorme benefici di

prestazioni con il minimo sforzo ( ).

Gestione della RAM su sistemi a 64 bit

Come host a 64-bit possono gestire logicamente exabyte di memoria RAM, limite non raggiungibile dai

sistemi a 32-bit. In questo caso l’uso della funzione ‘lock pages in memory’ era (o è ancora per le istanze

a 32-bit) utilizzata dagli amministratori, al fine di sfruttare tutta la RAM fisica dell’ host.

Tuttavia, con Windows Server 2003/2003 R2, ci sono alcune situazioni in cui i “padroni di casa”, seppur

con grande quantità di RAM (tipicamente da 32GB in su), possono incorrere in working set di SQL Server

spontaneamente rimossi dalla RAM in maniera apparentemente casuale e che può essere devastante

per le prestazioni generali di SQL Server. In casi come questo, istanze di SQL Server 2005 SP2 e

successive una parte significativa del processo server SQL memoria è stata ‘paginata’. Di conseguenza, la

correzione di questo problema particolare è quello di sfruttare il 'lock pages in memory' (proprio come

con gli host a 32-bit), anche se questo work-around richiede anche l'uso di uno speciale flag di traccia

sulle edizioni Standard di SQL Server precedenti a SQL Server 2008 SP1.

Per ulteriori informazioni e dettagli, si prega di consultare il seguente:

http://support.microsoft.com/kb/918483

2. OFFSET PARTITION

Con Windows Server 2008, Microsoft ha iniziato ad affrontare automaticamente un comune e mal

documentato problema di prestazioni IO: Allineamento Partizione Offset.

Per i sistemi in esecuzione sui vecchi sistemi operativi (Windows Server 2003 R2 e inferiori), la gestione

di questa caratteristica deve essere manuale.

Essendo questa poco conosciuta raramente è stata affrontata e gestita.

Come Kevin Kline1 sottolinea nel suo articolo molto pubblicizzato in SQL Server Magazine, una

spiegazione troppo semplicistica della Partizione Offset è che prima di Windows Server 2008, il primo

blocco di dati scritti su una partizione in termini di dimensioni era circa 63k. Eppure al sistema operativo

piace scrivere blocchi di dati logici da 64k su settori di disco fisico da 64K.

Perciò, senza allineare in modo esplicito le partizioni, una singola scrittura di 64k di dati sarà estesa a

due settori da 64k. Il problema è grave come si può facilmente intuire, e può portare ad una

penalizzazione delle prestazioni del 30-40% in molti casi.

Ad un livello più tecnico, l'allineamento di offset è semplicemente un residuo di quando i server Windows

non erano pensati per girare su dischi in configurazione RAID, e quindi non sapevano come gestire i settori

dei sistemi RAID sottostante. Windows Server 2008 risolve questo problema di imporre automaticamente

un offset di 1024K, che dovrebbe essere sufficiente per la maggior parte dei sistemi.

Purtroppo, non esiste un modo facile per individuare il problema sui sistemi più vecchi. Tuttavia questo

sarà un qualcosa da esaminare se si verificano problemi di prestazioni IO. Questo argomento ha ricevuto

molta attenzione negli ultimi tempi, e Microsoft ha fornito un fantastico white paper che tratta questo

argomento in profondità, insieme a best practice e soluzioni. L'unico inconveniente di correzione

Per questo problema è che il ri-allineamento di partizione offset rimuoverà tutti i dati sui dischi, quindi

dovrebbe essere fatto solo quando si ha il tempo di inattività per spostare tutti i dati dai dischi prima di

fare apportare le modifiche.

1Kline, Kevin (6/10/2008). How to Improve Application and Database Performance up to 40% in One Easy Step, SQL Server

Magazine. Retrieved July 2, 2012, from http://www.sqlmag.com/article/sql-server/how-to-improve-application-and-database-

performance-up-to-40-in-one-easy-step

3. DIMENSIONI DELLE UNITA’ NTFS

Concettualmente simile ai problemi con l'allineamento di partizione offset, il dimensionamento di

allocazione su dischi NTFS può avere un ruolo importante nel modo in cui Windows e il sottosistema IO

possono 'ritagliarsi' nuovi pezzi di disco durante la scrittura dei dati. Di default Windows tenta di allocare

nuove unità a blocchi di 4 KB; sistema adatto per i file server con un sacco di piccoli file. Dal momento

che SQL Server legge e scrive dati in 8K pagine e extents da 64K (cioè 8 pagine da 8K), le unità di

allocazione da 4K in genere finiscono per essere troppo piccolo per essere ottimali.

Microsoft consiglia in genere un’ allocazione di 64KB dell'unità per i dati, i registri, e tempdb

(http://technet.microsoft.com/en-us/library/cc966412.aspx).

Tuttavia, va notato che una dimensione di allocazione a 64K non può essere perfetto per ogni sistema.

Nei casi in cui la raccolta di dati (Es. quando SQL Server sta eseguendo un’operazione di ricerca in piccoli

blocchi di dati non contigui) è frequente, pagine di 8KB possono avere più senso, specialmente se le

scritture sono meno rispetto alle letture.

D'altra parte, nei casi in cui accesso IO sequenziale è più diffuso, come nelle soluzioni di reporting o di

analisi, grandi unità di allocazione (oltre 64K) possono avere più senso.

Anche se la dimensione di unità di allocazione è semplice (basta specificare il valore desiderato dalla

ripartizione Unità di formato durante la formattazione di un disco), il problema è che questa richiede la

formattazione dei dischi. Pertanto, questa soluzione è meglio implementarla assieme ad una delle

modifiche elencate sopra. Qui è stata inclusa solo per amore di completezza. Questa ottimizzazione non

avrà un drammatico impatto sulla maggior parte dei sistemi, a differenza dei punti 2 e 4.

4. CONFIGURAZIONI RAID DEI DISCHI

Se avete dovuto gestire block-size e partition offset ma ci sono ancora problemi di prestazioni I/O, un

altro fattore chiave è che non tutte le forme di RAID sono uguali.

RAID-0 è estremamente pericoloso in qualsiasi ambiente di produzione SQL Server e non dovrebbe

essere usato. Usarlo per approssimare velocità a livello di produzione in ambienti di test in alcuni casi

può avere senso, altrimenti, evitare del tutto RAID-0 per ambienti di produzione. E non cadere preda alla

nozione che è possibile utilizzare un RAID-0 per il tempdb. Se un solo disco fallisse SQL Server

smetterebbe di funzionare, dal momento che non può funzionare senza un tempdb.

RAID-1 offre la tolleranza ai guasti di base scrivendo copie dei dati su più unità. In genere, la velocità di

scrittura su sistemi con RAID-1 sono le stesse con la scrittura di una singola unità (in teoria c'è un po’ di

overhead a livello di controller, ma con la maggior parte dei controller attuali il problema è trascurabile),

mentre le velocità di lettura sono potenzialmente aumentate visto che i dati possono essere estratti da

più unità quando necessario.

RAID-5 offre un eccellente tolleranza perché scrive sul numero di dischi del RAID (meno 1), e quindi

scrive i dati di parità sulla restante unità dell'array. Questo significa che i dati su un'unità guasta possono

essere ricostruiti estraendo i dati dai restanti dischi comparati con i dati di parità, o 'Firma', del disco.

Come con RAID-1, i sistemi RAID-5 portano un aumento delle prestazioni leggere perché le letture

possono su più unità in parallelo. D'altra parte, pe performance di scrittura vengono penalizzate a causa

del modo in cui i dati sono scritti sul RAID, scansionati, e poi scritti sulla disco di parità o tolleranza dei

guasti.

Di conseguenza, ogni processo di scrittura logica contro un RAID-5 incorre matrice quattro IO fisici (due

scritture e due letture). RAID-5 sistemi sono pertanto non è raccomandato nei sistemi in cui scrive

costantemente rappresentano più del 10% dell'attività complessiva. backup e manutenzione sarebbe

ovviamente eccezioni qui-SE avete orari non di punta ottenuto in cui si verificano tali operazioni.

Determinare la percentuale di scritture per legge, si può semplicemente utilizzare SQL Profiler Server e

un po 'di matematica. Nella mia esperienza, solo una piccola percentuale di ambienti SQL Server

incorrere superiore al 10% scrive su base regolare, il che significa che il RAID-5 rimane un solido e

soluzione conveniente nella maggior parte degli ambienti.

RAID-10 supera le limitazioni associate RAID-0 e RAID-1, combinando un alto grado di tolleranza ai

guasti con alta terminare le prestazioni, a scapito del drastico aumento dei prezzi.

Di conseguenza, se avete bisogno di ottenere prestazioni ottimali del disco, RAID-10 è

comunemente presume essere il percorso migliore per prendere quando il costo non è

un fattore. Essere consapevoli, tuttavia, che il RAID-10 non è una panacea, né è

necessariamente più veloce di RAID-5.

Determinare quale configurazione RAID da utilizzare è meglio farlo quando si imposta un

sistema, perché naturalmente correzione 'dopo il fatto' non è banale in termini di sforzo

e tempi di inattività. Tuttavia, se si verificano problemi di prestazioni IO, e avete abbastanza dischi e

controller idonei disponibili per implementare un nuovo RAID Configurazione con l'hardware esistente,

per poi passare a un RAID-5 o RAID-10 soluzione in grado di fornire notevoli vantaggi prestazionali.

In caso contrario, cambiando tipi di RAID potrebbero meglio essere considerato sia un ultimo disperato

soluzione per migliorare le prestazioni con l'hardware esistente, o un invito a considerare gettando

opzioni di archiviazione più recenti e più costosi al vostro problema, partendo dal presupposto che avete

affrontato partizione Offset e dimensioni di blocco, e continui ad avere problemi di prestazioni. A meno

che naturalmente, non si stiano utilizzando le risorse disco esistenti nel modo più efficiente possibile.

5. SCELTE OTTIMALI DEI DISCHI

Anche quando l’hardware è configurato perfettamente, trovo molte organizzazioni che non riescono a

al meglio tutte le risorse disponibili, soprattutto quando si tratta dei dischi.

Poiché il sottosistema I/O è essenziale per prestazioni e disponibilità, dobbiamo affrontare

separatamente questi due argomenti per ottimizzare al massimo le risorse disponibili.

Se per esempio dovessi mantenere a portata di mano i full backup dei database business-critical, dovrei

considerare che lo spazio disco occupato dai backup potrebbe inficiare su altre componenti dell'istanza

SQL.

Ne consegue che, se da un lato non si può rischiare la disponibilità o la recuperabilità, dall'altro si deve

decidere se dedicare un disco ad alte prestazioni per backup/restore, sacrificando eventualmente le alte

prestazioni per gli indici dei database o altre performance.

Solitamente raccomando, alle organizzazioni che mantengono le copie dei backup su server per uno o

due giorni al massimo, di fare delle copie aggiuntive dei backup su altri server (dei file server per

esempio).

Automatizzando il processo di copia di backup in un'altra posizione, è possibile liberare i dischi

performanti (e più costosi) in modo che possano essere meglio utilizzati per le prestazioni business-

oriented, garantendo al tempo stesso la ridondanza dei backup e maggiore copertura con copie di

backup off-box. Questo è anche il caso in cui le soluzioni di backup di terze parti possono avere un

senso, come diminuire la quantità di disco necessaria per archiviare i backup, consentendo più spazio da

utilizzare per i file di log, dati e indici.

Un altro scenario comune a molte organizzazioni è la tendenza a memorizzare tutti file SQL Server di log

e dati sullo stesso volume, anche quando altri volumi sono disponibili. In questi casi, log e file di dati

competono per I/O sullo stesso set di controller (o volumi), mentre altri effettivamente rimangono

inattivi.

La mia ipotesi è che questo approccio alla memorizzazione di dati di SQL Server e file di log viene fatto

per mantenere le cose più organizzate e più facili da gestire. Tuttavia, un tale approccio organizzato

causa una pesante riduzione delle prestazioni.

Considerate che che i file indice suddivisi su più volumi è un enorme modo per migliorare le prestazioni.

Non sarà possibile invece ottenere lo stesso miglioramento delle prestazioni quando i file di dati di SQL

Server sono suddivisi su più partizioni sullo stesso volume.

Con i dati di log e file di indice distribuiti su unità distinte, in genere è possibile aumentare prestazioni in

modo impressionante. Quando si tratta di ottimizzare le prestazioni del disco, assicurarsi che il

bilanciamento del carico di lavoro sia distribuito in modo intelligente su dischi ridondati.

Salvate i log e i dati sui volumi più veloci, e relegate i backup e altri file su dischi meno performante ogni

volta che ne avete la possibilità.

Network

La maggior parte dei server SQL hanno una discreta quantità di traffico proveniente da applicazioni e

utenti. Tuttavia, con l'eccezione di backup, è raro che più implementazioni SQL Server possano saturare

le connessioni di rete. Questo non significa che piccole modifiche di configurazione di rete per

l'hardware esistente non possono produrre miglioramenti delle prestazioni.

6. JUMBO FRAME

Lo Standard Ethernet frame è limitato a 1500 byte per payload, il che richiede di gestire l'invio di grandi

flussi di dati separati in piccoli frame. I Jumbo Frames superano questo problema aumentando la

quantità di dati per payload fino a 9.000 byte, riducendo così il carico di elaborazione e di gestione

necessarie per inviare i dati avanti e indietro attraverso la rete.

Impostando Jumbo Frame su un Gigabit (o superiore) di rete può essere difficile, e non è raro imbattersi

in problemi durante il tentativo di modifiche di configurazione. E’ consigliabile programmare i tempi di

inattività, pianificare e provare le conseguenze prima di fare questo cambiamento in ambienti di

produzione. I Jumbo Frames possono essere un modo per aumentare le prestazioni complessive e la

reattività se si è costretti costantemente a gestire grandi blocchi di dati da 64 bit SQL Server a server

applicativi a 64 bit (per reports o simili).

Manutenzione

Non si può negare l'impatto che un DBA a tempo pieno può avere quando si tratta di gestire le

prestazioni, in particolare attraverso il fine-tuning e piani di manutenzione ben progettati. Ma nelle

organizzazioni in cui un DBA a tempo pieno non è giustificato (come quando i database sono piccoli), o

non è disponibile, l'automazione delle attività di manutenzione regolari è in grado di fornire enormi

vantaggi prestazionali.

NOTA: sp_msforeachdb non è supportato da Microsoft e purtroppo, NON funziona su tutti i database su alcuni server moderatamente utilizzati. Come tale, un approccio migliore sarebbe usare la sostituzione suggerita di Aaron Bertrand per sp_msforeachdb, utilizzando la seguente sintassi: EXEC sp_ForEachdb @command = N’EXEC ?..sp_updatestats’

Lo script e una panoramica di Aaron si possono trovare qui: http://www.mssqltips.com/sqlservertip/2201/making-a-più-affidabile-e-flexible-spmsforeachdb /

7. AGGIORNAMENTO STATISTICHE

Una cosa che rende SQL Server così tanto di successo è la sua fenomenale capacità interna di esecuzione

di query, che si basa sulle statistiche complesse circa la natura, cardinalità, e la densità dei dati per

accelerare l'esecuzione di query. Però, senza statistiche regolarmente aggiornate, la capacità di SQL

Server per determinare correttamente gli approcci più efficaci a soddisfare una query può diminuire.

SQL Server è impostato di default per aggiornare automaticamente le statistiche, ma limitarsi a questa

tecnica non è sufficiente.

Inoltre, nei sistemi in cui grandi volumi di variazioni avvengono regolarmente (o anche di tanto in tanto),

è facile avere delle statistiche non precise, e di conseguenza una perdita delle prestazioni. Ancora

peggio, se le statistiche degradano nel tempo, non è raro incontrare situazioni in cui il lento accumulo di

statistiche 'vecchie' porterà a problemi di prestazioni che peggiorano fino a raggiungere un 'punto di

rottura' in cui le prestazioni possono prendere una piega ‘catastrofica’ per query o altre operazioni

chiave.

Anche se il capitolo delle statistiche è al di fuori del presente documento, posso suggerire un modo

semplice per ottenere un miglioramento con poco sforzo anche sopra il 50%. Pianificare la seguente

query come parte di un Job di Server SQL Agent che viene eseguito ogni notte durante orari non di

punta:

EXEC sp_msforeachdb

@ Command1 = 'EXEC? .. Sp_updatestats'

Anche se non perfetto, questo semplice script passerà attraverso ogni database sul server (utilizzando il

comando non documentato sp_msforeachdb), e farà un aggiornamento minimo di eventuali statistiche

non aggiornate.

Risorse aggiuntive e approfondimenti di aggiornamento statistiche si possono trovare nei Books on line,

articoli Technet e post di blog. La chiave è che senza aggiornare regolarmente le statistiche, le

prestazioni subiranno diminuzioni di performance, anche se SQL Server è configurato per

l'aggiornamento automatico.

8. DEFRAMMENTAZIONE INDICI

Un altro compito regolare di un DBA è quello di deframmentare regolarmente gli indici. Se non si ha

familiarità con la frammentazione degli indici, un modo eccessivamente semplicistico è pensare ad esso

come la frammentazione del disco, in cui la scrittura dei dati avviene ‘a casaccio’ e non sequenziale. In

questa maniera la lettura dei dati sparsi sul disco avverrà in modo casuale incorrendo in più operazioni

di I/O di quanto sarebbe ottimale.

In realtà le cose sono un po’ più complesse poiché la frammentazione può derivare dai dati indice fuori

ordine logico, o da dati all'interno di pagine indice scarsamente popolato. In entrambe i casi, si verifica

una riduzione delle prestazioni continuando a peggiorare se il problema non viene corretto. In un

vecchio WHITE PAPER la frammentazione indice per SQL Server 2000, descriveva un decadimento delle

prestazioni dal 13% al 460%, a seconda dell'ambiente, a causa di indici frammentati.

Una panoramica completa di come deframmentare gli indici è al di fuori del campo di applicazione di

questa libro, ma, Michelle Ufford fornisce un ottimo script di defrag2 che può essere facilmente

implementato e automatizzato in ambienti senza un DBA a tempo pieno. Con operazioni di

indicizzazione avanzate, gli indici saranno più efficienti piuttosto che dell’ hardware molto perfomante

perché indici correttamente definiti riducono la quantità di carico che le componenti hardware devono

sopportare. D’altra parte, configurare correttamente gli indici richiede un approfondita comprensione di

T-SQL e buone tecniche di indicizzazione. Come mi piace dire, l’ index tuning è per il 50% scienza, il 50%

di abilità del DBA, e il 50% di Magia oscura ().

Gestire bene gli indici correttamente in genere può fornire la maggior quantità di miglioramento delle

prestazioni con il minimo costo.

2 http://sqlfool.com/2011/06/index-defrag-script-v4-1/

9. RIMOZIONE DEGLI INDICI INUTILIZZATI

Mentre gli indici correttamente definiti migliorano drasticamente le prestazioni generali, non è raro

vedere molte aziende (in particolare quelli senza un DBA a tempo pieno) optare come soluzione ai

problemi di prestazioni, quello di aggiungere più e più indici. Ogni indice aggiunto a un database

comporta un costo; ogni indice infatti deve essere aggiornato ogni volta che c'è una modifica dei dati

(UPDATE, DELETE o INSERT). Nella maggior parte dei casi, i vantaggi prestazionali di indici superano di

gran lunga l'overhead dovuto al mantenimento degli indici, soprattutto se questi sono regolarmente

deframmentati. SQL Server eccelle nel saper gestire rapidamente e facilmente le modifiche di un indice

nella maggior parte dei casi.

Nei casi di indici raramente o mai utilizzati la manutenzione di questi ultimi si va comunque a sommare

all’aggiornamento di quelli che vengono sfruttati. Di conseguenza, gli indici non utilizzati diminuiscono le

prestazioni invece di migliorarle, e devono quindi essere rimossi.

La rimozione efficace degli indici non utilizzati è banale e si può fare sia attraverso console grafica SSMS

(SQL Server Management Studio) che utilizzando il comando T-SQL DROP INDEX.

Trovare tali indici non utilizzati può essere meno semplice. Si dovrebbe prima iniziare a guardare gli

indici con scarsa selettività, dove la selettività è un termine usato per definire come unico o selettivo (in

grado di selezionare) i valori all'interno di un indice.

Ad esempio, in una società con 1.000 dipendenti, un indice Data di nascita sarebbe probabilmente

altamente selettivo, il che significa che una query per i dipendenti nati in un dato giorno dovrebbe,

statisticamente, non restituire più di una media di 3 o 4 dipendenti al giorno. Nella terminologia SQL

Server, questo indice avrebbe una densità di 0,003% (o 3 su 1000), che a sua volta, si tradurrebbe in un

indice di selettività di 0,997 (densità indice e selettività sono inversamente proporzionali). In sostanza,

questo indice sarebbe utile a qualsiasi query per recuperare la data di nascita dei dipendenti, data la sua

alta selettività e in grado di discriminare i risultati non richiesti nella query.

In SQL Server, più l’indice è selettivo, maggiore è la probabilità che venga utilizzato, e più performante

sarà nel restituire i risultati. Per esempio, immaginate un altro indice, questa volta sulla colonna

EmailAddress della stessa tabella dipendenti. In questo caso, ogni voce sarà univoca (assumendo che i

dipendenti non possono essere elencati più di una volta). Significa che la densità di questo indice sarà

0,001 (o 1 su 1000), dando la perfetta selettività. Differente sarebbe un indice sulla colonna del sesso

(M/F) della stessa tabella. Sarebbe un indice molto povero perché avrebbe solo due valori unici e

avrebbe una distribuzione dei valori abbastanza omogenea nella tabella, rendendo l'indice in gran parte

inutile. Infatti, come regola empirica, SQL Server non utilizza un indice con una selettività inferiore al

90%, il che significa che deve essere rimosso.

Per calcolare la densità e di conseguenza, la selettività, utilizzare il comando DBCC SHOW_STATISTICS su

una determinata tabella. Ad esempio, per controllare se uno degli indici menzionati in precedenza, è

necessario eseguire le seguenti operazioni:

DBCC SHOW_STATISTICS (Employees, IX_Employees_ByGender)

Nella prima serie di risultati, selezionare la colonna “Density” insieme alla “All density” della seconda

serie di risultati. Ricorda che questi valori sono espressi in esponenziale (così 1,166079E-06 è in realtà

0,00001166709) ogni volta che c'è un E-0x. Finché il valore di densità restituito è inferiore a 0,1 (cioè una

selettività superiore al 90%), è abbastanza sicuro che l'indice è abbastanza selettivo da poter essere

utilizzato nella maggior parte delle query.

Naturalmente, è anche possibile che esistano indici altamente selettivi ma è meglio non abituarsi perché

nessuna query viene eseguita sempre e solo sulle colonne indicizzate. Casi come questo sono molto più

difficili da trovare, ma se presenti aumentano sovraccarico delle prestazioni durante le modifiche.

Mentre esiste un certo numero di tecniche per trovare questi tipi di indici, rimuoverli può causare un

enorme incremento del lavoro in ambienti in cui l'indicizzazione è sfuggita di mano. In casi in cui

l'aggiunta di indici è stata più controllata, non si dovrebbe vedere un innalzamento del carico di lavoro di

SQL Server a meno di dover aggiornare un indice di grandi dimensioni che prevede un sacco di

aggiornamenti.

10. CREAZIONE E OTTIMIZZAZIONE DEGLI INDICI

Non scenderò a fondo su questo argomento che rimane al di fuori del campo di applicazione del

presente documento. Tuttavia, la corretta configurazione e utilizzo degli indici è uno dei modi più

semplici per aumentare drasticamente le prestazioni di SQL Server, con costi praticamente nulli. Quindi

se non si ha dimestichezza con la creazione degli indici, la consultazione di documentazione on-line delle

nozioni base di indicizzazione può essere estremamente utile. Tuttavia, la creazione degli indici e messa

a punto non è qualcosa che può essere appreso leggendo un gran numero di articoli o libri.

Invece, imparare a ottimizzare e creare correttamente indici richiederà una buona dose di ricerca

assieme all’ esperienza sul campo.

Anche i principi fondamentali di messa a punto degli indici possono restituire soddisfazioni quando si

arriva ad aumentare le prestazioni di SQL Server. Ancora più importante, che rende gli indici così potenti

è che quando impiegati correttamente, possono ridurre drasticamente la quantità di dati che SQL Server

deve esaminare al momento di compilare una query, anche quando sono coinvolte milioni di righe.

Questo a sua volta significa che, utilizzando in modo efficace gli indici, è possibile ridurre drasticamente

l’utilizzo delle risorse hardware potendo sfruttare quello già avete a portata di mano.