Date post: | 14-Apr-2017 |
Category: |
Technology |
Upload: | renzo-sotgia |
View: | 140 times |
Download: | 0 times |
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.