presenta BI 008 SQL Server 2016 Evoluzione dei

presenta BI 008 - SQL Server 2016: Evoluzione dei Columnstore Indexes e maturazione dell'In-Memory OLTP Gilberto Zampatti www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 - #wpc 15 it

Speaker info IT pro da …un bel mucchietto di anni ; ) MCT: MCT un pò meno di metà del tempo in aule di varia misura e capienza Mentore/Consulente: Mentore/Consulente mi si contatta quando le cose vanno …ma si vuol che vadano meglio Speaker: Speaker tra i fondatori di UGISS, qualche conferenza ogni anno mi tiene in salute

Agenda • Requisiti • Premesse (2012) Columnstore 2012 Limitazioni • Premesse (2014) Columnstore 2014 Limitazioni • Terzo passo …(2016) • Hekaton (2014) • Memory-optimized tables Indexes • Transaction Log streams • Checkpoint streams • Natively compiled Objects • What’s NEW? www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 3

Requisiti e soluzioni • A fronte del bisogno di queries molto efficienti in un DW su data set molto (MOLTO) voluminosi: In SQL Server 2008 e SQL Server 2008 R 2 OLAP (SSAS) MDX ROLAP e T-SQL + tabelle di aggregazione intermedie, indexed views e tabelle di aggregati Flessibilità insufficiente In SQL Server 2012 Creazione di columnstore index su tabelle dei fatti molto grandi, che referenzino tutte le colonne (purché supportino data type coerenti) Utilizzo di T-SQL e delle funzionalità di base del Database Engine Refactoring e/o interventi minimi Creando il columnstore index, la tabella diventa “read only” – ma si può adottare il partitioning per effettuare switch bi-direzionale dei dati, OPPURE drop/rebuild periodico degli indici www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 4

Premessa (SQL 2012) • Gli indici Columnstore offrono un metodo relativamente semplice per migliorare significativamente l’utilizzo dei datawarehouse in termini prestazionali (soprattutto su dataset VERAMENTE grandi) • I miglioramenti oscillano tra 10 x e 100 x • I migliori risultati si ottengono per queries effettuate su modelli starschema che applicano filtri, aggregazioni e raggruppamenti www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 5

Column vs. Row Store • Row Store (Heap / B-Tree) data page 1000 Product. ID Order. Date Cost 310 20010701 2171. 29 311 20010701 1912. 15 312 20010702 2171. 29 313 20010702 413. 14 Column Store (valori compressi) Product. ID data page 2000 311 312 Order. Date data page 20010701 … 20010702 Cost data page 2002 2171. 29 1912. 15 2171. 29 313 … 314 … 315 20010703 316 … 317 … 318 … 319 … 320 20010704 64. 32 321 … 1111. 25 413. 14 333. 42 Product. ID data page 1001 314 315 Order. Date 20010701 Cost 333. 42 1295. 00 316 20010702 4233. 14 317 20010702 641. 22 www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 4233. 14 641. 22 24. 95 6

Definizioni • Tipo di Indice Gli Indici Columnstore sono sempre non-clustered e non-unique NON possono essere creati su viste, viste indicizzate, sparse columns NON possono agire come vincoli di primary o foreign key • Selezione delle colonne Non esistono “key columns” Si scelgono le colonne che saranno usate nelle query Fino a 1, 024 colonne – non ha importanza l’ordine in cui sono indicate Concetto di “INCLUDE” non esiste Limite dei 900 byte sulla “chiave” abolito • Ordinamento Non sono consentite le opzioni ASC or DESC – l’ordinamento è definito dagli algoritmi di compressione www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 7

Creazione di un indice columnstore • T-SQL SSMS www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 8

Data Types • Supportati Char / nchar / varchar / nvarchar (max), legacy LOB types e FILESTREAM non supportati Decimal/numeric Precisione superiore ai 18 digits NON è supportata Tinyint, smallint, bigint Float/real Bit Money, smallmoney Date and time Datetimeoffset con scale > 2 NON supportata www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 9

Manutenzione • Creato l’indice, la tabella diventa “read-only” e INSERT/UPDATE/DELETE/MERGE non sono più ammissibili • ALTER INDEX REBUILD / REORGANIZE non sono consentiti • Opzioni supportate: Partition switches (IN and OUT) Drop dell’indice columnstore / modifiche alla tabella / ri-creazione dell’indice columnstore UNION ALL (verificare le prestazioni) www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 10

Limitazioni • Gli Indici Columnstore non sono ammessi in: Change Data Capture e Change Tracking Colonne Filestream (altre colonne sulla tabella sono ammesse) Compressioni Page, row e vardecimal Replication Sparse columns www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 11

Candidati ideali per indicizzazione Columnstore • Tabelle: Tabelle dei fatti MOLTO grandi (centinaia di milioni/miliardi di righe) Tabelle dimensionali (milioni di righe) con elevate densità Nel dubbio è semplice creare un indice columnstore e verificarne l’impatto sulle prestazioni www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 12

Candidati ideali per indicizzazione Columnstore • Query (su tabelle con indice columnstore): Scan (gli indici columnstore non supportano operazioni seek) Risultati di Aggregazione molto più piccoli della tabella Joins su tabelle dimensionali più piccole Filtering su tabelle dei fatti e/o dimensioni –su un modello star schema Sub-set di colonne Joins su singola Colonna tra la tabella con indice columnstore e altre tabelle www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 13

Premessa (SQL 2014) • Gli indici Columnstore offrono un metodo relativamente semplice per migliorare significativamente l’utilizzo dei datawarehouse MA NON SOLO in termini prestazionali (soprattutto su dataset VERAMENTE grandi) • I miglioramenti oscillano tra 10 x e 100 x • I migliori risultati si ottengono per queries effettuate su modelli star-schema che applicano filtri, aggregazioni e raggruppamenti www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 14

Evoluzione • Obiettivi del nuovo columnstore : Prestazioni “competitive” in caricamento e creazione dell’indice più efficiente. Massimizzazione dei fattori di compressione e prestazioni “competitive” delle query. Parità funzionale con il modello row-based. www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 15

Multi-Row Batch – Batch Processing • Motivi: • Column store riduce significativamente il fabbisogno di i/o; • Una volta che l’ i/o è ridotto, l’uso di CPU diventa il principale collo di bottiglia • Il Batch processing riduce l’utilizzo di CPU Batch object bitmap of qualifying rows Column vectors C 1 C 2 C 3 • Funzionalità: • Tra gli iteratori (operatori del piano di esecuzione) si muovono NON le righe bensì insiemi di righe chiamati BATCH; indicativamente circa 1000 righe alla volta. • I Batches sono organizzati in formato colonnare (a vettori) con un vettore aggiuntivo che indica le righe qualificanti. • Ogni batch passa da un iteratore al successivo. • Il numero di function calls per riga elaborate cala di alcuni ordini di grandezza. • Molte operazioni non necessitano della copia dei dati, ma determinano solo lievi variazioni del batch.

Piani di esecuzione L’ottimizzatore opera ora sull’intero set di operatori di join: inner, outer, semi- and anti-semi joins batch-mode hash join con nuova funzionalità di data spilling: Uso temporaneo del disco qualora la tabella non possa essere interamente “contenuta” in memoria www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 17

OPERAZIONI DML Possono avvenire solo su un indice CLUSTERED • INSERT: le righe sono inserite nel deltastore. • DELETE: se la riga è nel columnstore, viene «marchiata» ma non fisicamente eliminata dal media, fino alla rebuild dell’indice; se invece è nel deltastore, viene fisicamente eliminata. • UPDATE: se la riga è nel columnstore, viene marchiata come cancellata e la «nuova» versione è inserita nel deltastore; se invece è nel deltastore viene aggiornata direttamente www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 18

Manutenzione REBUILD • Tramite ALTER INDEX … REBUILD • Tramite CREATE …COLUMNSTORE INDEX …WITH (DROP EXISTING) • Rebuild di singola partizione (!!!) REORGANIZE • Tramite ALTER INDEX …REORGANIZE • Consente di spostare i rowgroups chiusi (CLOSED) dal Delta. Store nel columnstore www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 19

Compressione… COLUMNSTORE_ARCHIVE • sys. partitions www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 20

Terzo passo… SQL Server 2016 • Esecuzione in batch di queries in single thread • Supporto per Snapshot isolation e Read Committed Snapshot Isolation • Definizione di columnstore index durante la creazione di una tabella • Supporto ad indici columnstore UPDATABLE nelle repliche secondary di Always. On • Indici columnstore NON clustered AGGIORNABILI (tanto su heap quanto su b-tree) • Indici b-tree su un indice columnstore clustered • Indice columnstore NON clustered FILTRATO www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 21

Terzo passo… SQL Server 2016 • Di conseguenza: “REAL TIME OPERATIONAL ANALYTICS” La possibilità di creare indici columnstore updatable su una tabella OLTP Diventa possibile (e ragionevole) accedere alla tabella tanto per attività transazionali (OLTP) quanto per analisi dati… in tempo reale • Ma anche per i nostri datawarehouse… La presenza contemporanea di indici non clustered “tradizionali” insieme al columnstore permette accessi efficienti anche in seek (accessi puntuali o su piccoli intervalli di righe) www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 22

Terzo passo… SQL Server 2016 • Manutenzione: REBUILD: rimuove eventuali frammentazioni e porta tutte le righe nel columnstore; per ora è un’operazione offline, quindi la tabella o la partizione in rebuild non è disponibile per la durata del processo. REORGANIZE: deframmenta il columnstore portando le righe dei deltastores chiusi nel columnstore e elimina le righe DELETED. www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 23

Hekaton Cosa è Hekaton = In-Memory Optimized OLTP La risposta all’esigenza di prestazioni sempre maggiori e dati sempre accessibili/disponibili In greco Hekaton significa 100 x di miglioramento delle prestazioni. . . Hekaton è un nuovo engine Nativo in SQL Server 2014. . . Poche modifiche ai DB esistenti (con le dovute cautele. . ) Miglioramenti nelle prestazioni senza modifiche Sw e Hw www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 24

Hekaton Cosa NON è Hekaton NON è l’ecquivalente dell’istruzione DBCC PINTABLE Comunque DBCC PINTABLE era mantenuta per retrocompatibilità ma non funziona. . . Hekaton NON è Buffer Pool Extension Hekaton NON è la panacea di tutti i mali dei DB Hekaton non è Mongo. Db. . . www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 25

SQL Server engine (con Hekaton)…oggi Client Application TDS handler and Session Management Natively compiled SPs and Schema In-memory OLTP Compiler Tables… Parser, Catalog, Optimizer Query Interop Indexes… Memory Optimized Tables & Indexes Interpreted T-SQL Query execution Tables… Indexes… Sql Server. exe Buffer Pool for tables & Indexes

Memory-optimized Tables Storage • Sono interamente in memoria • Non richiedono I/O sul disco per reperire i dati Non è del tutto vero ma per ora va bene così • Non usano Datapages ed Extent Non usano nessuna delle strutture a cui siamo abituati Durability • Schema & Data =>Dati non volatili => User Tables • Schema Only => Dati Volatili => Staging Tables

Memory-optimized Tables Una In-Memory Table è composta da : • Rows Nuova struttura ottimizzata per la RAM Multiversioning (no lock, no blocking, no latch) • Index NO B-TREE L’indice punta la riga, non la duplica 2 tipi di indici Hash Range Index

Memory-optimized Tables • Struttura della riga: • Header = descrittore iniziale del record • Payload = Record

Memory-optimized Tables • Begin Ts = Timestamp Inizio validità riga (8 Bytes) • End Ts = Fine validità riga (8 Bytes). Se la riga è in corso di validità assume il nuovo valore «Infinity» • Stmt. Id (4 Bytes) Id dello statement che ha modificato la riga • Idx. Link. Count (2 Bytes) Numero di indici • Idx. Pointer (8 bytes * n Indici) Puntatore a ciascun indice che referenzia la riga

Memory-optimized Tables Limitazioni (2014) • Non tutti i tipi di dato sono supportati Xml Clr Type(MAX) i. e. Varchar(max) Lunghezza massima di una riga 8060 Bytes • No Foreign Key • No Check • No DML Trigger • No Unique Index oltre alla Primary key • No Identity • No ALTER post creazione !!!!

Hash Index Quando inseriamo una riga • Una Funzione di Hash è applicata a tutte le colonne chiave (key column) della riga e determina a quale bucket la riga sarà associata • Nel caso che più righe restituiscano il medesimo Hash, viene creata una catena di righe (Row Chain) (…tra poco) • Ogni Hash Bucket ha una dimensione di 8 Bytes Non sprecare Ram esagerando con il numero di bucket Non essere troppo restrittivi: le Row Chain possono degradare molto le prestazioni

Hash Index CREATE TABLE Hash_Memory. Table ( id int not null PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 1024) , Campo char(30) not null ) WITH( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ); GO

DEMO Q&A www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 - #wpc 15 it 34

Range Index • Bw-Tree (Buzz World) • Simile ai B-Tree che conosciamo ma più performanti • Utili se non conosciamo la cardinalità dei dati o faremo ricerche su range di valori

Range Index Data Pages a dimensione variabile, al max 8 Kb Ogni Datapage ha un PID • Logical page number Page Mapping Table • Traduzione da PID a physical Address

Range Index

CREATE Table Range_Memory. Table ( id int not null PRIMARY KEY NONCLUSTERED , Campo char(30) not null )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ); GO

Transaction Log Stream • La durabilità dei dati è garantita da Log Streams Sfrutta il T-Log del DB Ogni transazione è registrata nel T-Log Checkpoint Streams Files di checkpoint gestiti tramite Filestream • Il contenuto combinato di Log e Checkpoint Stream è sufficiente a ricostruire le tabelle point in time in caso di necessità

Transaction Log Stream • La scrittura delle transazioni delle In-Memory Tables è ottimizzata al fine da ridurre il più possibile l’I/O • Con un unico record di Log è possibile registrare multiple Insert, Update e Delete • Le operazioni sugli indici non necessitano di Log, vengono ricostruiti in caso di bisogno

Checkpoint Stream • Checkpoint Streams Data Streams = Contiene tutte le righe inserite in un determinato intervallo di tempo Delta Streams = Associato ad un Data Streams contiene la lista delle righe eliminate in un determinato intervallo di tempo

Checkpoint Stream • Data e delta file sono popolati da un thread in background denominato offline checkpoint Memory Optimized Data Filegroup Range 500 - Range 400 -499 Range 300 -399 Range 200 -299 Range 100 -199 Una transazione con un timestamp di 600 aggiunge una riga e ne elimina n inserite da una transazione con commit timestamp di 150, 250 E 420 offline checkpoint Thread

Checkpoint Stream • Con l’andare del tempo il numero di coppie Data e Delta aumenterà • Per mantenere basso il numero di coppie e la quantità di spazio allocata su disco, periodicamente parte un processo di Merge. • Periodicamente coppie di Data e Delta file, il cui contenuto è riconducibile a 128 MB, vengono unificati in un solo Data file. • In casi particolari è possibile invocare il merge dei file con stored procedure sys. sp_xtp_merge_checkpoint_files

Range 400 -499 Range 300 -399 Range 200 -299 Range 100 -199 Memory Optimized Data Filegroup Merge 200 -399 Range 500 -599 Range 400 -499 Range 200 -399 Range 300 -399 Range 200 -299 Range 100 -199 Merge Memory Optimized Data Filegroup

Natively Compiled Objects • Il linguaggio T-SQL è lento per sua natura. . • Cosa c’è di più veloce di un modulo C compilato e caricato in memoria ? !? ! • Possiamo creare SP in T-SQL ma renderle veloci come dei moduli C caricati in memoria !!

Natively Compiled Objects CREATE PROCEDURE Dbo. SP_NATIVE_COMPILATION_MEMORY_TAB @id int not null WITH NATIVE_COMPILATION , SCHEMABINDING , EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english' ) DECLARE@c varchar(25) SELECT @c = A. campo FROM dbo. Memorytable A WHERE A. id = @id UPDATE dbo. Memorytable SET campo = campo + 'AAAA' WHERE id = @id DELETE FROM dbo. Memorytable WHERE id = @id INSERT INTO dbo. Memorytable VALUES(@id, @c) END GO

T-SQL (2014!) • L’accesso alle In-Memory Tables può essere effettuato tanto da codice interpretato (tramite Interop), quanto da natively compiled stored procedures • Interop non supporta: TRUNCATE TABLE MERGE (se il target è una in-memory table) Cursori dinamici e keyset (questi degradano automaticamente a statici) Query cross-database Transazioni cross-database Linked Servers Locking hints: TABLOCK, XLOCK, PAGLOCK, ecc. Isolation level READ UNCOMMITTED, READ COMMITTEDLOCK

Evoluzione MEMORIA SQL 2014 SQL 2016 Si raccomanda un utilizzo massimo di 256 GB Il numero di checkpoint files supportati è di memoria per le memory-optimized table. esteso, e Microsoft ha testato vari scenari Non è un limite fisico ma un suggerimento portando il suggerimento a un utilizzo basato sul numero di checkpoint files massimo di 2 TB necessari

Evoluzione COLLATION SQL 2014 SQL 2016 Tutte le colonne alfanumeriche (char, Le colonne alfanumeriche degli indici varchar, nchar e nvarchar) utilizzate ed i confronti tra valori alfanumerici in indici devono usare una collation nelle NCSP possono far uso di BIN 2; in una Natively compiled sp i qualsiasi collation. Vi sono tuttavia confronti tra valori alfanumerici differenze prestazionali ancora a devono usare una collation BIN 2 favore di BIN 2

Evoluzione MODIFICHE A SCHEMA E DATI SQL 2014 SQL 2016 Non è possibile modificare una memory-optimized table dopo la creazione. In sintesi, non è consentito l’uso dello statement ALTER TABLE può essere utilizzato per aggiungere, modificare o eliminare colonne e per aggiungere, eliminare o ricostruire (REBUILD) indici

Evoluzione PARALLELISMO SQL 2014 SQL 2016 Per operazioni di accesso a memory -optimized tables non viene MAI generato un piano parallelo Viene considerato un piano parallelo per alcune operazioni che utililizzano hash indexes (purché non siano in una NCSP)

Evoluzione TDE (Transparent Data Encryption) SQL 2014 SQL 2016 Un database seppur abilitato all’encryption non sottopone ad encryption dati memorizzati nel filegroup MEMORY_OPTIMIZED_DATA Supporto completo per Transparent Data Encryption: Le memory-optimized tables persistite su disco sono assoggettabili ad encryption

Evoluzione Native Compilation SQL 2014 SQL 2016 L’Articolo Transact-SQL Constructs Not Supported by In-Memory OLTP della SQL Server Documentation elenca un nutrito schieramento di limitazioni e costrutti non supportati dale Native Compiled Stored Procedures Supporto introdotto per: LEFT and RIGHT OUTER JOIN SELECT DISTINCT OR and NOT operators Subqueries in tutte le clausole del SELECT statement • Nested stored procedure calls • UNION and UNION ALL • All built-in math functions • •

Evoluzione …nascosta FILESTREAM SQL 2014 SQL 2016 Il File System del Sistema Operativo supporta la scrittura sui files poggiando sulla tecnologia FILESTREAM; tuttavia questo richiede una tabella (xtp_storage) che contiene una Colonna filestream che il filegroup MEMORY_OPTIMIZED_DATA usa per backup FILESTREAM è utilizzato solo per offrire visibilità al filegroup MEMORY_OPTIMIZED_DATA; la gestione dei files di checkpoint è affidata all’engine chiama direppamente API dell’NTFS; ciò traspare in interfaccia per una diversa struttura del folder del filegroup rispetto a 2014

Evoluzione …nascosta LOG READER SQL 2014 SQL 2016 Per la lettura di transazioni dal log che impattano su memory-optimized tables, viene attivato un singolo thread per database. Ciò avviene tanto durante il recovery quanto in presenza di Always. On. Sono manifesti alcuni problemi di scalabilità Per le attività di recovery e di checkpoint, per lettura ed utilizzo delle transazioni logged sono disponibili più thread, in relazione al numero di cores disponibili

Evoluzione …nascosta Always. On SQL 2014 SQL 2016 La visibilità dei dati di in-memory OLTP sulle repliche secondarie subisce una dilazione di alcune transazioni La latenza tende ad essere annullata: dopo una commit sulla replica primaria un accesso alla secondaria raramente omette le ultime modifiche apportate

Evoluzione …nascosta Garbage Collecxtion SQL 2014 SQL 2016 I thread interni di garbage collection utilizzati possono subire – a fronte di carichi di lavoro particolarmente onerosi – una latenza percepibile nel rilascio della memoria impegnata da oggetti cancellati Miglioramenti agli algoritmi di garbage collection fanno si che – con sufficiente disponibilità di risorse – il garbage delle operazioni DML avvenga in tempo quasi-reale

Per non dimenticarci cose importanti… Connubio tra Columnstore technology e In Memory OLTP: • Possibilità di creare NONCLUSTERED COLUMNSTORE INDEX su una memory-optimized table Attualmente SOLO durante la CREATE… • Possibilità di creare contestualmente indici B-Tree e Columnstore www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 58

DEMO Q&A www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 - #wpc 15 it 59

Domande e Risposte Q&A www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 - #wpc 15 it 60

Fondamentalmente: - Tech. Net (In-Memory OLTP (In-Memory Optimization – https: //technet. microsoft. com/enus/library/dn 133186(v=sql. 130). aspx ) - SQL Server Documentation (SQL 2014 e SQL 2016) Corsi consigliati - https: //msdn. microsoft. com/en-us/library/dn 817827. aspx - https: //msdn. microsoft. com/enus/library/gg 492088(v=sql. 130). aspx - MSDN (In-Memory OLTP (In-Memory Optimization https: //msdn. microsoft. com/enus/library/dn 133186(v=sql. 130). aspx ) - WHITE Papers: - SQL_Server_2014_In-Memory_OLTP_TDM_White_Paper. pdf - SQL_Server_2016_In-Memory_OLTP_White_Paper. pdf www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 - #wpc 15 it 61

Over. Net Education info@overneteducation. it www. overneteducation. it Tel. 02 365738 Contatti Over. Net Education @overnet www. facebook. com/Over. Net. Education www. linkedin. com/company/overnet-solutions www. wpc 2015. it – info@wpc 2015. it - +39 02 365738. 11 - #wpc 15 it 62