Guida di riferimento a Fast Track Data Warehouse per
annuncio pubblicitario
Guida di riferimento a Fast Track Data Warehouse per SQL Server 2012
Articolo tecnico su SQL Server
Autori: Eric Kraemer, Mike Bassett, Eric Lemoine, Dave Withers
Revisori tecnici: Claude Lorenson, Susan Price, Ralph Kemperdick, Henk van der Valk,
Alexi Khalyako, Oliver Chiu
Data di pubblicazione: marzo 2012
Contesto di applicazione: SQL Server 2012
Riepilogo: in questo articolo viene descritto un modello di configurazione di riferimento,
denominato Fast Track Data Warehouse, che prevede l'adozione di un approccio incentrato
sul bilanciamento delle risorse per l'implementazione di un'architettura di sistema di tipo SMP
(multiprocessore simmetrico) per i database di SQL Server, nell'ottica di garantire livelli di
prestazioni eccellenti e affidabili caratteristiche di scalabilità per i carichi di lavoro del data
warehouse. Con la scelta di un'architettura di riferimento Fast Track Data Warehouse ci si
prefigge di ottenere un efficiente bilanciamento delle risorse tra la funzionalità di elaborazione
dati di SQL Server e la velocità effettiva dell'hardware.
Copyright
Il documento viene fornito "così com'è". Le informazioni e le opinioni espresse nel presente documento,
inclusi gli URL e altri riferimenti a siti Web, possono essere soggette a modifiche senza preavviso.
L'utente accetta di utilizzare tali informazioni a proprio rischio.
Il presente documento non implica la concessione di alcun diritto di proprietà intellettuale relativo ai
prodotti Microsoft. È possibile copiare e utilizzare questo documento per fini di riferimento interno.
© 2012 Microsoft. Tutti i diritti sono riservati.
2
Sommario
Cronologia delle modifiche relative a Fast Track Data Warehouse .............................................................. 6
Introduzione.................................................................................................................................................. 6
Destinatari ................................................................................................................................................. 6
Fast Track Data Warehouse .......................................................................................................................... 6
Fast Track .................................................................................................................................................. 7
Proposta di valore ..................................................................................................................................... 7
Metodologia .................................................................................................................................................. 8
Architettura dei componenti olistica ........................................................................................................ 8
Approccio ottimizzato per il carico di lavoro ............................................................................................ 8
Configurazioni di riferimento SQL Server Fast Track convalidate ............................................................. 9
Riepilogo ................................................................................................................................................... 9
Carico di lavoro FTDW................................................................................................................................... 9
Modelli di carichi di lavoro di data warehouse ......................................................................................... 9
Valutazione del carico di lavoro .............................................................................................................. 10
Attributi qualitativi del carico di lavoro del data warehouse ................................................................. 12
Scelta di una configurazione di riferimento FTDW ..................................................................................... 14
Opzione 1: valutazione di base ............................................................................................................... 14
Passaggio 1: valutare il caso di utilizzo del cliente .................................................................................. 14
Passaggio 2: scegliere un'architettura di riferimento FTDW pubblicata ............................................ 16
Opzione 2: valutazione completa ........................................................................................................... 16
Panoramica del processo .................................................................................................................... 16
Passaggio 1: valutare il caso di utilizzo del cliente.............................................................................. 16
Passaggio 2: stabilire le metriche di valutazione ................................................................................ 17
Passaggio 3: scegliere un'architettura di riferimento Fast Track Data Warehouse ........................... 18
Opzione 3: architetture di riferimento definite dall'utente.................................................................... 18
Passaggio 1: definire il carico di lavoro ............................................................................................... 19
Passaggio 2: stabilire i benchmark dell'architettura dei componenti ................................................ 19
Scelta di un'architettura di riferimento FTDW: riepilogo ....................................................................... 20
3
Configurazione FTDW standard .................................................................................................................. 20
Architettura dei componenti hardware .................................................................................................. 20
Requisiti e configurazione dei componenti ........................................................................................ 21
Configurazione dell'applicazione ............................................................................................................ 23
Windows Server 2008 R2 .................................................................................................................... 23
SQL Server 2012 Enterprise ................................................................................................................ 23
Sistema di archiviazione...................................................................................................................... 25
Procedure consigliate per FTDW relative a SQL Server .............................................................................. 29
Architettura dei dati ................................................................................................................................ 30
Struttura delle tabelle ......................................................................................................................... 30
Partizionamento delle tabelle............................................................................................................. 31
Indicizzazione ...................................................................................................................................... 32
Indici columnstore in memoria xVelocity ........................................................................................... 32
Statistiche del database ...................................................................................................................... 34
Compressione ..................................................................................................................................... 35
Gestione della frammentazione dei dati................................................................................................. 35
Frammentazione del file system ......................................................................................................... 35
Più filegroup ........................................................................................................................................ 38
Caricamento dei dati ............................................................................................................................... 38
Caricamenti incrementali.................................................................................................................... 38
Migrazione dei dati ............................................................................................................................. 41
Benchmarking e convalida .......................................................................................................................... 43
Esecuzione della convalida FTDW di base............................................................................................... 44
Testing di base con SQLIO ................................................................................................................... 44
Esecuzione del benchmark del database Fast Track ............................................................................... 48
Calcolo della metrica MCR .................................................................................................................. 49
Calcolo della metrica BCR ................................................................................................................... 50
Architetture di riferimento FTDW pubblicate............................................................................................. 53
Conclusioni .................................................................................................................................................. 53
Appendice ................................................................................................................................................... 55
4
Strumento di ridimensionamento del sistema FTDW............................................................................. 55
Convalida di un'architettura di riferimento FTDW definita dall'utente.................................................. 55
Testing sintetico del sistema di I/O..................................................................................................... 55
Generazione di file di test con SQLIO ................................................................................................. 55
Testing del carico di lavoro ..................................................................................................................... 58
Misurazione della metrica MCR per il server (facoltativo) ................................................................. 58
Misurazione della metrica BCR per il carico di lavoro ........................................................................ 58
Fattori che influiscono sulla frequenza di utilizzo delle query ........................................................... 63
5
Cronologia delle modifiche relative a Fast Track Data Warehouse
Nella tabella seguente è disponibile un elenco delle modifiche o degli aggiornamenti più
importanti che caratterizzano le versioni della Guida di riferimento a Fast Track Data Warehouse.
Descrizione
Versione
Novità per SQL
Server 2012
4.0
Novità per SQL
4.0
Server 2012
Novità per SQL
4.0
Server 2012
Novità per SQL
4.0
Server 2012
Novità per SQL
4.0
Server 2012
Novità per SQL
4.0
Server 2012
Novità per SQL
4.0
Server 2012
Tabella 1. Cronologia delle modifiche
Nota
Collegamenti ad altri documenti
sulle procedure consigliate per
SQL Server
Posizione
Benchmarking e convalida
Attenzione
Requisiti relativi alla memoria
RAM
Indici columnstore ottimizzati in
memoria xVelocity
Risorse di archiviazione SSS
(Solid State Storage)
Importante
Indici columnstore
Stato solido
Convalida e indici columnstore
Convalida
Convalida dell'I/O di base
SQLIO
Introduzione
In questo documento vengono descritte l'architettura dei componenti e la metodologia del
programma SQL Server Fast Track Data Warehouse (FTDW). L'obiettivo di questo approccio
consiste nella definizione di un'architettura di sistema minima per i database di Microsoft SQL
Server, costituita dai componenti hardware e software necessari per conseguire e mantenere
un livello base di prestazioni standard adeguato a numerosi carichi di lavoro di data
warehousing.
Destinatari
Questo documento si rivolgere principalmente a responsabili della pianificazione IT, architetti
e amministratori di database nonché agli utenti di sistemi di Business Intelligence (BI)
interessati alla scelta di collaudate architetture di sistema standard per carichi di lavoro di
SQL Server conformi al modello Fast Track Data Warehouse.
Fast Track Data Warehouse
L'iniziativa SQL Server Fast Track Data Warehouse è costituita da una metodologia di base
e da esempi concreti per la distribuzione di una configurazione hardware e di database
equilibrata per carichi di lavoro di data warehousing. Per ulteriori informazioni, vedere la
sezione Carico di lavoro FTDW di questo documento.
6
Il bilanciamento si ottiene attraverso un'equilibrata valutazione dei componenti di sistema
principali di un'installazione di SQL Server, ovvero le risorse e la rete di archiviazione, il server,
il database e il sistema operativo. Ognuno di questi componenti deve essere ottimizzato in
funzione di una configurazione ideale. L'obiettivo consiste nell'ottenere un livello efficiente di
bilanciamento tra le capacità di elaborazione dati di SQL Server e le risorse dei componenti
hardware. Idealmente, la configurazione deve includere hardware di sistema almeno sufficiente
a soddisfare i requisiti in termini di archiviazione e prestazioni per un carico di lavoro di data
warehousing.
Fast Track
Il marchio SQL Server Fast Track identifica una configurazione hardware dei componenti
conforme ai principi dell'architettura di riferimento FTDW. Ogni architettura di riferimento FTDW
è definita da un carico di lavoro e da un insieme di base di linee guida inerenti la configurazione,
la convalida e le procedure consigliate per il database. Ecco alcuni dei principi essenziali del
programma Fast Track:
Benchmark specifici per il carico di lavoro. La struttura e la configurazione del sistema si
basano su reali e simultanei carichi di lavoro di query.
Specifiche dettagliate e verificate dei componenti hardware.
Bilanciamento dell'architettura dei componenti in funzione dell'equilibrio tra capacità del
database e risorse hardware principali.
Proposta di valore
La proposta di valore di FTDW si basa sui principi indicati qui di seguito:
7
Bilanciamento predeterminato tra i componenti principali del sistema.
Questo aspetto consente di ridurre al minimo il rischio di compiere investimenti per
CPU o risorse di archiviazione superflui rispetto ai risultati effettivi conseguibili a livello
applicativo.
Livelli di prestazioni standard prevedibili. Le configurazioni Fast Track sono
strutturate in funzione di capacità conformi alle funzionalità dell'applicazione SQL Server
per il server e il carico di lavoro designati.
Approccio incentrato sul carico di lavoro. Anziché tentare di realizzare una
configurazione di database adeguata a qualsiasi situazione, l'approccio di FTDW si
allinea in modo specifico al determinato caso di utilizzo del data warehouse.
Metodologia
Architettura dei componenti olistica
Le architetture SQL Server FTDW offrono un pratico quadro di riferimento a cui affidarsi per
bilanciare le complesse interrelazioni tra i componenti principali del sistema del database.
Generalmente indicata con il nome di stack, l'architettura dei componenti è illustrata nella figura 1.
Figura 1. Esempio di architettura dei componenti del database Fast Track
Ogni componente dello stack rappresenta uno degli anelli di cui è costituita la catena di
operazioni necessarie per elaborare i dati in SQL Server. Se si considera lo stack come un
sistema integrato, è possibile predisporre benchmark in grado di stabilire la larghezza di banda
effettiva per ogni componente. In questo modo ci si può assicurare che i singoli componenti
agiscano a una velocità effettiva sufficiente per le funzionalità dell'applicazione SQL Server
nello stack prescritto.
Approccio ottimizzato per il carico di lavoro
Le caratteristiche delle architetture dei componenti più adatte ad assicurare un bilanciamento
ottimale delle risorse variano a seconda delle diverse tipologie di carichi di lavoro delle
applicazioni di database. Un classico esempio a dimostrazione di questo aspetto è rappresentato
dalle differenze tra i carichi di lavoro OLTP per l'elaborazione di transazioni online in base
a ricerche e con un numero limitato di richieste e il data warehousing caratterizzato da un
numero ingente di richieste e un uso intensivo delle funzionalità di analisi. L'ambito OLTP si
distingue in genere per l'elevato grado di indicizzazione volto a supportare il recupero a bassa
latenza di piccole quantità di righe da set di dati in cui l'entità delle informazioni cronologiche
è in genere limitata. Questi tipi di operazioni sul database comportano notevoli spostamenti
delle testine dei dischi e generano classici modelli di analisi di I/O casuali. Quando si tratta invece
dell'esecuzione di attività analitiche, come ad esempio nel data warehousing, il numero delle richieste
di dati aumenta sensibilmente. In questi casi, il potenziale complessivo maggiore in termini di
velocità effettiva derivante dalle analisi sequenziali dei dischi può produrre vantaggi notevoli.
8
Le differenze sostanziali tra i diversi casi di utilizzo comportano importanti implicazioni per quanto
concerne la realizzazione di uno stack di componenti bilanciato. Le velocità medie per disco con
cui vengono eseguite le analisi di I/O casuali nelle unità SAS attuali possono risultare 10 volte
inferiori rispetto a quelle di analisi sequenziale con lo stesso hardware. A differenza degli
approcci più convenzionali incentrati sul numero di operazioni al secondo (espresso in IOPS),
con la valutazione dei carichi di lavoro di data warehousing Fast Track viene dedicata maggiore
attenzione al conseguimento di velocità di analisi di I/O elevate costanti (espresse in MB/s).
Per superare le problematiche derivanti dall'estrema varietà dei carichi di lavoro possibili,
si procede a una chiara definizione degli attributi che caratterizzano i carichi specifici dei clienti.
I carichi di lavoro SQL Server Fast Track includono un elenco qualitativo di attributi che
definiscono in modo univoco un comune caso di utilizzo di applicazioni di database. Ogni carico
di lavoro è inoltre rappresentato da misure quantitative che includono query di benchmark
standard. Attraverso il benchmarking specifico del carico di lavoro è infatti possibile convalidare
la configurazione del database, le procedure consigliate e le indicazioni relative all'hardware.
Configurazioni di riferimento SQL Server Fast Track convalidate
Tutte le architetture di riferimento Fast Track pubblicate vengono convalidate in base alla
conformità all'insieme di principi e linee guida illustrato in questa guida. Esempi di questo
processo sono disponibili nelle sezioni successive di questo documento.
Riepilogo
La specifica SQL Server FTDW descritta in questa guida di riferimento è incentrata sulla
definizione dei carichi di lavoro e il bilanciamento dei componenti. Attraverso questo approccio
si deduce che l'adozione di una metodologia di provisioning adeguata a qualsiasi situazione
può risultare inefficiente ed estremamente onerosa per numerosi casi di utilizzo dei database.
La crescente complessità dei requisiti di business e l'estrema variabilità dei volumi di dati
richiedono un approccio più realistico. A tale scopo, questo documento illustra le indicazioni
prescrittive di riferimento a livello di architettura dei sistemi, il benchmarking dei componenti
hardware e software e una chiara definizione dei carichi di lavoro presi in esame, in modo da
delineare un approccio pratico alla realizzazione di architetture bilanciate.
Carico di lavoro FTDW
Modelli di carichi di lavoro di data warehouse
In genere le esigenze relative ai data warehouse prevedono l'accesso a volumi di dati elevati.
I data warehouse devono essere in grado di supportare un'ampia gamma di query funzionali
alle necessità di un pubblico estremamente vario che include team operanti nelle divisioni
finanziarie, organizzative, di marketing e di ricerca.
9
Per ovviare alle limitazioni dei sistemi di data warehouse tradizionali, le organizzazioni si sono
avvalse di tecniche di ottimizzazione RDBMS convenzionali quali la generazione di indici,
la preaggregazione di dati e la limitazione dell'accesso a livelli inferiori di dati. La complessità
e l'entità delle esigenze di manutenzione associate a questi approcci possono spesso rendere
insufficienti anche intervalli di tempo particolarmente ampi per le operazioni batch. Con il
progressivo sviluppo del data warehouse e l'aumento dell'utenza, la capacità di supportare
queste ottimizzazioni specifiche dei casi di utilizzo si complica ulteriormente, in particolare nel
caso di dati o correzioni ai dati ricevuti in ritardo.
Una soluzione comune a questo tipo di problematiche consiste nella semplice aggiunta di unità.
Non è raro osservare la presenza di centinaia di dischi per supportare data warehouse di
dimensioni relativamente piccole, nel tentativo di superare le limitazioni alle prestazioni di I/O
prodotte dal mapping di un'infrastruttura di I/O basata sulla ricerca a un carico di lavoro basato
sull'analisi. È possibile osservare spesso questa situazione in ambienti di rete SAN condivisi
tradizionalmente ottimizzati per la ricerca. Molte infrastrutture di I/O di archiviazione si basano
su modelli e tecniche che promuovono l'accesso I/O casuale, il che si traduce in latenza dei
dischi e in una riduzione complessiva della velocità effettiva del sottosistema di archiviazione
con i carichi di lavoro di data warehousing che prevedono intensive attività di analisi.
Fast Track Data Warehouse è una soluzione di ottimizzazione diversa per i carichi di lavoro
del data warehouse. Allineando i file di database e la configurazione del database a un
efficiente accesso con analisi (anziché con ricerca) sui dischi, è possibile ottenere prestazioni
notevolmente maggiori dai singoli dischi. L'aumento risultante delle prestazioni di ogni disco
comporta la riduzione del numero di dischi necessari per generare velocità effettiva di I/O
sufficiente a consentire a SQL Server di elaborare dati per un carico di lavoro specifico. È inoltre
possibile evitare alcune tecniche di ottimizzazione basate su indici che si adottano in genere per
migliorare la ricerca nei dischi.
Valutazione del carico di lavoro
Durante l'analisi dei carichi di lavoro per sistemi basati su FTDW è importante tenere conto
dell'adeguatezza rispetto alle procedure e alle configurazioni di sistema descritte in questo
argomento. I requisiti del data warehouse possono variare a seconda del cliente e in alcuni casi,
ad esempio per la replica del database, potrebbero non essere adatti a tutti i sistemi progettati
con FTDW. Di seguito sono illustrati i criteri fondamentali da cui partire per questo tipo di
valutazione.
Attività di analisi elevate
Le query in un carico di lavoro del data warehouse implicano spesso l'analisi di un numero
elevato di righe. Per questo motivo, le prestazioni di analisi dei dischi assumono una priorità
maggiore rispetto ai carichi di lavoro transazionali, che influiscono negativamente sui tempi di
ricerca nei dischi. L'architettura di riferimento FTDW prevede l'ottimizzazione dei componenti
hardware e software del database adottando come priorità principale le prestazioni di analisi dei
dischi. Questo aspetto si traduce in una maggiore efficienza delle operazioni di lettura sequenziali
dei dischi e nel conseguente aumento della velocità effettiva di I/O per ogni singola unità.
10
Assenza di volatilità
Una volta scritti, raramente i dati vengono modificati. Le operazioni DML, ad esempio
l'aggiornamento SQL, che comportano lo spostamento delle pagine associate alla stessa
tabella di database al di fuori dell'allineamento continuo, devono essere gestite con attenzione.
I carichi di lavoro che introducono in genere tale volatilità potrebbero non essere correttamente
conformi a FTDW. Quando si verifica tale volatilità, è consigliabile una manutenzione periodica
per ridurre al minimo la frammentazione.
Numero minimo di indici
L'aggiunta di indici non cluster migliora in genere le prestazioni delle ricerche di uno o pochi
record. Se si applicano indici non cluster a tabelle da cui recuperare quantità elevate di righe,
il conseguente aumento di operazioni di ricerca casuale nei dischi può invece influire
negativamente sulle prestazioni generali del sistema. La manutenzione degli indici può inoltre
implicare un sensibile aumento degli oneri correlati alla gestione dei dati, il che può costituire un
rischio in termini di rispetto dei livelli di servizi e dei tempi di caricamento del database.
Al contrario, le velocità di analisi sequenziale possono essere di oltre 10 volte maggiori a quelle
di accesso casuale. Un sistema concepito per ridurre al minimo l'utilizzo della ricerca casuale,
generando indici secondari, in genere produce velocità medie di I/O costanti e di gran lunga
superiori. Ciò assicura un utilizzo più efficiente delle risorse di I/O di archiviazione e prestazioni
coerenti e prevedibili per query analitiche di grandi dimensioni.
La metodologia FTDW prescrive tecniche di ottimizzazione del database allineate alle
caratteristiche dei carichi di lavoro designati. Gli indici cluster e il partizionamento per intervalli
rappresentano esempi di strutture di dati che supportano l'esecuzione su disco di efficienti
operazioni di I/O basate sull'analisi e sono pertanto gli strumenti principali consigliati per
l'ottimizzazione basata sull'architettura dei dati per ambienti FTDW.
Allineamento alle partizioni
Una caratteristica comune dei carichi di lavoro FTDW è la possibilità di sfruttare al meglio il
partizionamento in SQL Server. Il partizionamento consente di semplificare la gestione del ciclo
di vita dei dati e di ridurre nel tempo al minimo la frammentazione. I modelli di query per analisi
di grandi dimensioni possono inoltre trarre vantaggio dalla qualificazione delle partizioni
a intervalli e ridurre notevolmente l'entità delle analisi delle tabelle senza influire sulla
frammentazione o la velocità effettiva di I/O dei dischi.
Altre considerazioni
Nell'ambito della valutazione dei carichi di lavoro dei database è consigliabile tenere presenti le
ulteriori considerazioni illustrate di seguito:
11
L'implementazione e la gestione di una strategia di ottimizzazione del database con un
numero minimo di indici sono un requisito fondamentale per i carichi di lavoro FTDW.
Si presuppone che la frammentazione dei dati all'interno del data warehouse si attesti su
livelli minimi. Ciò implica quanto segue:
o Il tipo di frammentazione, che costituisce l'aspetto più importante, può essere
espresso in termini di dimensione del frammento. Un frammento rappresenta
allocazioni contigue di pagine del database da 8 KB.
o Se si desidera espandere il server aggiungendo risorse di archiviazione,
è necessario che tutte le tabelle più soggette a problemi di prestazioni vengano
ripopolate in modo coerente in base alle linee guida illustrate in questo
documento.
o Per l'implementazione di strutture di dati volatili, ad esempio tabelle con
normale attività di aggiornamento a livello di riga, può essere necessaria una
manutenzione frequente, ad esempio la deframmentazione o la ricompilazione
degli indici, per ridurre la frammentazione.
o Il caricamento di tabelle con indici cluster e batch di ID chiave cluster che si
sovrappongono a intervalli esistenti è una causa frequente di frammentazione.
Questo aspetto deve essere monitorato e gestito con attenzione, seguendo le
procedure consigliate descritte in questa guida.
Il concetto di data warehousing può avere molti significati diversi a seconda dei
destinatari. È consigliabile valutare con attenzione i requisiti dei clienti in base agli
attributi dei carichi di lavoro definiti nella metodologia FTDW.
Attributi qualitativi del carico di lavoro del data warehouse
È possibile definire il carico di lavoro FTDW attraverso le proprietà delle aree di interesse
correlate alle operazioni sul database:
Requisiti e modello di accesso degli utenti
Modello di dati
Architettura dei dati
Ottimizzazione del database
Nella tabella seguente sono riepilogati gli attributi dei carichi di lavoro di data warehousing. Le
differenze vengono evidenziate grazie al confronto con carichi di lavoro OLTP o ODS.
Attributo
Descrizione del
caso di utilizzo
12
Affinità dei carichi di lavoro:
data warehouse
Prevalentemente di sola lettura
(90%-10%)
Aggiornamenti in genere limitati
a requisiti di qualità dei dati
Inserimenti bulk con volume
elevato
Concorrenza complessiva
delle query da media a bassa;
picco della richiesta di query
simultanee compreso tra 10 e 30
OLTP/ODS
Rapporto lettura-aggiornamento
bilanciato (60%-40%)
Velocità effettiva delle query
simultanee caratterizzata da
esigenze operative
Inserimenti e aggiornamenti con
granularità fine
Elevata velocità effettiva delle
transazioni, ad esempio
10 KB/s
Attributo
Modello di dati
Affinità dei carichi di lavoro:
data warehouse
Velocità effettiva delle query
simultanee caratterizzata da
esigenze di analisi e report
Aggregazioni e/o analisi di
intervalli di grandi dimensioni
Query complesse (filtro, join,
raggruppamento, aggregazione)
Modello del data warehouse
centralizzato altamente
normalizzato
Denormalizzazione per
supportare i requisiti di report
spesso gestiti da applicazioni di
Business Intelligence come
SQL Server Analysis Services
Strutture di dati dimensionali
ospitate nel database con
concorrenza relativamente
bassa, richieste analitiche con
volume elevato
Sono comuni analisi di intervalli
di grandi dimensioni
Casi di utilizzo analitici ad hoc
Architettura dei
Utilizzo elevato di strutture di
dati
tabella heap
Tabelle partizionate di grandi
dimensioni con indici cluster che
supportano analisi limitate agli
intervalli
Tabelle dei fatti di dimensioni
molto grandi, ad esempio da
centinaia di gigabyte a più
terabyte
Dimensioni dei dati molto grandi,
ad esempio da centinaia di
terabyte a un petabyte
Ottimizzazione
Utilizzo minimo di indici secondari
del database
(caratteristica descritta in
precedenza come numero
minimo di indici)
Il partizionamento è comune
Tabella 2. Attributi dei carichi di lavoro di data warehouse
13
OLTP/ODS
Concorrenza complessiva
delle query utente da media
a elevata; picco della richiesta
di query simultanee compreso
tra 50 e 100 o oltre
Transazioni generalmente
molto brevi, ad esempio
numero minimo di ricerche di
righe discrete
Modello di dati operativi
altamente normalizzato
Denormalizzazione frequente
per il supporto decisionale;
concorrenza elevata, ricerche
discrete a bassa latenza
Memorizzazione cronologica
dei dati limitata
Modelli di dati denormalizzati
estratti da altri sistemi di origine
per supportare il processo
decisionale degli eventi operativi
Utilizzo minimo di strutture di
tabella heap
Strutture delle tabelle con indici
cluster che supportano ricerche
di record dettagliate (da 1 a
poche righe per ogni richiesta)
Tabelle dei fatti più piccole,
ad esempio meno di 100 GB
Dimensioni dei dati
relativamente ridotte, ad
esempio pochi terabyte
Utilizzo elevato
dell'ottimizzazione degli indici
secondari
Scelta di una configurazione di riferimento FTDW
Esistono tre approcci generali per l'utilizzo della metodologia FTDW descritta in questo
documento. I primi due sono specifici delle architetture di riferimento Fast Track pubblicate
e conformi per i data warehouse. Questi approcci consentono di selezionare sistemi predefiniti,
pubblicati nell'ambito del programma FTDW. Il terzo approccio considera la metodologia Fast
Track di base come un insieme di linee guida per la creazione di un sistema di data warehouse
definito dall'utente. Questo approccio finale prevede l'esecuzione di approfondite attività di
profiling del carico di lavoro e di benchmarking del sistema prima dell'acquisto o della distribuzione.
L'approccio richiede un elevato livello di conoscenze tecniche nelle aree della configurazione di
server e sistemi di archiviazione aziendale e dell'ottimizzazione dei database di SQL Server.
Opzione 1: valutazione di base
Questo scenario presuppone che il cliente abbia già scelto una configurazione di riferimento
FTDW o che disponga di metodi alternativi per determinare i requisiti relativi a server e CPU.
Questa opzione non richiede una valutazione completa della piattaforma, ovvero un modello
di prova.
Passaggio 1: valutare il caso di utilizzo del cliente
I modelli di riferimento Fast Track Data Warehouse non sono configurazioni di software
e hardware adatte a qualsiasi situazione. Sono invece definiti per le caratteristiche specifiche
di un carico di lavoro di data warehousing. Il primo passaggio della scelta di una configurazione
consiste nell'identificare queste caratteristiche, a partire dall'esame delle aree chiave dei
requisiti e dei modelli di utilizzo del cliente.
Carico di lavoro
Le definizioni dei carichi di lavoro FTDW offrono due aspetti essenziali per la valutazione del
caso di utilizzo. Il primo consiste in una serie di principi di base che definiscono gli elementi
principali del carico di lavoro rispetto alle prestazioni di SQL Server. È necessario valutare con
estrema attenzione questi principi rispetto al caso di utilizzo designato poiché eventuali conflitti
potrebbero indicare incompatibilità tra l'architettura di riferimento FTDW e il carico in questione.
Il secondo aspetto inerente il carico di lavoro è una descrizione generale del caso di utilizzo
designato. Questa fase ha l'obiettivo di sviluppare un'utile descrizione complessiva del caso di
utilizzo e di stabilire in questo modo un punto di partenza attendibile per valutare l'adeguatezza
rispetto al carico di lavoro.
14
Valutazione del carico di lavoro
Nell'elenco seguente viene illustrato un processo di base per la valutazione del carico di lavoro
del cliente. Si tratta di una valutazione qualitativa da considerare come una linea guida:
1. Definire i requisiti del carico di lavoro designato. Effettuare un esame comparativo
rispetto agli attributi dei carichi di lavoro FTDW. Per ulteriori informazioni, vedere la
sezione Carico di lavoro FTDW di questo documento.
2. Valutare le procedure consigliate per la metodologia FTDW. È opportuno valutare
le procedure correlate alla gestione del database e all'ottimizzazione del sistema
e dell'architettura dei dati rispetto al caso di utilizzo in esame e all'ambiente operativo.
Processo decisionale
La valutazione del carico di lavoro viene condotta allo scopo di raccogliere le informazioni
necessarie ad assumere una decisione pienamente consapevole per quanto concerne la scelta
di un'architettura di riferimento FTDW convalidata. In effetti, la maggior parte degli scenari
di data warehousing è costituita da una combinazione di attributi coerenti e contrastanti
relativamente al carico di lavoro FTDW. Gli attributi dei carichi di lavoro di priorità più elevata
e maggiormente affini alle configurazioni di riferimento Fast Track sono illustrati in questa
sezione. È consigliabile valutare con attenzione i principali casi di utilizzo del cliente in diretto
conflitto con uno o più di questi attributi, in quanto potrebbero invalidare la metodologia.
Carico di lavoro
Ecco gli attributi dei carichi di lavoro con priorità più elevata:
I carichi di lavoro critici sono caratterizzati da modelli di accesso ai dati che prevedono
un utilizzo intensivo delle attività di analisi e che pertanto traggono vantaggio dal
posizionamento sequenziale dei dati. In generale, ogni singola richiesta di query può
comportare la lettura di una quantità di righe in un ordine che può andare dalle decine
di migliaia fino a milioni o più.
Elevata capacità di dati, concorrenza ridotta relativamente ai carichi di lavoro OLTP
comuni.
Bassa volatilità dei dati. È consigliabile limitare la frequenza con cui vengono eseguite
attività DML di aggiornamento/eliminazione a una minima percentuale del footprint
complessivo del data warehouse.
Gestione del database
Questo aspetto prevede l'amministrazione del database, l'architettura dei dati (modello di dati
e struttura delle tabelle) e procedure di integrazione dei dati:
15
Architettura dei dati con partizionamento e un numero minimo di indici.
Attenta gestione della frammentazione del database, tramite adeguate strategie di
caricamento ed ETL e la manutenzione periodica.
Requisiti in termini di prevedibilità dell'aumento del volume di dati. I sistemi FTDW sono
predisposti per assicurare il completo bilanciamento della capacità. L'espansione delle
risorse di archiviazione implica la migrazione dei dati.
Passaggio 2: scegliere un'architettura di riferimento FTDW pubblicata
Se si conduce una semplice valutazione basata sul budget disponibile o esperienze precedenti,
è possibile che si protenda già per un determinato server. Può altresì accadere che si decida
di fondare l'analisi dei requisiti della larghezza di banda su un'idea di massima già formata in
merito alla capacità del carico di lavoro oppure sulle caratteristiche di un sistema esistente.
In ogni caso, la valutazione FTDW di base non prevede una valutazione completa della
piattaforma. Al contrario, si procede selezionando una configurazione FTDW conforme ai
requisiti stimati del cliente.
Opzione 2: valutazione completa
Le architetture di riferimento conformi a Fast Track offrono configurazioni dei componenti
hardware abbinate a carichi di lavoro definiti. La metodologia indicata di seguito delinea un
approccio lineare alla scelta di una architettura dei componenti del database in grado di
garantire un immediato equilibrio tra requisiti del caso di utilizzo, prestazioni e scalabilità.
Per poter seguire questo approccio è necessario disporre di solide competenze nell'ambito
dell'architettura dei sistemi di database e della distribuzione di data warehouse. In genere,
questo processo prevede il coinvolgimento di partner Fast Track e di personale commerciale
e tecnico Microsoft.
Panoramica del processo
Il processo di valutazione FTDW completa può essere sintetizzato nel flusso di attività descritto
qui di seguito:
1. Valutare gli attributi dei carichi di lavoro Fast Track rispetto allo scenario di utilizzo
designato.
2. Identificare i requisiti relativi a server e/o larghezza di banda per il caso di utilizzo del
cliente. Prima di iniziare la valutazione è necessario scegliere una configurazione di
riferimento FTDW pubblicata.
3. Identificare una query rappresentativa del requisito del carico di lavoro del cliente.
4. Calcolare il livello di BCR (Benchmark Consumption Rate) di SQL Server per la query.
5. Calcolare la capacità di dati utente necessaria (UDC, User Data Capacity).
6. Confrontare i livelli di BCR e UDC rispetto alle valutazioni di capacità e frequenza
massima di utilizzo della CPU (MCR, Maximum CPU Consumption Rate) per le
architetture di riferimento Fast Track conformi.
Nelle sezioni a seguire vengono descritti in dettaglio i singoli punti del processo di valutazione
completa.
Passaggio 1: valutare il caso di utilizzo del cliente
Valutazione del carico di lavoro
Questo processo è lo stesso di quello descritto in Opzione 1: valutazione di base.
16
Selezionare l'hardware per la valutazione FTDW
Prima di iniziare una valutazione completa del sistema, è necessario scegliere e distribuire una
configurazione di riferimento FTDW pubblicata a fini di testing. È possibile scegliere tra diversi
metodi per identificare una configurazione di riferimento appropriata. Ecco alcuni approcci comuni:
Budget. Il cliente sceglie il sistema con la capacità più elevata e/o il sistema dalle
prestazioni migliori acquistabile con il budget a disposizione.
Prestazioni. Il cliente sceglie di acquistare il sistema dalle prestazioni migliori disponibile.
Analisi interna. La decisione si basa sull'analisi del carico di lavoro condotta dal cliente
con l'hardware esistente.
Analisi ad hoc. Lo strumento di ridimensionamento di FTDW offre un approccio di base
per calcolare i requisiti del sistema FTDW in base a presupposti generali relativi al
carico di lavoro del database in questione. Questo strumento basato su foglio di calcolo
è disponibile per il download all'indirizzo http://download.microsoft.com/download/
D/F/A/DFAAD98F-0F1B-4F8B-988F-22C3F94B08E0/Fast%20Track%20Core
%20Calculator%20v1.2.xlsx.
Passaggio 2: stabilire le metriche di valutazione
Le tre metriche seguenti sono importanti per una valutazione FTDW completa e includono criteri
per adottare decisioni chiave riguardo alla valutazione dell'hardware:
Frequenza di utilizzo massima dei core della CPU (MCR, Maximum CPU Core
Consumption Rate)
Frequenza di utilizzo del benchmark (BCR, Benchmark Consumption Rate)
Capacità di dati utente (UDC, User Data Capacity) necessaria
Per ulteriori informazioni sul calcolo di queste metriche, vedere la sezione Benchmarking
e convalida di questo documento.
MCR
Questa metrica misura la velocità di elaborazione dati massima di SQL Server per una query
e un set di dati standard per una combinazione di server e CPU specifica. Viene espressa
come velocità per core ed è misurata come analisi basata su query dalla cache in memoria.
La metrica MCR costituisce il punto di partenza per la progettazione del sistema Fast Track.
Rappresenta una larghezza di banda di I/O massima stimata necessaria per il server, la CPU
e il carico di lavoro. La metrica MCR è utile come riferimento di progettazione iniziale, in quanto
richiede solo archiviazione locale e schema del database minimi per stimare la velocità effettiva
potenziale per una determinata CPU. È importante sottolineare che la metrica MCR viene
utilizzata come punto di partenza per la progettazione del sistema e non è una misura delle
prestazioni del sistema.
17
BCR
La metrica BCR viene misurata tramite un set di query considerate definitive per il carico di
lavoro FTDW. La metrica BCR viene calcolata in termini di larghezza di banda di lettura totale
dal disco e dalla cache, anziché solo dalla cache come nel calcolo della metrica MCR.
La metrica BCR può consentire la personalizzazione dell'infrastruttura per un caso di utilizzo
di un cliente specifico tramite la misurazione rispetto a un set di query corrispondente ai modelli
di carico di lavoro del cliente. In alternativa, nel caso di un'architettura di riferimento FTDW
convalidata da partner, viene utilizzato un set di query di benchmark finalizzato a rendere possibile
la progettazione per carichi di lavoro intensivi. In breve, BCR è una misura reale dell'elaborazione
dati che utilizza più query con un carico di lavoro simultaneo su volumi di dati consistenti.
Capacità di dati utente
Si tratta della capacità prevista del database di SQL Server. La capacità di dati utente di Fast
Track tiene conto della compressione del database successiva al caricamento e rappresenta
una stima della quantità di file o flussi di dati utente non compressi che possono essere caricati
nel sistema Fast Track. Il rapporto di compressione standard utilizzato per FTDW è 3,5:1.
Occorre sottolineare che eventuali espansioni delle risorse di archiviazione successive alla
distribuzione iniziale possono richiedere la migrazione dei dati, attraverso cui eseguire un
efficiente striping dei dati esistenti nei vari percorsi dei nuovi file di database. Per questo motivo
è importante considerare l'aumento previsto delle dimensioni del database e la durata presunta
del sistema quando si sceglie un'architettura di riferimento appropriata.
Passaggio 3: scegliere un'architettura di riferimento Fast Track Data Warehouse
Una volta calcolata, la metrica BCR può essere confrontata con le valutazioni di MCR
e capacità fornite dai partner Fast Track per ogni architettura di riferimento FTDW pubblicata.
Per ulteriori informazioni sui partner Microsoft, vedere Fast Track Data Warehouse
(http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/
fast-track.aspx).
È possibile utilizzare la metrica BCR come punto di riferimento comune per la valutazione dei
risultati del sistema di test/valutazione rispetto a configurazioni pubblicate. Basandosi sui dati
della metrica BCR, il cliente può scegliere l'opzione Fast Track più coerente con i risultati dei test.
Opzione 3: architetture di riferimento definite dall'utente
Questo approccio si avvale della metodologia FTDW per personalizzare un sistema ai fini di un
carico di lavoro o di un gruppo di componenti hardware specifico. In questo caso è necessaria
una comprensione esaustiva di SQL Server e dei componenti hardware su cui viene eseguito.
Nei passaggi seguenti viene descritto l'approccio generale per sviluppare un'architettura di
riferimento definita dall'utente che sia conforme ai principi di FTDW.
18
Passaggio 1: definire il carico di lavoro
La comprensione del caso di utilizzo del database di destinazione è l'elemento centrale per
le configurazioni FTDW. Questo aspetto si applica allo stesso modo a qualsiasi applicazione
personalizzata basata sulle linee guida contenute in questo documento. Le linee guida per
le architetture FTDW, in particolare per quanto riguarda i carichi di lavoro, possono essere
utilizzate come modello di riferimento per integrare la valutazione del carico di lavoro nella
progettazione dell'architettura dei componenti.
Passaggio 2: stabilire i benchmark dell'architettura dei componenti
L'insieme di attività indicate di seguito costituisce una base affidabile su cui fondare lo sviluppo
di un'architettura di riferimento per un carico di lavoro predefinito:
1. Stabilire la frequenza di utilizzo massima dei core della CPU (MCR) per il server e la
CPU scelti. Utilizzare il metodo descritto nella sezione Benchmarking e convalida di
questo documento per calcolare la metrica MCR. È inoltre possibile utilizzare le
valutazioni pubblicate della metrica MCR per le configurazioni FTDW. In generale,
le CPU della stessa famiglia hanno frequenze di utilizzo dei core simili per il database
di SQL Server.
2. Utilizzare il valore MCR per stimare i requisiti di archiviazione e di rete di archiviazione
ed elaborare una progettazione iniziale del sistema.
3. Predisporre un sistema di test basato sulla progettazione iniziale del sistema. Si tratterà
idealmente della configurazione completa specificata.
4. Stabilire una frequenza di utilizzo del benchmark (BCR). In base alla valutazione del
carico di lavoro, identificare una query o, nel caso ideale, un set di query rappresentative.
Utilizzare le procedure descritte nella sezione Misurazione della metrica BCR per il
carico di lavoro di questo documento.
5. Modificare la progettazione di sistema in base ai risultati.
6. Stabilire la configurazione finale del server e delle risorse di archiviazione.
Passaggio 3: convalida del sistema
L'obiettivo del benchmarking del sistema deve consistere nella convalida della configurazione
e della velocità effettiva dei componenti hardware identificati nel passaggio 2. Per ulteriori
informazioni su questo processo, vedere la sezione Convalida di un'architettura di riferimento
FTDW definita dall'utente di questo documento. Per convalidare il sistema, effettuare le
operazioni seguenti:
1. Valutare la velocità effettiva dei componenti rispetto ai requisiti di prestazioni stabiliti.
Ciò garantisce che la velocità effettiva del sistema corrisponda alle previsioni.
2. Per convalidare la velocità effettiva del sistema, ricreare la configurazione finale ed
eseguire i benchmark conclusivi. Come regola generale, la metrica BCR finale deve
raggiungere almeno l'80% rispetto al valore MCR del sistema.
19
Scelta di un'architettura di riferimento FTDW: riepilogo
La tabella seguente offre un quadro riepilogativo delle tre opzioni di selezione dell'architettura di
riferimento FTDW.
Opzione
Valutazione di base
Valutazione completa
Architettura di riferimento
definita dall'utente
Vantaggi
Configurazione e
predisposizione del
sistema molto rapide
(da alcuni giorni ad alcune
settimane)
Costi di progettazione
e valutazione minimi
Requisiti minori in termini
di competenze relative
all'infrastruttura
Architettura di riferimento
predefinita personalizzata
per il carico di lavoro
previsto
Potenziale risparmio sui
costi per l'hardware
Maggiore fiducia nella
soluzione
Potenziale riutilizzo
dell'hardware esistente
Potenziale integrazione di
hardware più recente
Sistema altamente
personalizzato per il caso
di utilizzo
Svantaggi
Rischio di
sovradimensionamento
delle risorse di
archiviazione o di
sottovalutazione della
CPU
La valutazione richiede
impegno e tempo
(da alcune settimane ad
alcuni mesi)
Richiede una comprensione
approfondita del carico di
lavoro designato
Il processo richiede
diversi mesi
Richiede notevoli
competenze inerenti
l'infrastruttura
Richiede approfondite
competenze inerenti
SQL Server
Tabella 3. Confronto delle diverse opzioni di valutazione
Configurazione FTDW standard
Architettura dei componenti hardware
Le attuali architetture di riferimento FTDW sono basate su configurazioni di archiviazione
dedicate. Le opzioni attualmente pubblicate includono rete SAN commutata, rete SAN
a collegamento diretto, rete SAS a collegamento diretto, SAS-RBOD e iSCSI. La velocità
effettiva di I/O dei dischi viene ottenuta tramite l'utilizzo di enclosure e processori di
archiviazione dedicati e indipendenti. Ulteriori dettagli e configurazioni vengono pubblicati dai
diversi fornitori di soluzioni Fast Track. Nella figura 2 sono illustrati i blocchi costitutivi a livello di
componente di un'architettura di riferimento FTDW basata sul sistema di archiviazione SAN.
20
Figura 2. Esempio di configurazione dell'archiviazione per un server con 2 socket e 12 core
Requisiti e configurazione dei componenti
Memoria del server
RAM totale: l'allocazione della RAM per le architetture di riferimento FTDW è basata sui risultati
dei benchmark, con l'obiettivo di bilanciare la velocità effettiva logica massima (pagine totali
lette dal disco e dal buffer nel tempo) con l'utilizzo della CPU. Nella tabella 4 sono elencate le
allocazioni di memoria consigliate per le architetture di riferimento per SQL Server 2012. I valori
massimi di memoria indicati non rappresentano limiti rigidi, bensì valori medi per sistemi già
convalidati con successo.
Dimensioni del server
Memoria minima
Memoria massima
1 socket
64 GB
128 GB
2 socket
128 GB
256 GB
4 socket
256 GB
512 GB
8 socket
512 GB
768 GB
Tabella 4. Allocazioni di memoria consigliate per SQL Server 2012
21
Nel valutare i requisiti di memoria del sistema, è importante tenere presente anche quanto segue:
Query dalla cache: i carichi di lavoro che utilizzano una percentuale elevata di query
dalla cache possono ottenere un vantaggio complessivo dalle maggiori allocazioni di
RAM man mano che aumentano le dimensioni del carico di lavoro.
Hash join e ordinamenti: le query che si basano su hash join su larga scala o che
eseguono operazioni di ordinamento su larga scala otterranno vantaggi dalla possibilità
di disporre di grandi quantità di memoria fisica. Con quantità inferiori di memoria, queste
operazioni ricadono sul disco e utilizzano pesantemente tempdb, introducendo un
modello di I/O casuale tra le unità dati nel server.
Carichi: anche gli inserimenti bulk possono introdurre operazioni di ordinamento che
utilizzano tempdb se non possono essere elaborati nella memoria disponibile.
Indice columnstore ottimizzato in memoria xVelocity: i carichi di lavoro che favoriscono
in misura notevole i piani di query su indici columnstore funzionano in modo più efficiente
con pool di memoria prossimi ai limiti superiori degli intervalli indicati nella tabella 4.
SAN Fibre Channel
HBA - SAN: tutti i componenti di rete HBA e SAN presentano differenze più o meno significative
a seconda di marca e modello. La velocità effettiva dell'enclosure di archiviazione può inoltre
dipendere dalla configurazione SAN e dalle funzionalità dei bus PCIe. Questa indicazione è di
carattere generale ed è coerente con i test eseguiti durante lo sviluppo della configurazione di
riferimento FTDW.
Se si utilizza la suddivisione in zone, in queste ultime devono essere presenti solo le porte in
uso per Fast Track. Informazioni dettagliate su topologia e configurazione di rete FC sono
disponibili nella guida alla configurazione tecnica fornita da ogni partner Fast Track e specifica
di ogni architettura di riferimento FTDW pubblicata.
Multipath I/O (MPIO): è consigliabile configurare MPIO. Ogni volume ospitato in array di
archiviazione dedicati deve avere almeno un percorso attivo.
Il round robin con subset corrisponde ai criteri standard adottati per le configurazioni Fast Track,
ma viene raramente utilizzato per le architetture di riferimento dei partner, in quanto configurazioni
più adeguate vengono in genere individuate dai team tecnici dei partner FTDW. DSM e/o
documenti specifici dei partner prescrivono spesso impostazioni diverse e devono essere
esaminati con attenzione prima della configurazione.
Archiviazione
Disco locale: un array RAID1 con 2 dischi rappresenta l'allocazione minima per l'installazione
di SQL Server e di Windows Server. È necessario allocare spazio su disco sufficiente per
i requisiti di RAM virtuale e paging. In generale, lo spazio su disco disponibile deve essere
pari a 250 GB o a 1,5 volte la RAM di sistema, a seconda di quale sia il valore maggiore.
La rimanente configurazione dei dischi dipende dal caso di utilizzo e dalla preferenza del cliente.
File system logico: è preferibile il montaggio dei LUN in percorsi per cartelle di punti di
montaggio in Windows, anziché utilizzare lettere di unità, a causa del numero di volumi in molti
sistemi Fast Track.
22
Può essere utile determinare quale assegnazione di unità del sistema operativo Windows
rappresenti il LUN (volume), il gruppo di dischi RAID e il punto di montaggio di Windows Server
specifici nelle enclosure di archiviazione. È possibile adottare uno schema di denominazione
per i punti di montaggio e i volumi quando si montano i LUN in cartelle di Windows. Per ulteriori
informazioni sugli schemi di denominazione dei dispositivi, vedere le linee guida per la
configurazione tecnica a cura dei partner Fast Track.
È possibile utilizzare strumenti specifici del fornitore per ottenere lo schema di denominazione
dei volumi consigliato. Se non è disponibile alcuno strumento appropriato, è possibile rendere
disponibile in Windows un disco per volta dagli array di archiviazione, assegnando nomi di unità
per garantire la topologia corretta sia fisica che logica.
File system fisico: per ulteriori informazioni, incluse istruzioni dettagliate, vedere la sezione
Configurazione dell'applicazione di questo documento.
Configurazione dell'enclosure di archiviazione: tutte le impostazioni dell'enclosure restano
configurate sui valori predefiniti, a meno che non venga indicato altrimenti nella documentazione
tecnica dei partner Fast Track. Le specifiche di FTDW per la configurazione del file system
richiedono enclosure di archiviazione che consentano la configurazione specifica di
raggruppamenti RAID e assegnazioni di LUN. Questo è un fattore di cui è necessario tenere
conto per qualsiasi sostituzione hardware o per qualsiasi valutazione personalizzata
dell'hardware della configurazione di riferimento FTDW.
Configurazione dell'applicazione
Windows Server 2008 R2
A meno che non sia specificato diversamente, è consigliabile utilizzare le impostazioni
predefinite per il sistema operativo Windows Server 2008 R2 Enterprise. Assicurarsi che
siano stati applicati il Service Pack più recente e tutti gli aggiornamenti critici. La funzionalità
Multipath I/O è necessaria per molte architetture di riferimento. Per ulteriori informazioni sulla
configurazione MPIO, vedere la guida alla configurazione tecnica del partner Fast Track per
l'architettura di riferimento specificata. Verificare che Windows Server 2008 R2 sia installato
con il ruolo di server applicazioni per per assicurare la correttezza dell'installazione di .NET
Framework e delle relative impostazioni predefinite.
SQL Server 2012 Enterprise
Opzioni di avvio
È necessario aggiungere -E alle opzioni di avvio. In questo modo, è possibile aumentare il numero
di extent contigui in ogni file allocato a una tabella di database man mano che ne aumentano le
dimensioni. Ciò migliora l'accesso sequenziale al disco. Per ulteriori informazioni su questa opzione,
vedere l'articolo 329526 della Microsoft Knowledge Base (http://support.microsoft.com/kb/329526).
È importante verificare che l'opzione -E abbia avuto effetto all'avvio del database. L'opzione
distingue tra maiuscole e minuscole e tra formati diversi. Uno spazio vuoto prima o dopo
l'opzione può impedire l'inizializzazione.
23
È inoltre consigliabile aggiungere -T1117 alle opzioni di avvio. Questo flag di traccia garantisce
un aumento uniforme di tutti i file in un filegroup se è abilitato l'aumento automatico delle
dimensioni. L'indicazione standard per FTDW riguardo all'aumento delle dimensioni dei file
di database prevede la preallocazione anziché l'aumento automatico delle dimensioni,
ad eccezione di tempdb. Per ulteriori informazioni, vedere la sezione Dettagli della
configurazione dell'archiviazione di questo documento.
Abilitare l'opzione Blocco di pagine in memoria. Per ulteriori informazioni, vedere Abilitazione
dell'opzione Blocco di pagine in memoria (Windows)
(http://go.microsoft.com/fwlink/?LinkId=141863).
L'aggiunta dell'opzione -T834 deve essere valutata caso per caso. Questo flag di traccia può
migliorare le velocità effettive per molti carichi di lavoro dei data warehouse. Questo flag
consente allocazioni di pagina di grandi dimensioni per il pool di buffer di SQL Server.
Per ulteriori informazioni su questo e altri flag di traccia, vedere l'articolo 920093 della Microsoft
Knowledge Base (http://support.microsoft.com/kb/920093).
Nota: attualmente SQL Server 2012 non supporta il flag -T834 se nel database vengono
utilizzati indici columnstore. Se si intende utilizzare indici columnstore, non aggiungere questo
flag di traccia.
Memoria massima SQL
Non più del 92% della RAM totale del server deve essere allocato a SQL Server 2012. Se si
prevede che applicazioni aggiuntive debbano condividere il server, la quantità di RAM resa
disponibile per il sistema operativo deve essere modificata di conseguenza. Questa
impostazione è controllata dall'opzione max server memory. Per ulteriori informazioni sulle
impostazioni di memoria per le architetture di riferimento convalidate, vedere la documentazione
dei partner FTDW.
Resource Governor
I carichi di lavoro dei data warehouse includono in genere query complesse eseguite su volumi
elevati di dati. Queste query possono utilizzare grandi quantità di memoria e possono scrivere
sul disco se la memoria è vincolata. Questo comportamento ha implicazioni specifiche in termini
di gestione delle risorse, il cui utilizzo può essere controllato avvalendosi della tecnologia
Resource Governor in SQL Server 2012.
Nelle impostazioni predefinite di SQL Server, Resource Governor offre al massimo il 25% di
risorse di memoria di SQL Server a ogni sessione. Ciò significa che, nel peggiore dei casi,
tre query tali da consumare almeno il 25% di memoria disponibile bloccheranno qualsiasi altra
query che utilizzi una quantità elevata di memoria. In questo stato, tutte le query aggiuntive per
la cui esecuzione è necessaria una concessione di memoria elevata verranno accodate fino
a quando non si rendono disponibili altre risorse.
24
È possibile utilizzare Resource Governor per ridurre la memoria massima allocata per ogni
query. Di conseguenza, tuttavia, query simultanee che utilizzerebbero altrimenti grandi quantità
di memoria utilizzano invece tempdb, introducendo operazioni di I/O più casuali, che possono
ridurre la velocità effettiva complessiva. Sebbene per molti carichi di lavoro dei data warehouse
possa rivelarsi utile limitare la quantità di risorse di sistema disponibili per una singola sessione,
questo aspetto viene misurato al meglio tramite l'analisi di carichi di lavoro di query simultanei.
Per ulteriori informazioni su come utilizzare Resource Governor, vedere Resource Governor
(http://msdn.microsoft.com/it-it/library/bb933866.aspx).
È inoltre consigliabile esaminare linee guida e procedure specifiche del fornitore per le soluzioni
Fast Track. In particolare, le soluzioni Fast Track di dimensioni maggiori, con 4 e 8 socket,
possono utilizzare impostazioni di Resource Governor specifiche per ottenere prestazioni ottimali.
In breve, è possibile attuare un compromesso tra la riduzione dei vincoli che offrono prestazioni
migliori per singole query e vincoli più rigorosi che garantiscono il numero di query eseguibili
contemporaneamente.
Per ulteriori informazioni sulle procedure consigliate e gli scenari comuni per Resource Governor,
vedere il white paper relativo all'utilizzo di Resource Governor (http://msdn.microsoft.com/
it-it/library/ee151608.aspx).
Sistema di archiviazione
Nelle architetture di riferimento FTDW in cui le risorse di archiviazione del database sono
rappresentate da unità disco rigido, la gestione della frammentazione è un aspetto cruciale ai
fini del mantenimento di livelli di prestazioni di sistema costanti nel tempo. Per questo motivo,
è necessario specificare una configurazione dell'archiviazione e del file system dettagliata.
Componenti del sistema di archiviazione
Nella figura 3 vengono combinati tre livelli principali di configurazione dell'archiviazione per lo
stack integrato del database. Questo scenario deve essere considerato un caso di riferimento,
in quanto la topologia specifica varia notevolmente a seconda del partner Fast Track. Un tipico
stack del database contiene gli elementi seguenti:
25
Array di dischi fisici: RAID 1+0 con 4 spindle costituisce l'approccio standard illustrato
nella figura 3. In alcune architetture di riferimento dei partner sono stati utilizzati anche
RAID 5 e RAID 6 per SQL Server 2008 R2 e SQL Server 2012.
Assegnazione del volume del sistema operativo (LUN)
Database: utente, temporaneo di sistema, log di sistema
Figura 3. Esempio di architettura di archiviazione completa per un sistema FTDW basato su tre
enclosure di archiviazione con un LUN (volume) per ogni gruppo di dischi
Dettagli della configurazione dell'archiviazione
Per ogni enclosure di archiviazione, effettuare le operazioni indicate di seguito.
1. Creare gruppi di quattro dischi ciascuno, utilizzando RAID 1+0 (RAID 10). Il numero
esatto di gruppi di dischi per ogni enclosure di archiviazione può variare a seconda del
fornitore. Per ulteriori informazioni, vedere la documentazione specifica del fornitore.
In generale il numero è 2 gruppi di dischi RAID10 e 1 gruppo di dischi RAID1 per ogni
enclosure con fattore di forma grande e 5 gruppi di dischi RAID10 per ogni enclosure
con fattore di forma ridotto.
26
Il numero totale di volumi utilizzati come posizioni dei filegroup per i dati primari non
deve superare 32. Se il numero totale di LUN del sistema di archiviazione supera questa
soglia, è possibile utilizzare gruppi di dischi più grandi per ridurre il conteggio di LUN
mantenendo una velocità effettiva di I/O simile. Ad esempio, utilizzare un gruppo di
8 dischi RAID 10 con un LUN anziché un gruppo di 4 dischi RAID 10 con un LUN.
Con gruppi di dischi più grandi, si verifica una certa riduzione della velocità effettiva
e dell'efficienza. Questo comportamento varia a seconda della tecnologia di archiviazione.
2. Dedicare tutti i gruppi di dischi tranne uno ai dati utente primari. Le posizioni dei dati
utente primari equivalgono alle posizioni dei filegroup del database di SQL Server.
Tutte le architetture di riferimento FTDW chiamano uno o due LUN per ogni gruppo di
dischi dei dati utente primari. A questo proposito, consultare le linee guida specifiche del
fornitore relative all'architettura di riferimento scelta. Questi LUN vengono utilizzati per
archiviare i file di database di SQL Server (file con estensioni mdf e ndf).
3. Assicurarsi che l'assegnazione del processore di archiviazione primario per ogni volume
di disco allocato ai dati primari nell'enclosure di archiviazione sia bilanciata
uniformemente. Ad esempio, una enclosure di archiviazione con quattro volumi di disco
allocati per i dati primari avrà due volumi assegnati al processore di archiviazione "A"
e due assegnati al processore di archiviazione "B".
4. Creare un LUN nel gruppo di dischi rimanente per ospitare i log delle transazioni del
database. Per alcune configurazioni Fast Track di dimensioni maggiori le allocazioni dei
log sono limitate solo alle prime enclosure di archiviazione nel sistema. In questo caso
i gruppi di dischi aggiuntivi vengono utilizzati per la gestione temporanea non di
database o restano non popolati per ridurre i costi.
Per ogni database, effettuare le operazioni seguenti:
1. Creare almeno un filegroup contenente un file di dati per ogni LUN primario. Assicurarsi
di impostare la stessa dimensione per tutti i file. Se si prevede di utilizzare più filegroup
in un singolo database per segregare gli oggetti, ad esempio un database di gestione
temporanea per supportare il caricamento, assicurarsi di includere tutti i LUN primari
come posizioni per ogni filegroup.
2. Quando si creano i file per ogni filegroup, preallocarli alla dimensione maggiore prevista,
con una dimensione sufficientemente elevata per contenere gli oggetti pianificati.
3. Disabilitare l'opzione di aumento automatico delle dimensioni per i file di dati e aumentare
manualmente le dimensioni di tutti i file di dati quando ci si avvicina al limite di
dimensione corrente.
4. Per ulteriori informazioni sulle indicazioni relative ai database utente e ai filegroup,
vedere la sezione Gestione della frammentazione dei dati di questo documento.
Per tempdb, effettuare le operazioni seguenti:
1. Preallocare spazio e quindi aggiungere un singolo file di dati per ogni LUN.
Assicurarsi di impostare la stessa dimensione per tutti i file.
2. Assegnare file di log temporanei in uno dei LUN dedicati ai file di log.
27
3. Abilitare l'aumento automatico delle dimensioni. In generale, l'utilizzo di un valore
incrementale elevato è adatto a carichi di lavoro di data warehouse. Un valore equivalente
al 10% della dimensione file iniziale costituisce un punto di partenza ragionevole.
4. Seguire le procedure consigliate standard di SQL Server relative alle considerazioni
sulle dimensioni per i database e per tempdb. Può essere necessaria un'allocazione di
spazio maggiore durante la fase di migrazione o durante il caricamento iniziale dei dati
del data warehouse. Per ulteriori informazioni, vedere Pianificazione delle capacità per
tempdb (http://msdn.microsoft.com/it-it/library/ms345368.aspx) nella documentazione
online di SQL Server.
Per il log delle transazioni, effettuare le operazioni seguenti:
1. Creare un singolo file di log delle transazioni per ogni database in uno dei LUN
assegnati allo spazio dei log delle transazioni. Distribuire i file di log per database diversi
tra i LUN disponibili oppure utilizzare più file di log per l'aumento delle dimensioni dei log
in base alle esigenze.
2. Abilitare l'opzione di aumento automatico delle dimensioni per i file di log.
3. Verificare che la capacità dei log sia allineata ai requisiti indicati nella tabella 5. Alcune
variazioni sono accettabili, a seconda delle caratteristiche di progettazione di sistema
specifiche.
RAM di sistema (GB)
Capacità valutata di Fast
Track (terabyte)
<= 96
<=10
<= 128
>10
<=40
Allocazione minima
consigliata dei log
Spazio libero con
mirroring (GB)
300 GB x 1 volume
300 GB x 2 volumi
oppure
600 GB x 1 volume
Tabella 5. Indicazioni relative all'allocazione dei log
Fare riferimento alle procedure consigliate esistenti per l'allocazione e la gestione dei log delle
transazioni di SQL Server.
Risorse di archiviazione SSS (Solid State Storage)
Le architetture di riferimento FTDW che utilizzano questo tipo di risorse per l'archiviazione
dei dati primari presentano molti vantaggi, tra cui la semplificazione delle attività di gestione,
costi operativi inferiori ed esigenze di manutenzione prevedibili.
Gestione semplificata: le risorse di archiviazione SSS non richiedono la gestione della
frammentazione. È comunque consigliabile utilizzare l'opzione di avvio -E di SQL Server, ma
non sono necessarie ulteriori operazioni di ottimizzazione o gestione dell'allocazione delle pagine.
Questa semplificazione rende la gestione a lungo termine degli ambienti FTDW sensibilmente
più agevole. È inoltre possibile utilizzare gruppi di dischi più grandi e conteggi di volumi/LUN
minori senza conseguenze negative sulle prestazioni. Questa modifica semplifica la creazione
e la gestione dei filegroup.
28
Resilienza di I/O: l'uso di risorse SSS riduce al minimo il rallentamento delle prestazioni in
situazioni di concorrenza o elevata frammentazione delle pagine. Il carico di lavoro di lettura
casuale mista (ricerca), inoltre, non influisce negativamente sui modelli di I/O con richieste
(analisi) elevate.
Esigenze di manutenzione prevedibili: molte opzioni di archiviazione a stato solido offrono
monitoraggio basato su software della durata delle operazioni di scrittura con una minore
frequenza di errori fisici di difficile previsione.
Costi di esercizio inferiori: benché più costose in termini di prezzo di acquisto, le risorse SSS
garantiscono un rapporto tra velocità effettiva di I/O e capacità per unità estremamente più
vantaggioso. Le velocità di I/O del carico di lavoro FTDW per dischi rigidi SAS 10k da 300 GB
sono in media pari a 50 MB. Unità SSD MLC aziendali offrono tra 150 e 200 MB a una capacità
di 600 GB. La tecnologia di archiviazione SSS consente inoltre di ridurre i consumi energetici,
la produzione di calore e supporta spesso soluzioni a densità più elevata.
Configurazione delle risorse di archiviazione SSS (Solid State Storage)
Fare riferimento alle indicazioni riportate di seguito per adeguare le linee guida standard di
configurazione dell'archiviazione FTDW all'uso di risorse SSS per volumi primari.
Se è necessario il mirroring, è possibile utilizzare RAID1+0 o RAID5. RAID5 garantisce
la capacità migliore senza riduzione delle prestazioni per carichi di lavoro FTDW su unità
SSD.
È possibile ridurre il conteggio di LUN e volumi fino a un solo volume primario per ogni
unità di archiviazione. In alcuni casi è utile che il conteggio dei volumi primari sia un
multiplo del conteggio dei core della CPU. Il conteggio minimo dei volumi primari è due.
È possibile posizionare anche il log delle transazioni in un'unità SSD, ma i carichi di
lavoro FTDW non sono in genere associati ai log. È possibile ridurre i costi posizionando
il log in unità disco rigido tradizionali. Lo stesso vale per l'archiviazione locale per
l'installazione di SQL Server e Windows Server.
Le indicazioni per la gestione della frammentazione delle pagine e il caricamento
parallelo degli indici cluster possono essere ignorate, perché la frammentazione logica
del database non influisce sulle prestazioni di I/O delle unità SSD.
Procedure consigliate per FTDW relative a SQL Server
Le procedure per i carichi di lavoro Fast Track vengono convalidate e documentate in due casi.
Il primo si verifica se una procedura Fast Track differisce in modo sostanziale dalle procedure
consigliate definite per SQL Server. Il secondo caso si verifica in scenari in cui le procedure
esistenti risultano mancanti o non facilmente accessibili. Le procedure indicate di seguito
non sono concepite per essere esaustive, in quanto è già disponibile una nutrita raccolta di
documenti per la distribuzione di database di SQL Server. Per quanto concerne diversi aspetti
correlati alle distribuzioni FTDW, è consigliabile fare riferimento alla documentazione tecnica
e alle procedure consigliate di SQL Server.
29
Importante: in questa guida sono disponibili diversi collegamenti alla documentazione di SQL
Server 2008 R2. La maggior parte di queste linee guida è comunque valida per SQL Server
2012. È opportuno consultare le versioni aggiornate di questi documenti via via che vengono
rese disponibili. Le versioni future di questa guida di riferimento conterranno collegamenti
aggiornati ai nuovi documenti disponibili.
Architettura dei dati
Struttura delle tabelle
Il tipo di tabella utilizzata per archiviare i dati nel database ha importanti implicazioni sulle
prestazioni di accesso sequenziale. È molto importante progettare lo schema fisico tenendo
presente questo elemento per consentire ai piani di query di produrre quanto più I/O
sequenziale possibile.
La scelta di un tipo di tabella è determinata dalla modalità più frequente con cui verrà eseguito
l'accesso ai dati contenuti nella tabella stessa. È possibile utilizzare le informazioni seguenti per
determinare il tipo di tabella da considerare in base ai dettagli dei dati archiviati.
Tabelle heap
Le tabelle heap offrono I/O sequenziale pulito per le analisi di tabella e in genere un overhead
minore in termini di frammentazione. Le tabelle heap non sono direttamente concepite per
consentire analisi basate su intervalli ottimizzate (accesso diretto), come avviene invece nelle
tabelle con indici cluster. In caso di analisi di intervalli, viene analizzata l'intera tabella heap
(o la partizione a intervalli appropriata, se è applicato il partizionamento).
Poiché l'analisi delle tabelle heap raggiunge la massima velocità effettiva a 32 file, per l'uso di
heap per tabelle dei fatti di grandi dimensioni in sistemi con conteggi elevati di LUN (oltre 32)
o core (oltre 16) può essere necessario utilizzare Resource Governor, vincoli relativi al grado di
parallelismo o modifiche all'allocazione dei file di database Fast Track standard.
È preferibile utilizzare tabelle heap nei casi seguenti:
30
La maggior parte delle query ad alta priorità sul riferimento di tabella contiene predicati
che fanno riferimento a un'ampia gamma di colonne diverse o non include predicati di
colonna.
Le query eseguono in genere analisi di grandi dimensioni anziché analisi limitate
a intervalli, ad esempio tabelle utilizzate esclusivamente per popolare cubi di Analysis
Services. In questi casi, la tabella heap deve essere partizionata con la stessa
granularità del cubo di Analysis Services popolato.
I requisiti del carico di lavoro di query vengono soddisfatti senza l'overhead incrementale
della gestione degli indici oppure le prestazioni di caricamento sono di importanza
essenziale: le tabelle heap sono più veloci da caricare.
Tabelle con indici cluster
Nell'ambiente di data warehouse un indice cluster è più efficace se la chiave è una colonna
qualificata con intervalli (ad esempio data) utilizzata frequentemente in restrizioni per il carico di
lavoro di query pertinente. In questa situazione, l'indice può essere utilizzato sostanzialmente
per limitare e ottimizzare i dati da analizzare.
È preferibile utilizzare tabelle con indici cluster nei casi seguenti:
Nella tabella sono presenti colonne qualificate con intervalli utilizzate in restrizioni di
query per la maggior parte degli scenari di carichi di lavoro di query con priorità elevata
sulla tabella. Per le configurazioni FTDW, la colonna data partizionata di un indice
cluster deve essere anche la chiave di indice cluster.
Nota: la scelta di una chiave di indice cluster che non sia la colonna di partizione data
per una tabella con indice cluster può essere vantaggiosa in alcuni casi. È tuttavia
probabile che questa scelta causi frammentazione, a meno che non vengano caricate
partizioni complete, perché nuovi dati che si sovrappongono a intervalli di chiavi di indice
cluster esistenti creano divisioni di pagina.
Le query sulla tabella in genere eseguono ricerche granulari o vincolate agli intervalli
e non analisi complete o analisi di più intervalli di grandi dimensioni.
Partizionamento delle tabelle
Il partizionamento delle tabelle può essere uno strumento importante per la gestione della
frammentazione nei database FTDW. È possibile utilizzare il partizionamento, ad esempio,
per aggiornare o eliminare grandi blocchi di dati utente basati su intervalli di una tabella senza
occuparsi di altre parti della tabella stessa. Al contrario, l'eliminazione riga per riga da un indice
cluster può provocare una frammentazione di extent significativa. Uno scenario comune consiste
nel ricreare partizioni più nuove con l'aumentare del periodo di permanenza delle partizioni
e quando la frequenza delle operazioni DML per l'intervallo di dati diminuisce. La partizione
è ora stabile rispetto alle operazioni DML e ha una frammentazione di extent minima.
Le tabelle di grandi dimensioni utilizzate prevalentemente per il popolamento di cubi di SQL
Server Analysis Services possono inoltre essere create come tabelle heap partizionate, con il
partizionamento delle tabelle allineato al partizionamento del cubo. All'accesso, vengono
analizzate solo le partizioni pertinenti della tabella di grandi dimensioni. Le partizioni che
supportano la modalità ROLAP di Analysis Services possono essere strutturate meglio come
indici cluster.
Per ulteriori informazioni sul partizionamento delle tabelle, vedere il white paper sulle strategie
relative alle tabelle partizionate e agli indici con SQL Server 2008 (http://msdn.microsoft.com/
it-it/library/dd578580(v=SQL.100).aspx).
31
Indicizzazione
Per la creazione di indici FTDW sono disponibili le linee guida descritte di seguito:
Utilizzare un indice cluster per intervalli di date o restrizioni comuni.
Utilizzare un indice columnstore se possibile. Nella sezione seguente vengono descritte
le procedure consigliate per l'utilizzo di indici columnstore in ambienti FTDW.
Limitare l'indicizzazione non cluster alle situazioni in cui è necessaria la ricerca
granulare e il partizionamento delle tabelle non garantisce prestazioni sufficienti.
Se possibile, utilizzare un indice columnstore anziché un indice non cluster.
Gli indici di copertura non cluster possono risultare vantaggiosi per alcuni carichi di
lavoro del data warehouse. L'uso di questi indici deve essere valutato caso per caso
e confrontato rispetto all'indice columnstore.
Indici columnstore in memoria xVelocity
In SQL Server 2012 è stata introdotta una nuova funzionalità di accelerazione delle query del
data warehouse basata sulla tecnologia a colonne, gli indici columnstore. Questi nuovi indici,
combinati con funzionalità di elaborazione delle query migliorate, garantiscono prestazioni più
elevate nei data warehouse per un'ampia gamma di query analitiche.
Gli indici columnstore ottimizzati in memoria xVelocity sono columnstore "puri" (non ibridi)
perché contengono in pagine distinte tutti i dati delle colonne incluse. Gli indici columnstore
consentono un miglioramento delle prestazioni di analisi di I/O e delle frequenze dei riscontri del
buffer e sono ben allineati alla metodologia di progettazione FTDW.
Procedure consigliate
Gli oggetti indice columnstore si trovano nella stessa posizione delle tabelle e vengono creati
analogamente agli indici non cluster. Questo comportamento non implica la necessità di
capacità di archiviazione incrementale. Non è necessario creare indici columnstore in filegroup
separati, a meno che non si prevedano frequenti modifiche alla tabella di destinazione
dell'indice. La gestione degli indici columnstore in filegroup separati può semplificare la gestione
della frammentazione delle pagine nel tempo in ambienti estremamente volatili.
Creazione di indici columnstore per modelli di dati normalizzati
I modelli di dati normali (ovvero 3NF) attivano spesso join tra due o più tabelle (dei fatti) di
grandi dimensioni. Questi tipi di join non sono attualmente ideali per l'elaborazione degli indici
columnstore e possono mostrare una riduzione delle prestazioni in termini di piani di query
di indici non columnstore. Gli approcci descritti di seguito possono aiutare a evitare questo
problema relativo ai modelli di dati normali:
32
Avvalersi di hint a livello di query per bloccare l'utilizzo dell'elaborazione degli indici
columnstore.
Utilizzare OPTION(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
Riscrivere le query. Per ulteriori informazioni, vedere le risorse elencate nella sezione
Procedure consigliate generali per gli indici columnstore di questo documento.
Provare a omettere chiavi di join comuni da una tabella interessata dai join SQL che
mostrano una riduzione delle prestazioni dei piani di query di indici non columnstore.
L'omissione della chiave di join dall'indice columnstore in una tabella può far sì che
l'indice columnstore non venga utilizzato per le query che creano un join nella colonna
omessa. Questo approccio può essere utile negli ambienti in cui non possono essere
applicate opzioni a livello di query. Tenere presente che l'omissione di una colonna
dall'indice columnstore non garantisce come risultato un piano di query migliore e può
influire su altre query per cui l'indice columnstore offrirebbe vantaggi in termini di
prestazioni. Se si sceglie di utilizzare questa opzione, la selezione di una colonna dalla
tabella più piccola tra quelle interessate può ridurre l'impatto in termini di prestazioni
sulle altre query. Si noti che le chiavi primarie dichiarate (DDL) devono essere incluse
nell'indice columnstore, limitando a volte le colonne di join disponibili. Anche se si
omette una colonna chiave primaria dalla definizione dell'indice columnstore, tutte le
colonne chiave primaria vengono aggiunte automaticamente all'indice columnstore
quando questo viene creato.
Mentre i modelli di dati normali non vengono ottimizzati perfettamente per gli indici columnstore
nella versione corrente, è importante notare che il benchmarking FTDW è basato su una versione
modificata di TPC-H, che è un modello normalizzato. Sono stati comunque osservati miglioramenti
significativi per carichi di lavoro simultanei che combinano piani di query di indici columnstore
e non columnstore, anche per quanto riguarda la velocità effettiva valutata di FTDW, per cui le
prestazioni complessive del carico di lavoro sono risultate quasi raddoppiate in alcuni casi.
Creazione di indici columnstore per modelli di dati dimensionali
Seguire le procedure consigliate standard per gli indici columnstore per i modelli dimensionali
quali gli schemi star. Questo può essere considerato uno scenario ottimale per l'elaborazione
degli indici columnstore.
Gestione della memoria per gli indici columnstore
Un'architettura di riferimento FTDW convalidata per SQL Server 2012 dispone in genere di RAM
di sistema totale maggiore rispetto a configurazioni simili per SQL Server 2008 R2. Il motivo
principale è dato dal fatto che i carichi di lavoro migliorati per gli indici columnstore vengono
eseguiti in modo più efficiente con pool di memoria di dimensioni maggiori. È consigliabile
utilizzare sempre Resource Governor per impostare la quantità massima di memoria per ogni
sessione per gli ambienti FTDW in cui si prevede di utilizzare indici columnstore. Nelle architetture
di riferimento FTDW convalidate è documentato l'utilizzo di impostazioni di Resource Governor
per ottenere prestazioni ottimali per Fast Track e tali valori possono essere considerati un
punto di partenza per i carichi di lavoro dei clienti. Idealmente l'impostazione verrà valutata
e ottimizzata in modo specifico per il carico di lavoro di un cliente dopo l'installazione del sistema.
Il comando SQL riportato di seguito consente di configurare Resource Governor in SQL Server
in modo conforme a queste indicazioni. In questo caso la quantità massima di memoria per ogni
sessione è impostata sul 19%.
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=19);
33
Procedure consigliate generali per gli indici columnstore ottimizzati in memoria xVelocity
Le linee guida di riferimento per FTDW includono solo procedure esclusive per Fast Track.
Per ulteriori informazioni sugli indici columnstore, vedere la guida all'ottimizzazione degli indici
columnstore di SQL Server 2012 (http://social.technet.microsoft.com/wiki/contents/articles/
sql-server-columnstore-performance-tuning.aspx) e le domande frequenti relative agli indici
columnstore di SQL Server 2012 (http://social.technet.microsoft.com/wiki/contents/articles/
sql-server-columnstore-index-faq.aspx).
Statistiche del database
Le scelte relative ai momenti in cui eseguire le statistiche e alla frequenza con cui aggiornarle
non può dipendere dall'esame di singoli fattori. I tempi di manutenzione disponibili e la riduzione
complessiva delle prestazioni di sistema sono in genere considerati i due fattori principali da
considerare quando si affrontano le problematiche correlate alle statistiche del database.
Per ulteriori informazioni, vedere la pagina relativa alle statistiche per SQL Server 2008
(http://msdn.microsoft.com/it-it/library/dd535534.aspx).
Procedure consigliate
Attenersi alle procedure consigliate relative alle statistiche del database indicate di seguito:
34
Utilizzare le opzioni AUTO CREATE e AUTO UPDATE (sincrone o asincrone) per le
statistiche (impostazione predefinita di sistema in SQL Server). L'utilizzo di questa
tecnica riduce al minimo la necessità di eseguire statistiche manualmente.
Se è necessario raccogliere statistiche manualmente, le statistiche devono idealmente
essere raccolte per tutte le colonne di una tabella. Se non è possibile eseguire
statistiche per tutte le colonne, è consigliabile raccogliere almeno le statistiche per tutte
le colonne utilizzate in una clausola WHERE o HAVING e nelle chiavi di join. Poiché la
creazione di indici comporta la compilazione di statistiche sulla chiave di indice,
non è necessario eseguire tale operazione in modo esplicito.
Le statistiche composte (multicolonna) sono di importanza cruciale in molti scenari
di join. I join di dimensione dei fatti che coinvolgono chiavi di join composte possono
implicare piani di ottimizzazione di loop annidati non ideali in assenza di statistiche
composte. Statistiche automatiche non comportano la creazione, l'aggiornamento
o la sostituzione di statistiche composte.
Le statistiche che interessano un valore di chiave in aumento, ad esempio una data
in una tabella dei fatti, devono essere aggiornate manualmente al termine di ogni
operazione di caricamento incrementale. In tutti gli altri casi, le statistiche possono
essere aggiornate con una frequenza minore. Se si determina che l'opzione
AUTO_UPDATE_STATISTICS non è sufficiente, eseguire le statistiche in base a una
pianificazione.
Compressione
Le configurazioni FTDW sono progettate con la compressione di pagina abilitata. È consigliabile
utilizzare la compressione di pagina in tutte le tabelle dei fatti. La compressione di piccole
tabelle delle dimensioni, ovvero quelle che includono meno di un milione di righe, è facoltativa.
Con tabelle delle dimensioni più grandi è spesso consigliabile utilizzare la compressione di
pagina. In entrambi i casi, la compressione delle tabelle delle dimensioni deve essere valutata
in base al caso di utilizzo. La compressione di riga è un'opzione aggiuntiva che offre rapporti di
compressione ragionevoli per determinati tipi di dati.
La compressione di pagina di SQL Server consente di ridurre le dimensioni dei dati in tabelle,
indici e partizioni. In questo modo, viene ridotta la quantità di spazio fisico necessario per
archiviare le tabelle utente, consentendo l'inserimento di un numero maggiore di dati nel pool di
buffer (memoria) di SQL Server. Un vantaggio derivante consiste nella riduzione del numero di
richieste di I/O elaborate dalle risorse di archiviazione fisiche.
La quantità di compressione effettiva che può essere ottenuta varia in base ai dati archiviati
e alla frequenza di campi dati duplicati nei dati. Se i dati sono altamente casuali, i vantaggi della
compressione sono molto limitati. Anche nelle migliori condizioni, l'utilizzo della compressione
aumenta l'impiego della CPU per comprimere e decomprimere i dati, ma riduce nel contempo
i requisiti di spazio nei dischi fisici e nella maggior parte dei casi migliora i tempi di risposta delle
query grazie all'elaborazione delle richieste di I/O dal buffer di memoria. In genere, la compressione
di pagina ha un rapporto di compressione (dimensioni originali/dimensioni compresse)
compreso tra 2 e 7:1, con un rapporto 3:1 che rappresenta una tipica stima ragionevole.
I risultati possono variare a seconda delle caratteristiche dei dati.
Gestione della frammentazione dei dati
La frammentazione può verificarsi a diversi livelli, che devono essere tutti controllati per
mantenere l'I/O sequenziale. Un obiettivo principale di una configurazione FTDW consiste
nel mantenere i dati ordinati in sequenza quanto più possibile limitando al contempo la
frammentazione sottostante. Se si permettono fenomeni di frammentazione, le prestazioni di
sistema complessive subiscono conseguenze negative.
Benché la deframmentazione periodica sia necessaria, le linee guida seguenti consentono di
ridurre al minimo il numero di processi di deframmentazione dispendiosi in termini di tempo.
Frammentazione del file system
I blocchi del disco per ogni file di database devono essere mantenuti contigui nel disco fisico
all'interno del file system NTFS. È possibile impedire la frammentazione a questo livello tramite
la preallocazione dei file alle rispettive dimensioni massime previste al momento della creazione.
È consigliabile evitare l'uso di strumenti di deframmentazione su file system NTFS. Questi
strumenti sono progettati per l'esecuzione a livello di sistema operativo e non riconoscono le
strutture di file di dati interne di SQL Server.
35
Frammentazione di extent
In SQL Server tutte le pagine in un file, indipendentemente dall'associazione di tabella, possono
essere soggette a interleave fino alla dimensione degli extent (2 MB) o al livello pagina (8 KB).
Questa situazione si verifica in genere a causa di operazioni DML simultanee o di un numero
eccessivo di aggiornamenti o eliminazioni a livello di riga.
La riscrittura completa della tabella o delle tabelle in questione è l'unico modo per garantire
un'allocazione di pagina ottimale all'interno di un file. Non sono disponibili metodi alternativi per
risolvere questo tipo di frammentazione del database. Per questo motivo, è importante seguire
le linee guida per la configurazione di SQL Server e le procedure consigliate per il caricamento
dei dati e la gestione di DML.
La query seguente fornisce informazioni chiave per la valutazione della frammentazione logica
per una tabella FTDW. La metrica con priorità più alta è quella relativa alle dimensioni medie
dei frammenti. Questo valore restituisce un numero intero che rappresenta il numero medio di
pagine di SQL Server inserite in cluster in extent contigui.
SELECT db_name(ps.database_id) as database_name
,object_name(ps.object_id) as table_name
,ps.index_id
,i.name
,cast (ps.avg_fragmentation_in_percent as int) as [Logical Fragmentation]
,cast (ps.avg_page_space_used_in_percent as int) as [Avg Page Space Used]
,cast (ps.avg_fragment_size_in_pages as int) as [Avg Fragment Size In Pages]
,ps.fragment_count as [Fragment Count]
,ps.page_count
,(ps.page_count * 8)/1024/1024 as [Size in GB]
FROM sys.dm_db_index_physical_stats (DB_ID() --NULL = All Databases
, OBJECT_ID('$(TABLENAME)')
,1
, NULL
, 'SAMPLED') AS ps
--DETAILED, SAMPLED, NULL = LIMITED
INNER JOIN sys.indexes AS i
on (ps.object_id = i.object_id AND ps.index_id = i.index_id)
WHERE ps.database_id = db_id()
and ps.index_level = 0;
36
Nella tabella seguente sono presentate linee guida di carattere generale per l'interpretazione
dei valori delle dimensioni medie dei frammenti.
Dimensioni medie dei
frammenti
>400
Stato
Azione
Ideale
Si tratta di un valore ideale che può essere
difficile da mantenere per alcune strutture
di dati.
300-399
Verde
La tabella offrirà buone prestazioni di I/O
e non richiede la manutenzione per la
frammentazione logica.
150-299
Giallo
È molto probabile che la frammentazione
logica influisca sull'efficienza di I/O.
È consigliabile eseguire la manutenzione
per migliorare il numero dei frammenti.
10-149
Rosso
Ingente frammentazione logica. Elevate
richieste di I/O su questa struttura
produrranno notevoli spostamenti delle
testine dei dischi e ridurranno l'efficienza di
I/O complessiva del sistema.
<10
Rosso
Valori delle dimensioni medie dei frammenti
così bassi significano in genere che
l'opzione di avvio -E di SQL Server non
è stata impostata o non viene riconosciuta
all'avvio.
Tabella 6. Valori delle dimensioni medie dei frammenti
È infine importante notare che i risultati relativi alle dimensioni medie dei frammenti non devono
essere valutati per le tabelle o le partizioni di dimensioni inferiori a 500 MB. Strutture di dati di
dimensioni ridotte non includono un numero totale di pagine sufficiente a ottenere conteggi di
frammenti distintamente efficienti. Queste strutture di dati di dimensioni limitate rappresentano
inoltre richieste di dati relativamente piccole e hanno un impatto perlopiù irrilevante
sull'efficienza di I/O complessiva del sistema. I risultati migliori vengono spesso osservati
gestendo solo le tabelle più grandi e dall'accesso più frequente in un ambiente di data warehouse.
Frammentazione degli indici
Un indice può seguire un ordine fisico (pagina) e logico (indice) diverso.
Non utilizzare il comando ALTER INDEX REORGANIZE per risolvere questo tipo di
frammentazione, perché in questo modo si potrebbero eliminare i vantaggi offerti dalle
allocazioni di grandi dimensioni. La ricompilazione di un indice o l'utilizzo di INSERT...SELECT
per inserire dati in una nuova copia dell'indice (che consente di evitare un nuovo ordinamento)
può risolvere questo problema. Qualsiasi processo ALTER INDEX REBUILD deve specificare
SORT_IN_TEMPDB=TRUE per evitare la frammentazione del filegroup di destinazione.
L'impostazione di MAXDOP su 1 è ideale, ma può provocare frequenze di caricamento molto
ridotte. In alcuni casi, è possibile impostare i valori di MAXDOP fino a 8. Per ulteriori
informazioni, vedere la sezione Caricamento dei dati di questo documento.
37
Più filegroup
È possibile creare filegroup separati per ridurre al minimo la frammentazione logica per casi di
utilizzo di dati volatili come quelli descritti di seguito:
Tabelle o indici che vengono eliminati e ricreati di frequente (lasciando gap nel layout di
archiviazione, riempiti da altri oggetti).
Indici per i quali non esiste altra scelta se non quella di supportare una frammentazione
elevata a causa delle divisioni di pagina, come i casi in cui dati incrementali che si
sovrappongono perlopiù all'intervallo di chiavi di indice cluster esistente vengono caricati
di frequente.
Tabelle più piccole, come le tabelle delle dimensioni, caricate in incrementi
relativamente ridotti e che possono essere inserite in un filegroup volatile per impedire
l'interleave delle righe con tabelle delle transazioni o dei fatti di grandi dimensioni.
Database dell'area di gestione temporanea da cui vengono inseriti dati nella tabella di
destinazione finale.
Altre tabelle possono essere inserite in un filegroup non volatile. Anche tabelle dei fatti molto
grandi possono essere inserite in filegroup separati.
Caricamento dei dati
L'architettura dei componenti Fast Track viene bilanciata per ottenere velocità di analisi
medie superiori con l'accesso sequenziale ai dischi. Per mantenere queste velocità di analisi,
è necessario accertarsi con attenzione che i dati siano organizzati secondo un layout contiguo
all'interno del file system di SQL Server.
Questa sezione è articolata nei due approcci generali descritti di seguito, ovvero il caricamento
incrementale e la migrazione dei dati. Queste linee guida sono specifiche, ma non esclusive,
dei data warehouse Fast Track.
Per ulteriori informazioni sul caricamento bulk di SQL Server, vedere la guida all'ottimizzazione
delle prestazioni di caricamento dei dati (http://msdn.microsoft.com/it-it/library/dd425070.aspx).
Un'altra utile risorsa è costituita dalla guida alle procedure consigliate per il caricamento dei dati
di Fast Track 3.0. Questa presentazione di Microsoft PowerPoint è disponibile nel portale di
SQL Server Fast Track Data Warehouse (http://msdn.microsoft.com/it-it/library/dd425070.aspx).
Benché inizialmente basato su SQL Server 2008 R2, questo documento è comunque valido
anche per SQL Server 2012.
Caricamenti incrementali
In questa sezione vengono illustrati gli scenari di caricamento giornalieri comuni di un ambiente
di data warehouse. Sono inclusi scenari di caricamento con uno o più degli attributi seguenti:
38
Dimensioni ridotte per quanto riguarda la memoria di sistema disponibile
Le operazioni di ordinamento dei caricamenti sono appropriate alla memoria disponibile
Dimensioni ridotte per quanto riguarda le righe totali nell'oggetto di caricamento di
destinazione
È consigliabile prendere in considerazione le linee guida seguenti quando si caricano tabelle
heap e con indici cluster.
Processo di caricamento di tabelle heap
È possibile implementare inserimenti bulk per le tabelle heap come processo seriale o parallelo.
Attenersi ai suggerimenti seguenti:
Per eseguire lo spostamento di dati nella tabella heap di destinazione, utilizzare BULK
INSERT con l'opzione TABLOCK. Se la tabella permanente finale è partizionata,
utilizzare l'opzione BATCHSIZE, perché il caricamento in una tabella partizionata
provoca il verificarsi di un ordinamento in tempdb.
Per migliorare le prestazioni relative ai tempi di caricamento quando si importano set di
dati di grandi dimensioni, eseguire più operazioni di inserimento bulk simultaneamente
per utilizzare il parallelismo nel processo bulk.
Processo di caricamento di indici cluster
Sono disponibili due approcci generali per caricare tabelle con indici cluster con
frammentazione minima delle tabelle.
Opzione 1
Utilizzare BULK INSERT per caricare dati direttamente nella tabella di destinazione.
Per prestazioni ottimali, il set completo dei dati caricati deve essere inserito in un ordinamento
in memoria. Tutti i dati caricati devono essere gestiti da una singola operazione di commit
impostando BATCHSIZE su 0. Questa impostazione impedisce l'interleave dei dati in più batch
e la generazione di divisioni di pagina. Se si utilizza questa opzione, il caricamento deve essere
a thread singolo.
Opzione 2
Creare una tabella di gestione temporanea corrispondente alla struttura (incluso il partizionamento)
della tabella di destinazione:
39
Eseguire un inserimento bulk seriale o multithreading nella tabella di gestione
temporanea con indice cluster vuota utilizzando valori delle dimensioni batch moderati
e diversi da zero per evitare che gli ordinamenti ricadano su tempdb. Un certo livello di
parallelismo consente di ottenere prestazioni ottimali. L'obiettivo di questo passaggio
sono le prestazioni. Per questo motivo, le divisioni di pagina e la frammentazione logica
provocate da inserimenti paralleli e/o simultanei non costituiscono un problema.
L'inserimento dalla tabella di gestione temporanea alla tabella con indice cluster di
destinazione utilizzando un'unica istruzione SELECT INSERT...CATCH con MAXDOP
impostato su 1 garantisce una frammentazione di extent minima, ma spesso a discapito
delle prestazioni. È possibile impostare MAXDOP su valori fino a 8 per migliorare le
prestazioni di caricamento, ma con l'aumentare del parallelismo aumenterà anche la
frammentazione di extent. È opportuno valutare caso per caso l'effettivo bilanciamento
ottenuto con questo compromesso.
Opzione 3
Per questa opzione è necessario l'utilizzo di due filegroup e due o più tabelle. Questo approccio
richiede una tabella con indice cluster partizionata ed è ideale per le tabelle in cui si osservano
livelli elevati di frammentazione logica nelle partizioni più aggiornate con poca o nulla attività di
modifica nelle partizioni meno recenti. L'obiettivo complessivo consiste nel posizionare le
partizioni volatili in un filegroup dedicato e rendere permanenti tali partizioni o spostarle nel
filegroup statico quando non ricevono più nuovi record o modifiche ai record esistenti:
40
Creare due filegroup, in base alle linee guida di FTDW. Uno sarà dedicato alle partizioni
volatili e l'altro a quelle statiche. Una partizione volatile è una partizione in cui più del
10% delle righe verrà modificato nel tempo. Una partizione statica è una partizione non
volatile.
Compilare la tabella partizionata con indice cluster primaria nel filegroup statico.
Compilare una tabella coerente con uno dei due approcci generali seguenti:
o Una singola tabella heap con un vincolo che esegue il mirroring dello schema di
partizione della tabella primaria. Questo vincolo deve rappresentare l'intervallo
volatile del set di dati primario e può estendersi a uno o più intervalli di partizione
dello schema della tabella primaria. Questo approccio è particolarmente utile se
le prestazioni di caricamento iniziali rappresentano i principali criteri decisionali,
in quanto i caricamenti in un heap sono in genere più efficienti rispetto a quelli in
un indice cluster.
o Una singola tabella con indice cluster con uno schema di partizione coerente con
la partizione di tabella primaria. Questo approccio consente inserimenti diretti
con un basso grado di parallelismo nella tabella primaria quando le partizioni
volatili diventano permanenti. Quando vengono rese permanenti tramite
l'inserimento nella tabella primaria, le partizioni vengono eliminate e vengono
aggiunti nuovi intervalli.
Compilare una vista che riunisca entrambe le tabelle. In questo modo viene presentata
la combinazione delle due tabelle come singolo oggetto dal punto di vista dell'utente.
Quando gli intervalli di dati volatili diventano statici da un punto di vista dei dati delle
modifiche, utilizzare un processo di permanenza appropriato, ad esempio il cambio di
partizione:
o Se si utilizza una tabella heap con vincolo, spostare i dati in base all'intervallo di
partizione nel filegroup statico tramite l'inserimento nella tabella di gestione
temporanea. Utilizzare CREATE INDEX e il cambio di partizione per spostare
i dati nella tabella primaria. Per ulteriori informazioni su questo tipo di operazione
per le configurazioni FTDW, vedere la sezione Migrazione dei dati di questo
documento.
o Se si utilizza un indice cluster partizionato, utilizzare un grado di parallelismo
minore o uguale a 8. Utilizzare quindi un comando INSERT limitato dall'intervallo
di partizione direttamente nella tabella primaria. Potrebbe essere necessario
impostare il grado di parallelismo su 1 per evitare la frammentazione, a seconda
della concorrenza complessiva del sistema.
Migrazione dei dati
In questa sezione vengono descritti scenari di caricamento di grandi dimensioni occasionali
o poco frequenti in un ambiente di data warehouse. Queste situazioni possono verificarsi durante
la migrazione della piattaforma o il caricamento dei dati di test per il benchmarking del sistema.
In questo argomento sono inclusi scenari di caricamento con uno o più degli attributi seguenti:
Operazioni di caricamento che superano la memoria di sistema disponibile
Operazioni di caricamento con concorrenza e volumi elevati che creano pressione sulla
memoria disponibile
Processo di caricamento di tabelle heap
Per l'elaborazione di caricamenti incrementali, attenersi alle linee guida già descritte a questo
proposito.
Processo di caricamento di indici cluster
Sono disponibili diversi approcci generali per caricare tabelle con indici cluster con
frammentazione minima delle tabelle.
Opzione 1
Utilizzare BULK INSERT per caricare i dati direttamente in una tabella con indice cluster di
destinazione. Le operazioni di ordinamento e le dimensioni complete di commit devono essere
appropriate alla memoria disponibile per ottenere prestazioni ottimali. È necessario prestare
attenzione per fare sì che batch separati di dati caricati non includano intervalli di chiavi di indice
che si sovrappongono.
Opzione 2
Eseguire un inserimento bulk seriale o multithreading in una tabella di gestione temporanea di
indice cluster vuota con una struttura identica. Utilizzare dimensioni batch moderate e diverse
da zero per mantenere gli ordinamenti in memoria. Successivamente, inserire i dati in una
tabella con indice cluster vuota utilizzando una singola istruzione INSERT...SELECT con
MAXDOP impostato su 1.
Opzione 3
Utilizzare inserimenti bulk multithreading in una tabella heap di gestione temporanea conforme
alla partizione, utilizzando valori delle dimensioni batch moderati e diversi da zero per mantenere
gli ordinamenti in memoria. Successivamente, utilizzare istruzioni INSERT...SELECT seriali
o parallele che si estendono su ogni intervallo di partizione per inserire i dati in una tabella con
indice cluster.
41
Opzione 4
Utilizzare operazioni di cambio di partizione in un processo a più passaggi, che in genere offre
i risultati migliori per operazioni di caricamento di grandi dimensioni. Questo approccio aggiunge
maggiore complessità al processo completo ed è progettato per illustrare uno scenario ottimale
per prestazioni di caricamento non elaborate. L'obiettivo principale di questo approccio consiste
nel consentire un'attività di scrittura parallela in tutte le fasi dell'inserimento nelle operazioni
degli indici cluster, senza introdurre frammentazione logica. Questo obiettivo viene realizzato
eseguendo la gestione temporanea della tabella tra più filegroup prima di inserire i dati nella
tabella di destinazione finale.
1. Identificare lo schema di partizione per la tabella con indice cluster di destinazione
finale.
2. Creare un filegroup di gestione temporanea.
3. Creare una tabella heap di gestione temporanea di base non compressa e non
partizionata nel filegroup di gestione temporanea.
4. Eseguire un inserimento bulk dei dati utilizzando WITH TABLOCK nella tabella di
gestione temporanea di base. Più operazioni di copia bulk parallele costituiscono
l'approccio più efficiente se è possibile utilizzare più file di origine. Il numero di
operazioni di caricamento parallele per ottenere la velocità effettiva massima dipende
dalle risorse del server (CPU e memoria) e dai dati caricati.
5. Identificare il numero di filegroup primari da supportare. Questo numero deve essere un
multiplo del numero totale di partizioni nella tabella di destinazione. Il numero rappresenta
anche il numero totale di operazioni CREATE e INSERT INDEX da eseguire
simultaneamente nei passaggi successivi. Ad esempio, per una tabella con 24 partizioni
e un server con otto core, sarebbe indicato un database con otto filegroup primari.
Questa configurazione consente l'esecuzione di otto inserimenti paralleli nei passaggi
successivi, uno per ognuno degli otto filegroup primari e core della CPU. In questo caso,
ogni filegroup conterrebbe dati relativi a tre intervalli di partizione.
6. Creare il numero di filegroup primari in base a quanto determinato in precedenza.
7. Creare una tabella heap di gestione temporanea in ogni filegroup primario per ogni
intervallo di partizione, senza compressione. Creare un vincolo nella tabella di gestione
temporanea corrispondente all'intervallo di partizione equivalente della tabella di
destinazione. Nell'esempio precedente, sarebbero presenti tre tabelle di gestione
temporanea per ogni filegroup primario creato in questo passaggio.
8. Creare la tabella con indice cluster partizionata di destinazione, con compressione di
pagina. Questa tabella deve essere partizionata tra tutti i filegroup primari. Le partizioni
devono essere allineate agli intervalli dei vincoli della tabella heap di gestione
temporanea.
9. Eseguire un'istruzione INSERT o SELECT dalla tabella di gestione temporanea di base
alle tabelle di filegroup di gestione temporanea per ogni filegroup primario. Questa
operazione deve essere eseguita in parallelo. Assicurarsi che il predicato per l'istruzione
INSERT o SELECT corrisponda agli intervalli di partizione equivalenti. Non eseguire mai
più di un'istruzione INSERT o SELECT per ogni filegroup simultaneamente.
42
10. Eseguire un comando CREATE CLUSTERED INDEX con compressione di pagina
per ogni filegroup per le nuove tabelle di gestione temporanea popolate. È possibile
eseguire questa operazione in parallelo, ma mai con un grado di parallelismo maggiore
di 8. Non eseguire mai più di un'istruzione di creazione di indici per ogni filegroup
contemporaneamente. Assicurarsi di utilizzare l'opzione SORT_IN_TEMPDB ogni
volta che si esegue un'operazione CREATE INDEX per evitare la frammentazione
dei filegroup primari. Il numero ottimale di operazioni di creazione di indici simultanee
dipende dalle dimensioni del server, dalla memoria e dai dati stessi. In generale,
cercare di ottenere un utilizzo elevato della CPU in tutti i core senza sottoscrizioni
eccessive (85%-90% di utilizzo totale).
11. Eseguire operazioni di cambio di partizione seriali dalle tabelle di gestione temporanea
alla tabella di destinazione. Questa operazione può essere eseguita anche al termine di
ogni operazione CREATE INDEX di gestione temporanea.
Benchmarking e convalida
In questa sezione viene fornita una descrizione di base dei processi utilizzati per progettare
e qualificare le architetture di riferimento FTDW per SQL Server. L'obiettivo di tali informazioni
consiste nel supportare architetture di riferimento personalizzate o definite dall'utente basate
sulla metodologia FTDW. Per il benchmarking, la risoluzione dei problemi o la verifica delle
architetture di riferimento dei partner pubblicate e preconvalidate, contattare il partner
responsabile della pubblicazione (H-P, Dell, EMC, IBM, Cisco e altri).
Il processo di convalida di FTDW può essere suddiviso nelle due categorie descritte di seguito.
Convalida hardware di base
L'obiettivo della convalida hardware consiste nello stabilire metriche di prestazioni reali anziché
valutate per i componenti hardware principali dell'architettura di riferimento Fast Track. Questo
processo determina le effettive caratteristiche delle prestazioni di base dei componenti
hardware principali nello stack del database.
Convalida del database Fast Track
Stabilendo standard di prestazioni di SQL Server in base a un carico di lavoro FTDW, è possibile
eseguire confronti rispetto ai presupposti relativi alle prestazioni risultanti dal processo di
valutazione hardware di base. In generale, le metriche relative alla velocità effettiva del carico
di lavoro del database devono riflettere almeno l'80% delle velocità di base per le architetture
di riferimento Fast Track convalidate. Le metriche relative alle prestazioni calcolate in questo
processo costituiscono l'elemento di base per i valori di prestazioni delle architetture FTDW
pubblicate e si basano sui carichi di lavoro di query SQL simultanei eseguiti tramite lo strumento
di benchmarking Fast Track Reference Point.
Reference Point è uno strumento software Microsoft distribuito ai partner hardware Fast Track
ed è l'unica infrastruttura tramite cui un'architettura di riferimento Fast Track ufficiale può essere
convalidata e approvata da Microsoft. Lo strumento consente di creare un'istanza di uno schema
di database di riferimento e di disporre di più carichi di lavoro di query simultanei progettati per
identificare i colli di bottiglia e stabilire misure relative alle prestazioni di sistema principali.
43
Convalida di Fast Track con indici columnstore ottimizzati in memoria xVelocity
SQL Server 2012 implementa la tecnologia degli indici columnstore come opzione di
indicizzazione non cluster per tabelle preesistenti. Singole query possono o meno utilizzare
piani di ottimizzazione di indici columnstore a seconda della propria struttura. Ciò significa che
non è possibile prevedere la combinazione di piani di query in formato riga tradizionali e nuovi
piani di query in formato colonna per un ambiente FTDW in un determinato momento.
Per questi motivi, la progettazione e la convalida del sistema FTDW per SQL Server 2012 sono
basate su benchmark di indici non columnstore. I sistemi FTDW sono progettati nell'ottica di
garantire efficienza nei casi in cui non è possibile eseguire l'ottimizzazione in formato colonna
per un determinato periodo di tempo. Si ottengono spesso miglioramenti significativi delle
prestazioni quando sono attivi piani di query di indici columnstore e tali prestazioni possono
essere considerate incrementali per la progettazione di base del sistema.
Le architetture di riferimento Fast Track per SQL Server 2012 convalidate da partner includono
una valutazione aggiuntiva della velocità effettiva logica per benchmark ottimizzati per gli indici
columnstore ed è possibile utilizzare queste cifre per simulare l'effetto positivo sulle prestazioni
delle query che i clienti possono prevedere di ottenere con carichi di lavoro di query simultanei.
Questi numeri sono basati sugli stessi benchmark e sullo stesso schema di FTDW utilizzati per
tutte le convalide di sistema.
Esecuzione della convalida FTDW di base
La convalida di base viene eseguita a livello di sistema operativo con strumenti quali SQLIO.
Il testing dell'applicazione SQL Server non viene eseguito in questa fase e tutte le prove si
basano su scenari sintetici ottimali. L'obiettivo consiste nel garantire che la configurazione
hardware e del sistema operativo sia accurata e offra i risultati previsti in base ai benchmark di
progettazione e sviluppo.
È possibile utilizzare lo strumento di monitoraggio delle prestazioni e dell'affidabilità di Windows
Server, denominato anche perfmon, per il monitoraggio, la registrazione e la creazione di report
delle prestazioni di I/O. È possibile utilizzare uno strumento come SQLIO per testare la
larghezza di banda di I/O. Per ulteriori informazioni su SQLIO, incluse le istruzioni e le posizioni
dei download, vedere il white paper SQLCAT sulle procedure consigliate per il sistema di I/O
prima della distribuzione (http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/21/
predeployment-i-o-best-practices.aspx).
Per generare benchmark di base dell'hardware ci si avvale dei componenti e dei processi di
convalida indicati di seguito.
Testing di base con SQLIO
L'utilizzo di SQLIO viene descritto in modo più completo nell'articolo sulle procedure consigliate.
I test di lettura utilizzano in genere il formato seguente:
sqlio –kR –fSequential -s30 -o120 -b512 d:\iobw.tst –t1
44
In questo caso R indica un test di lettura, 30 è la durata del test in secondi, 120 è il numero di
richieste in attesa emesse, 512 sono le dimensioni in kilobyte dei blocchi delle richieste
effettuate, d:\iobw.tst è il percorso del file di test e 1 è il numero di thread.
Per testare gli scenari di larghezza di banda aggregati, è necessario eseguire più test SQLIO in
parallelo. Una singola istanza di SQLIO deve essere utilizzata per ogni punto di montaggio dei
dati primari (volume del disco). È possibile realizzare la parallelizzazione delle istanze di SQLIO
utilizzando Windows PowerShell o altri metodi di scripting. Per le architetture di riferimento
FTDW convalidate da partner, script di convalida dell'I/O di base possono essere resi disponibili
dai partner.
L'articolo relativo alle procedure consigliate per la fase precedente alla distribuzione contiene
anche informazioni su come tenere traccia dei test utilizzando lo strumento di monitoraggio
delle prestazioni e dell'affidabilità di Windows Server. Registrando e archiviando i risultati di
questi test, è possibile disporre di un riferimento per future attività di analisi delle prestazioni
e risoluzione dei problemi.
Passaggio 1: convalidare la larghezza di banda di I/O
Il primo passaggio per la convalida di una configurazione FTDW consiste nel determinare la
velocità effettiva massima aggregata che è possibile ottenere tra la rete di I/O di archiviazione
e il server. Questo passaggio implica la rimozione di un disco come collo di bottiglia e una
maggiore attenzione ai componenti diversi dai dischi, ovvero HBA, infrastruttura degli switch
e controller di array. Utilizzare i passaggi seguenti per eseguire questa attività con SQLIO:
1. Generare un piccolo file di dati in ogni LUN da utilizzare per i file di database. Questi file
devono essere ridimensionati in modo che tutti i file di dati possano essere contenuti
nella cache di lettura dei controller di array, ad esempio 50 MB per ogni file.
2. Utilizzare SQLIO per eseguire letture sequenziali sul file contemporaneamente con
dimensioni di I/O dei blocchi elevate (512 KB) e almeno due thread di lettura per ogni
file. Assicurarsi di calcolare le letture in attesa aggregate. Ad esempio, 2 thread di lettura
con 50 richieste in attesa rappresenterebbero 100 richieste in attesa totali al LUN di
destinazione.
3. Iniziare con un valore relativamente basso per gli I/O in attesa (-o) e ripetere i test
aumentando questo valore fino a quando non si riscontrano ulteriori miglioramenti nella
velocità effettiva aggregata.
L'obiettivo di questo test consiste nell'ottenere velocità effettiva aggregata ragionevole rispetto
ai limiti teorici dei componenti nel percorso tra il server e l'archiviazione. Questo test consente
di convalidare la larghezza di banda tra il server e i processori di archiviazione SAN, ovvero
i percorsi multipli Fibre Channel.
45
Passaggio 2: convalidare la larghezza di banda di LUN/volumi
Questo test è simile al precedente. Viene tuttavia utilizzato un file di dimensioni maggiori per
eliminare dalla cache del controller i possibili vantaggi assicurati dalla cache di array. Questi file
di test devono essere sufficientemente grandi per simulare le dimensioni dei file di database di
destinazione per ogni volume, ad esempio 25 GB per ogni volume. È consigliabile utilizzare
parametri simili per SQLIO, come descritto nel passaggio 1.
È consigliabile eseguire letture sequenziali di blocchi di grandi dimensioni (512 KB) sui file di
test in ogni volume. È inoltre consigliabile utilizzare un thread singolo per ogni file con una
profondità di richieste in attesa compresa tra 4 e 16 (iniziare con un valore basso e quindi
aumentarlo fino a raggiungere la velocità effettiva massima). Testare innanzitutto ogni volume
singolarmente e quindi i due volumi simultaneamente. La velocità effettiva del gruppo di dischi
varia a seconda del fornitore e della configurazione del sistema di archiviazione, ma è sempre
possibile eseguire confronti con velocità di lettura di singole unità disco rigido. Un gruppo di
4 dischi RAID1+0, ad esempio, potrebbe raggiungere una velocità di lettura massima di circa
quattro volte superiore rispetto alla velocità di lettura di singole unità disco rigido per questo tipo
di modello di lettura di base. Le prestazioni di RAID 1 o RAID 1+0 possono variare in base
al prodotto di archiviazione, in quanto la tecnologia di alcuni fornitori consente letture con
mirroring, che permettono l'elaborazione degli I/O da entrambi i lati della coppia con mirroring
quando vengono ricevute richieste contigue.
Passaggio 3: convalidare la larghezza di banda aggregata
In questo test è necessario eseguire letture sequenziali in tutti i volumi di dati disponibili
simultaneamente sugli stessi file utilizzati nel passaggio 2. È necessario eseguire SQLIO
utilizzando due thread per ogni file di test, con una dimensione di I/O di 512 KB e un numero
ottimale di operazioni di I/O in attesa determinato dal test precedente.
I risultati di questo test illustrano la velocità effettiva aggregata massima ottenibile durante la
lettura di dati dai dischi fisici.
I dati vengono letti dal file di dati di grandi dimensioni, come nel test precedente, in ogni volume
simultaneamente.
Le prestazioni aggregate del disco devono essere all'incirca comprese tra l'80% e il 90% della
larghezza di banda di I/O di archiviazione aggregata per i sistemi FTDW bilanciati.
46
Valutazioni dei componenti
Nel diagramma seguente vengono illustrati i risultati di benchmark sintetici, che sono coerenti
con i valori osservati in architetture di riferimento Fast Track analoghe.
Figura 4. Esempio di benchmark sintetico sulla larghezza di banda ottenuta per un server con
2 socket e 12 core, 3 schede HBA a due porte da 8 Gbps e 12 volumi di dati primari RAID1+0
con 4 dischi
Riepilogo
Il benchmarking di base dell'hardware consente di convalidare la larghezza di banda effettiva
per i componenti hardware principali dello stack del database. Questa operazione viene
effettuata con una serie di test sintetici del miglior caso possibile eseguiti tramite uno strumento
come SQLIO.
47
Esecuzione del benchmark del database Fast Track
Questa fase della valutazione dell'architettura di riferimento FTDW consente di misurare le
prestazioni di SQL Server per il carico di lavoro FTDW in base a due metriche principali.
La prima, relativa alla frequenza di utilizzo massimo della CPU (MCR), è una misura del valore
massimo della velocità effettiva di elaborazione di I/O. La seconda, relativa alla frequenza di
utilizzo del benchmark (BCR), è una misura della reale velocità effettiva di elaborazione di I/O
per un carico di lavoro di query o basato su query.
Che cos'è la metrica MCR?
Il calcolo della metrica MCR restituisce un valore di velocità effettiva di I/O per ogni core,
espresso in MB o GB al secondo. Questo valore viene misurato mediante l'esecuzione di una
query predefinita, di sola lettura e non ottimizzata, dalla cache del buffer e la misurazione del
tempo di esecuzione rispetto alla quantità di dati in MB o GB. Poiché il calcolo della metrica
MCR viene eseguito dalla cache, rappresenta la velocità di analisi massima non ottimizzata
ottenibile tramite SQL Server per il sistema valutato. Per questo motivo, la metrica MCR
restituisce una frequenza di base massima a fini di progettazione iniziale. Il suo scopo non
consiste nell'indicare risultati medi o previsti per un carico di lavoro reale. Le architetture FTDW
convalidate avranno risultati relativi alla velocità effettiva di I/O di base aggregata almeno pari
al 100% della metrica MCR valutata per il server. Un altro modo per descrivere questo aspetto
è che la metrica MCR rappresenta la velocità di elaborazione ottimale di SQL Server per un
carico di lavoro ragionevole nel peggiore dei casi.
È possibile utilizzare la metrica MCR anche come struttura di riferimento per il confronto di altre
architetture di riferimento FTDW pubblicate e convalidate per SQL Server 2012.
In sintesi:
48
La metrica MCR non definisce gli effettivi risultati per il carico di lavoro di un cliente.
La metrica MCR offre un valore di riferimento per la velocità di elaborazione dati
massima per SQL Server e una singola query associata al carico di lavoro Fast Track.
La metrica MCR è specifica di una CPU e di un server. Benché in generale le frequenze
per una CPU specifica non presentino variazioni notevoli a seconda dell'architettura del
server e della scheda madre, la metrica MCR finale deve essere determinata dalle
effettive attività di testing.
La valutazione della velocità effettiva MCR può essere utilizzata come valore
di confronto rispetto ad architetture di riferimento FTDW pubblicate esistenti.
Tale valore può semplificare la scelta dell'hardware prima del testing dei componenti
e dell'applicazione.
Calcolo della metrica MCR
Una frequenza di utilizzo della CPU di base per l'applicazione SQL Server viene stabilita
eseguendo una query SQL standard definita per il programma FTDW. Questa query
è progettata come rappresentazione relativamente semplice di una tipica query per il tipo
di carico di lavoro (in questo caso del data warehouse) e viene eseguita dalla cache del buffer.
Il valore risultante è specifico della CPU e del server su cui viene eseguita la query. Utilizzare il
metodo seguente per calcolare la metrica MCR:
1. Creare un set di dati di riferimento basato sulla tabella TPC-H lineitem o su un set di
dati simile. La dimensione della tabella deve essere tale da poter essere interamente
memorizzata nella cache del pool di buffer di SQL Server e tuttavia mantenere un tempo
di esecuzione minimo di un secondo per la query fornita di seguito.
2. Per FTDW viene utilizzata la query seguente: SELECT sum([integer field]) FROM [table]
WHERE [restrict to appropriate data volume] GROUP BY [col].
3. L'ambiente deve:
o Garantire che le impostazioni di Resource Governor siano configurate sui valori
predefiniti.
o Garantire che la query venga eseguita dalla cache del buffer. La prima
esecuzione della query deve comportare l'inserimento delle pagine nel buffer
e le esecuzioni successive devono consentire la lettura completa dal buffer.
Verificare che non vi siano letture fisiche nelle statistiche della query restituite.
o Impostare STATISTICS IO e STATISTICS TIME su ON per restituire i risultati.
4. Eseguire la query più volte, con MAXDOP = 4.
5. Registrare il numero di letture logiche e il tempo della CPU dalle statistiche restituite per
ogni esecuzione della query.
6. Calcolare la metrica MCR in MB/s utilizzando la formula seguente:
( [Letture logiche] / [tempo CPU in secondi] ) * 8 KB / 1024
7. È necessario che venga visualizzato un intervallo di valori coerente (+/-5%) in un minimo
di cinque esecuzioni della query. Outlier significativi (+/-20% o oltre) possono indicare
problemi di configurazione. La media di almeno 5 risultati calcolati corrisponde alla
metrica MCR per FTDW.
In base al calcolo della metrica MCR, è possibile creare un diagramma della velocità effettiva
dell'architettura dei componenti. Ai fini della valutazione della metrica MCR del sistema,
la velocità effettiva dei componenti è basata sulla larghezza di banda valutata dal fornitore.
Questo diagramma può essere utile per le attività di progettazione e selezione e per l'analisi dei
colli di bottiglia del sistema. Nella figura 5 viene illustrato un esempio di questo diagramma.
49
Figura 5. Esempio di frequenza massima di utilizzo della CPU (MCR) e larghezza di banda dei
componenti valutata per un server con 2 socket e 12 core basato su CPU Intel Westmere
Per ulteriori informazioni sulla misurazione della metrica MCR, vedere Testing del carico di
lavoro nell'appendice.
Calcolo della metrica BCR
La frequenza di utilizzo della CPU del benchmark per l'applicazione SQL Server viene stabilita
eseguendo un set di query SQL di base, con un livello di concorrenza appropriato, che siano
specifiche del carico di lavoro di data warehouse. Il numero di query e il livello di concorrenza
utilizzati dipende interamente dal caso di utilizzo previsto. Il carico di lavoro di query deve
essere gestito dal disco e non dal pool di buffer di SQL Server come per la metrica MCR.
Il valore risultante è specifico della CPU, del server e del carico di lavoro su cui viene eseguito
il calcolo. La sezione dell'appendice Testing del carico di lavoro fornisce un esempio più
dettagliato della creazione di un benchmark del carico di lavoro BCR.
50
Il metodo illustrato di seguito consente di calcolare la metrica BCR:
1. Creare un set di dati di riferimento contenente almeno una tabella. La tabella deve
essere di dimensioni sostanzialmente sufficienti per impedire la memorizzazione
completa nella cache del pool di buffer di SQL Server o nella cache dell'array SAN.
In assenza di dati del cliente, è possibile utilizzare un set di dati sintetico. È importante
provare a simulare le caratteristiche previste dei dati per il caso di utilizzo di
destinazione.
2. Il formato di base della query per FTDW è il seguente: SELECT sum([integer field])
FROM [table] WHERE [restrict to appropriate data volume] GROUP BY [col]. È possibile
utilizzare questa query come punto di partenza per la progettazione del carico di lavoro
di query se le query del cliente non sono immediatamente disponibili. TPC-H è un altro
benchmark di query di uso comune che può essere utilizzato come set di query di
riferimento.
3. Per un benchmark del cliente per FTDW è sempre preferibile scegliere query
rappresentative del carico di lavoro designato. Le query devono essere pianificate in
più sessioni simultanee che siano rappresentative dell'attività cronologica o prevista
massima per l'ambiente del cliente. Per la selezione della query è possibile considerare
i criteri seguenti:
Rappresentare i requisiti medi del carico di lavoro designato. Ciò può implicare
l'aumento o la riduzione della complessità del formato di base della query,
l'aggiunta di join e/o la rimozione di più o meno dati tramite proiezione e
restrizione.
La query non deve provocare la scrittura di dati in tempdb, a meno che questa
caratteristica non sia una parte essenziale del carico di lavoro di destinazione.
La query deve restituire un numero minimo di righe. È possibile utilizzare
l'opzione SET ROWCOUNT per gestire questo aspetto. È consigliabile utilizzare
un valore di ROWCOUNT maggiore di 100 (105 è il valore standard per il
benchmarking di Fast Track). In alternativa, è possibile utilizzare l'aggregazione
per ridurre i record restituiti da analisi di grandi dimensioni senza restrizioni.
4. L'ambiente deve:
Garantire che le impostazioni di Resource Governor corrispondano ai valori
predefiniti.
Garantire che le cache vengano cancellate prima dell'esecuzione della query,
utilizzando DBCC dropcleanbuffers.
Impostare STATISTICS IO e STATISTICS TIME su ON per restituire i risultati.
5. Eseguire la query o il carico di lavoro più volte, iniziando con MAXDOP impostato su 8.
Ogni volta che si esegue la query, aumentare l'impostazione di MAXDOP per la query,
cancellando le cache dopo ogni esecuzione.
Registrare il numero di letture logiche e il tempo della CPU dall'output delle
statistiche.
Calcolare la metrica BCR in MB/s utilizzando la formula seguente:
( [Letture logiche] / [tempo CPU in secondi] ) * 8 KB / 1024
Questa formula restituisce un intervallo per la metrica BCR. Se si utilizzano più
query, utilizzare una media ponderata per determinare la metrica BCR.
51
Risultati di BCR
Nella figura 6 vengono illustrati i risultati del benchmark basato sul carico di lavoro di SQL
Server, che sono coerenti con i valori osservati in architetture di riferimento Fast Track Data
Warehouse simili.
Figura 6. Esempio di benchmark sintetico sulla larghezza di banda ottenuta per un server con
2 socket e 12 core, 3 schede HBA a due porte da 8 Gbps e 12 LUN di dati primari RAID1+0 con
4 dischi
52
Interpretazione della metrica BCR
Se la metrica BCR per la query media è molto più bassa rispetto alla metrica MCR standard
valutata per l'architettura di riferimento FTDW, è probabile che il risultato dipenda dalla CPU.
Per rimediare al problema, è possibile provare a ridurre la velocità effettiva di archiviazione,
ad esempio diminuendo il numero di array, introducendo più dischi per ogni array o aumentando
le dimensioni dei dischi. Questi interventi possono ridurre i costi dell'infrastruttura di archiviazione
a un livello bilanciato. In alternativa, è possibile valutare l'utilizzo di un server con un numero
maggiore di socket o CPU dalle prestazioni più elevate, che possono sfruttare l'eccesso di
velocità effettiva di I/O di archiviazione. In entrambi i casi l'obiettivo consiste nel bilanciare la
funzionalità di elaborazione del database con la velocità effettiva di I/O di archiviazione.
Analogamente, se la metrica BCR è maggiore della metrica MCR, potrebbe essere necessaria
una velocità effettiva di I/O maggiore per elaborare un carico di lavoro di query in modo bilanciato.
Architetture di riferimento FTDW pubblicate
Le specifiche dettagliate dell'architettura di riferimento hardware vengono rese disponibili
da ogni partner Fast Track Data Warehouse partecipante. Per ulteriori informazioni, inclusi
i collegamenti a ogni partner, vedere Fast Track Data Warehousing (http://www.microsoft.com/
sqlserver/en/us/solutions-technologies/data-warehousing/fast-track.aspx).
È possibile calcolare la capacità di FTDW valutata in base alla quantità stimata di file di dati
utente non compressi che è possibile caricare nel database. Questa capacità è denominata
capacità di dati utente (UDC, User Data Capacity). Questo calcolo presuppone che la
compressione di pagina sia abilitata per tutte le tabelle e che verrà eseguito il mirroring dei
volumi di dati. Viene utilizzato un fattore di compressione media pari a 3,5:1. Viene inoltre
eseguita un'allocazione fino al 30% della capacità non compressa a tempdb prima del calcolo
della metrica UDC. Si noti che per configurazioni di dimensioni più elevate con capacità totale
maggiore, questo rapporto viene ridotto fino al 20%.
Per ulteriori informazioni sul ridimensionamento di tempdb, vedere Pianificazione delle capacità
per tempdb (http://msdn.microsoft.com/it-it/library/ms345368.aspx).
Conclusioni
SQL Server Fast Track Data Warehouse offre un modello e una serie di strumenti per agevolare
il passaggio dalla fase di progettazione del data warehouse alla sua distribuzione. In questo
documento vengono descritte la metodologia, le opzioni di configurazione, le procedure
consigliate, le configurazioni di riferimento e le tecniche di benchmarking e convalida per Fast
Track Data Warehouse.
53
Per ulteriori informazioni
Sito Web SQL Server
Sito Web SQL Server Fast Track
TechCenter di SQL Server
Risorse online per gli sviluppatori di SQL Server
Prime dieci procedure consigliate per la creazione di data warehouse relazionali su larga scala
(team SQLCAT)
Abilitazione dell'opzione Blocco di pagine in memoria (Windows)
Opzioni di ottimizzazione per SQL Server durante l'esecuzione di carichi di lavoro ad alte
prestazioni
Configurazione di SQL Server per l'utilizzo di Soft-NUMA (SQL Server)
Inizializzazione di file di database
Visualizzazione o modifica del modello di recupero di un database (SQL Server)
Monitoraggio dell'utilizzo della memoria
Risoluzione dei problemi di Storage Area Network (SAN)
Installazione e configurazione di MPIO
White paper delle nozioni di base sul sistema di I/O di SQL Server 2000
Compressione dei dati: strategia, pianificazione della capacità e procedure consigliate
Il documento è risultato utile? Esprimere una valutazione utilizzando una scala compresa tra 1
(scarso) e 5 (eccellente), indicandone i motivi. Ad esempio:
La valutazione è alta perché il documento contiene esempi validi, catture di schermate
nitide, spiegazioni chiare o altri motivi?
La valutazione è bassa perché il documento contiene esempi confusi, catture di
schermate sfocate, spiegazioni poco chiare?
I commenti aiuteranno Microsoft a migliorare la qualità dei white paper pubblicati.
Commenti e suggerimenti.
54
Appendice
Strumento di ridimensionamento del sistema FTDW
Lo strumento di ridimensionamento del sistema FTDW è basato su un foglio di calcolo che
semplifica l'elaborazione dei requisiti del carico di lavoro di un cliente in termini di velocità
effettiva del sistema FTDW. È possibile utilizzare questo strumento in assenza di una
piattaforma di test o come punto di partenza per valutare i requisiti dei clienti. Lo strumento
è disponibile nella pagina Fast Track Data Warehousing (http://www.microsoft.com/sqlserver/
en/us/solutions-technologies/data-warehousing/fast-track.aspx). Alcuni fornitori partner, inoltre,
hanno creato propri strumenti di ridimensionamento di Fast Track conformi. Tali strumenti sono
disponibili nei siti Web dei partner.
Convalida di un'architettura di riferimento FTDW definita dall'utente
Testing sintetico del sistema di I/O
SQLIO è uno strumento Microsoft disponibile per il download che consente di testare il
sottosistema di I/O indipendentemente da SQL Server.
Generazione di file di test con SQLIO
Quando si esegue SQLIO, viene creato un file di test appropriato, se non è già presente. Per
generare un file di una dimensione specifica, utilizzare il parametro -F. Utilizzare, ad esempio,
un file di parametri (param.txt) con il contenuto seguente:
C:\stor\pri\1\iobw.tst 1 0x0 50
L'esecuzione di SQLIO con il parametro -F genera un file di 50 MB alla prima esecuzione:
Eq sqlio -kW -s60 -fsequential -o1 -b64 -LS -Fparam.txt
Questo processo può richiedere una certa quantità di tempo per file di grandi dimensioni.
Creare un file in ogni disco dati in cui verranno ospitati dati di SQL Server e file di tempdb.
Questa operazione può essere eseguita aggiungendo più righe al file di parametri, per creare
i file necessari uno per uno. Per creare i file in parallelo, creare più file di parametri e quindi
eseguire più sessioni di SQLIO simultaneamente.
Convalidare la larghezza di banda di archiviazione (dalla cache)
L'utilizzo di un piccolo file di test con una durata di lettura di diversi minuti garantisce che il file
sia contenuto interamente nella cache dell'array. Nella figura 7 viene illustrato il contatore
Logical Disk > Read Bytes / sec sui dischi in un sistema Fast Track di esempio con diverse
quantità di richieste in attesa e dimensioni dei blocchi. I test devono essere eseguiti per almeno
alcuni minuti in modo da garantire prestazioni costanti. Nella figura viene illustrato che per
ottenere prestazioni ottimali è necessaria una coda di richieste in attesa di almeno quattro
richieste per ogni file. Ogni singolo disco deve contribuire alla larghezza di banda totale.
55
Figura 7. Contatore Logical Disk > Read Bytes / sec
Convalidare la larghezza di banda di LUN/volumi (dal disco)
Questi test garantiscono che tutti i volumi di disco presentati dagli array di dischi a Windows
sono capaci di contribuire alla larghezza di banda aggregata complessiva tramite la lettura di
ogni volume, uno per volta. Si potrebbe riscontrare che alcuni dei LUN sono leggermente più
veloci di altri. Benché questo comportamento non sia insolito, è consigliabile esaminare le
differenze maggiori del 15%.
Figura 8. Convalida della larghezza di banda delle coppie di LUN\volumi e RAID
56
Eseguire test simultanei su uno o più volumi che condividono lo stesso gruppo di dischi.
Nell'immagine seguente viene illustrato l'output dei test su otto gruppi di dischi.
Figura 9. Testing di LUN che condividono gruppi di dischi
Convalidare la larghezza di banda aggregata (dal disco)
Tramite il test seguente viene illustrato l'effetto dell'aumento della velocità effettiva di I/O,
tramite l'aggiunta di un ulteriore volume nel test a intervalli regolari. Con l'esecuzione di ogni
test per un intervallo impostato, è possibile osservare una riduzione. Il modello osservato
dovrebbe essere simile. La larghezza di banda aggregata massima del disco dovrebbe
avvicinarsi all'80%-90% della larghezza di banda indicata dalla cache nel primo passaggio.
Nel grafico viene illustrato il test in corrispondenza di più dimensioni di blocchi di 512 KB e 64 KB.
Figura 10. Larghezza di banda aggregata con più dimensioni di blocchi
57
Testing del carico di lavoro
Misurazione della metrica MCR per il server (facoltativo)
L'obiettivo della metrica MCR consiste nello stimare la velocità effettiva massima di un singolo
core della CPU eseguendo SQL Server e in assenza di colli di bottiglia di I/O. La metrica MCR
viene valutata per ogni core. Se si sceglie di calcolare questa metrica per il proprio server, di
seguito sono disponibili ulteriori dettagli in cui viene descritta la metodologia per tale calcolo:
1. Creare un set di dati di riferimento basato sulla tabella TPC-H lineitem o su un set di dati
simile. La dimensione della tabella deve essere tale da poter essere interamente
memorizzata nella cache del pool di buffer di SQL Server e tuttavia mantenere un tempo
di esecuzione minimo di due secondi per la query fornita qui.
2. Per FTDW viene utilizzata la query seguente: SELECT sum([integer field]) FROM [table]
WHERE [restrict to appropriate data volume] GROUP BY [col].
3. L'ambiente deve:
Garantire che Resource Governor sia impostato sui valori predefiniti.
Garantire che la query venga eseguita dalla cache del buffer. La prima
esecuzione della query deve comportare l'inserimento delle pagine nel buffer
e le esecuzioni successive devono consentire la lettura completa dal buffer.
Verificare che non vi siano letture fisiche nelle statistiche della query restituite.
Impostare STATISTICS IO e STATISTICS TIME su ON per restituire i risultati.
4. Eseguire la query più volte, con MAXDOP = 4.
Registrare il numero di letture logiche e il tempo della CPU dalle statistiche
restituite per ogni esecuzione della query.
Calcolare la metrica MCR in MB/s utilizzando la formula seguente:
( [Letture logiche] / [tempo CPU in secondi] ) * 8 KB / 1024
È necessario che venga visualizzato un intervallo di valori coerente (+/-5%) in un
minimo di cinque esecuzioni della query. Outlier significativi (+/-20% o oltre)
possono indicare problemi di configurazione. La media di almeno 5 risultati
calcolati corrisponde alla metrica MCR per FTDW.
Misurazione della metrica BCR per il carico di lavoro
La misurazione della metrica BCR è simile a quella della metrica MCR, con la differenza che
i dati vengono gestiti dal disco e non dalla cache. La query e il set di dati per la metrica BCR
sono rappresentativi del carico di lavoro del data warehouse di destinazione.
Un approccio per la metrica BCR consiste nell'utilizzare una query semplice, una query media
e una query complessa dal carico di lavoro. Le query complesse devono essere quelle che
richiedono l'impegno maggiore della CPU. La query semplice deve essere analoga alla metrica
MCR ed effettuare una quantità simile di lavoro, in modo che possa essere confrontabile con
tale metrica.
58
Creazione del database
Di seguito è riportato un esempio di un'istruzione CREATE DATABASE per un sistema FTDW
con 8 core e 16 LUN di dati.
CREATE DATABASE FT_Demo ON
( NAME = N 'FT_Demo_.mdf' ,
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v1.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v2.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v3.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v4.ndf'
FILEGROWTH = 0 ),
PRIMARY Filegroup FT_Demo
FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 100MB ,
, FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP1-DG2-v2' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP2-DG3-v3' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP2-DG4-v4' , SIZE = 417GB ,
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
'FT_Demo_v6.ndf'
= 0 ),
'FT_Demo_v7.ndf'
= 0 ),
'FT_Demo_v8.ndf'
= 0 ),
'FT_Demo_v9.ndf'
= 0 ),
, FILENAME = N'C:\FT\PRI\SE2-SP1-DG6-v6' , SIZE = 417GB ,
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
'FT_Demo_v11.ndf'
= 0 ),
'FT_Demo_v12.ndf'
= 0 ),
'FT_Demo_v13.ndf'
= 0 ),
'FT_Demo_v14.ndf'
= 0 ),
, FILENAME = N'C:\FT\PRI\SE3-SP1-DG11-v11' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP1-DG7-v7' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP2-DG8-v8' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP2-DG9-v9' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE3-SP1-DG12-v12' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE3-SP2-DG13-v13' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE3-SP2-DG14-v14' , SIZE = 417GB ,
LOG ON
( NAME = N 'FT_LOG_v5.ldf' , FILENAME = N 'C:\FT\LOG\SE1-SP2-DG5-v5' , SIZE = 100GB ,
MAXSIZE = 500GB , FILEGROWTH = 50 )
GO
/*****************Configurare le impostazioni consigliate***********************/
ALTER DATABASE FT_Demo SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE
GO
FT_Demo
SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE
GO
FT_Demo
SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE
GO
FT_Demo
SET RECOVERY SIMPLE
sp_configure 'show advanced options', 1
go
reconfigure with override
go
/********Assicurarsi che tutte le tabelle vengano inserite nel filegroup appropriato e
non nel filegroup primario****/
59
ALTER DATABASE FT_Demo
MODIFY FILEGROUP FT_Demo
DEFAULT
GO
Creazione delle tabelle di test
Di seguito viene riportata un'istruzione CREATE TABLE di esempio.
CREATE TABLE lineitem
( l_orderkey
bigint not null,
l_partkey
integer not null,
l_suppkey
integer not null,
l_linenumber
integer not null,
l_quantity
float not null,
l_extendedprice float not null,
l_discount
float not null,
l_tax
float not null,
l_returnflag
char(1) not null,
l_linestatus
char(1) not null,
l_shipdate
datetime not null,
l_commitdate
datetime not null,
l_receiptdate
datetime not null,
l_shipinstruct char(25) not null,
l_shipmode
char(10) not null,
l_comment
varchar(132) not null
)
ON FT_Demo
GO
CREATE CLUSTERED INDEX cidx_lineitem
ON lineitem(l_shipdate ASC)
WITH( SORT_IN_TEMPDB = ON
, DATA_COMPRESSION = PAGE
)
ON FT_Demo
GO
Caricamento di dati per la misurazione della metrica BCR
Come descritto in precedenza in questo documento, i sistemi Fast Track Data Warehouse sono
esposti alla frammentazione dei file di database. Utilizzare una delle tecniche descritte in questo
documento per caricare i dati. Durante il testing di FTDW, è stato utilizzato il metodo di
caricamento di indici cluster descritto come opzione 2. Tramite lo strumento TPC-H datagen,
sono stati generati i dati della tabella lineitem con una dimensione di 70 GB, utilizzando le
opzioni -s100, generando il file in 8 parti e utilizzando le opzioni -S e -C.
Durante tutte le operazioni di caricamento è stato impostato il flag di traccia 610 per utilizzare la
registrazione minima laddove possibile.
60
Utilizzando BULK INSERT, tali dati sono stati inseriti in parallelo in una singola tabella di
gestione temporanea con indice cluster, utilizzando la registrazione minima. È stata scelta
una dimensione dei blocchi non eccessiva per la memoria disponibile e che avrebbe ridotto
la scrittura sul disco. Disabilitando i blocchi di pagina e l'escalation dei blocchi nella tabella
di gestione temporanea, è stato possibile migliorare le prestazioni durante questa fase.
È stato eseguito un inserimento finale in una tabella di destinazione identica, con MAXDOP
impostato su 1 (utilizzando l'hint TABLOCK) ed evitando un ordinamento.
Esecuzione di query per la misurazione della metrica BCR
Utilizzare lo strumento SQL Server Profiler per registrare le informazioni pertinenti per
i benchmark di query. SQL Server Profiler deve essere configurato per la registrazione di letture
logiche, CPU, durata, nome del database, nome dello schema, istruzione SQL ed effettivi piani
di query. In alternativa, è possibile utilizzare i parametri di sessione delle statistiche set
statistics io on e set statistics time on.
Di seguito sono riportate alcune query di esempio (basate su query dal benchmark TPC-H) e la
metrica BCR ottenuta nei sistemi di riferimento. Si noti che questo esempio non è indicativo
delle prestazioni che verranno ottenute in qualsiasi sistema specificato. I numeri relativi alla
metrica BCR sono univoci di variabili quali, tra le altre, sistema, dimensione dello schema, tipi di
dati, struttura di query e statistiche.
Complessità delle query
Semplice
Media
Complessa
Tabella 7. Esempi di benchmark
BCR per ogni core
(compressione di pagina) con MAXDOP
4
201 MB/s
83 MB/s
56 MB/s
Semplice
SELECT
sum(l_extendedprice * l_discount) as revenue
FROM
lineitem
WHERE
l_discount between 0.04 - 0.01 and 0.04 + 0.01 and
l_quantity < 25
OPTION (maxdop 4)
61
Media
SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count_big(*) as count_order
FROM
lineitem
WHERE
l_shipdate <= dateadd(dd, -90, '1998-12-01')
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus
OPTION (maxdop 4)
Complessa
SELECT
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice*(1-l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue
FROM
lineitem,
part
WHERE
l_partkey = p_partkey
and l_shipdate >= '1995-09-01'
and l_shipdate < dateadd(mm, 1, '1995-09-01')
OPTION (maxdop 4)
62
Fattori che influiscono sulla frequenza di utilizzo delle query
Non tutte le query otterranno la frequenza massima di utilizzo della CPU (MCR) o la frequenza
massima di utilizzo del benchmark (BCR). Molti fattori influiscono sulla frequenza di utilizzo per
una query. Query più semplici del carico di lavoro utilizzato per generare la frequenza di utilizzo
avranno frequenze di utilizzo maggiori, mentre carichi di lavoro complessi avranno frequenze di
utilizzo minori. Molti fattori possono influire su questa complessità e sulla frequenza di utilizzo,
ad esempio:
63
Complessità della query: maggiore è l'utilizzo della CPU da parte della query, ad
esempio in termini di calcoli e numero di aggregazioni, minore sarà la frequenza di
utilizzo.
Complessità dell'ordinamento: gli ordinamenti prodotti da operazioni di ordinamento
o raggruppamento esplicite generano un carico di lavoro della CPU maggiore e riducono
la frequenza di utilizzo. Scritture aggiuntive in tempdb provocate da tali query che
sovraccaricano il disco influiscono negativamente sulla frequenza di utilizzo.
Complessità del piano di query: più è complesso un piano di query e maggiore è il
numero di passaggi e operatori, minore sarà la frequenza di utilizzo della CPU, perché
ogni unità di dati viene elaborata tramite una pipeline di operazioni più lunga.
Compressione: la compressione riduce la frequenza di utilizzo dei dati in termini reali,
perché per definizione la frequenza di utilizzo viene misurata per le query che dipendono
dalla CPU e la decompressione utilizza i cicli della CPU. In genere, tuttavia, i vantaggi
dell'aumento della velocità effettiva superano l'overhead aggiuntivo della CPU prodotto
dalla compressione, a meno che il carico di lavoro non utilizzi la CPU in maniera
eccessiva. Nel confrontare le frequenze di utilizzo per dati compressi e non compressi,
considerare il fattore di compressione. Un altro modo di esaminare questo aspetto
consiste nel considerare la frequenza di utilizzo in termini di righe al secondo.
Utilizzo dei dati: la rimozione dei dati durante le analisi, ad esempio tramite la
proiezione e la selezione di query, è un processo piuttosto efficiente. Le query che
utilizzano tutti i dati in una tabella hanno frequenze di utilizzo inferiori, perché viene
elaborata una quantità maggiore di dati in base alla velocità effettiva dei dati dell'unità.
