back to the roots - sql server indexing
TRANSCRIPT
#sqlsatPordenone
#sqlsat367February 28, 2015
Back to the Roots: SQL Server
Indexing
Davide Mauri
#sqlsatPordenone
#sqlsat367February 28, 2015
Davide Mauri
20 Years of experience on the SQL Server Platform Specialized in Data Solution Architecture, Database
Design, Performance Tuning, Business Intelligence
Projects, Consulting, Mentoring & Training Regular Speaker @ SQL Server events
Microsoft SQL Server MVP
President of UGISS (Italian SQL Server UG)
Mentor @ SolidQ
Find me here: Blog: http://sqlblog.com/blogs/davide_mauri/default.aspx
Twitter: @mauridb
#sqlsatPordenone
#sqlsat367February 28, 2015
Agenda
Indici e chiavi
Architettura dello storage
Tipi di indici
Utilizzo degli indici
Gestione e manutenzione
Query Tuning
#sqlsatPordenone
#sqlsat367February 28, 2015
Dopo il modello dei dati sono gli strumenti che
– utilizzati in modo corretto - permettono di
ottenere le performance più alte!
Perché una sessione sugli indici?
#sqlsatPordenone
#sqlsat367February 28, 2015
Indici e chiavi
Primary key
Insieme (minimo) delle colonne che permettono
l’identificazione univoca di una riga tra tutte le
altre
Foreign Key
Chiavi Primarie “migrate” in una tabella collegata
#sqlsatPordenone
#sqlsat367February 28, 2015
Indici e chiavi
Index keys
Insieme delle colonne che compongono l’indice
ATTENZIONE!
INDICI e CHIAVI (PK e FK) non hanno nulla in
comune!!!!!
Semplicemente – per motivi di performance – le
chiavi (PK e FK) usano gli indici.
#sqlsatPordenone
#sqlsat367February 28, 2015
Architettura dello storage
I dati presenti in tabelle (ed indici) sono
memorizzati in pagine
grosse circa 8Kb
L’unità di I/O più piccola per SQL Server è la
pagina
Le pagine sono raggruppate in extent
Extent = 8 pagine da 8 Kb
#sqlsatPordenone
#sqlsat367February 28, 2015
Tipi di indici
«Row-Store»
Clustered
Non-Clustered
Included Columns, Filtered Indexes
«Column-Store»
Clustered
Non-Clustered
«Beyond Relational»
XML / Full Text / Spatial / UDT
#sqlsatPordenone
#sqlsat367February 28, 2015
Clustered Indexes
Strutture ad albero rovesciato
B+ TreesRoot
Non-LeafNon-Leaf Non-Leaf
Leaf
(Table Data)
Leaf
(Table Data)
Leaf
(Table Data)
Leaf
(Table Data)
Leaf
(Table Data)
#sqlsatPordenone
#sqlsat367February 28, 2015
Clustered Indexes
Le pagine non-foglia permettono di capire in
quali pagine sottostanti sta il dato che si sta
cercando
Le pagine foglia contengono i dati della tabella
L’indice cluster ordina “fisicamente” i dati
Un solo indice cluster per tabella
#sqlsatPordenone
#sqlsat367February 28, 2015
Clustered Indexes
Di default è messo sulla PK
Ma si può spostare!!!
Può essere costruito su colonne non
univoche
Una tabella senza indice cluster si chiama
“Heap”
#sqlsatPordenone
#sqlsat367February 28, 2015
Non-Clustered (Row-Store) Indexes
Anche in questo caso B+ Trees
Root
Non-LeafNon-Leaf Non-Leaf
Leaf
(Index Data)
Leaf
(Index Data)
Leaf
(Index Data)
Leaf
(Index Data)
Leaf
(Index Data)
#sqlsatPordenone
#sqlsat367February 28, 2015
Non-Clustered (Row-Store) Indexes
GRANDI DIFFERENZE con il cluster
Le pagine foglia NON contengono tutti i dati… …ma solo i valori delle chiavi dell’indice
E’ una struttura dati separata Quindi “pesa” e comporta un overhead
Le pagine foglia contengono dei puntatori alle pagine dati della tabella I puntatori sono diversi a seconda dell’esistenza o
meno di un indice cluster
#sqlsatPordenone
#sqlsat367February 28, 2015
Non-Clustered (Row-Store) Indexes
I puntatori possono essere
Row Ids (se Heap)
Clustering Keys (se esiste indice cluster)
Ergo:
Gli indici non-cluster sono costruiti sull’indice cluster
Nelle loro pagine foglia portano con se la clustering key
#sqlsatPordenone
#sqlsat367February 28, 2015
Non-Clustered (Row-Store) Indexes
Se indice cluster non univoco?
Univocità mantenuta in automatico da SQL…
…costa 4 byte per riga!
ATTENZIONE! Più è grande la chiave di
cluster….
…più spazio occupato nell’indice non cluster!
#sqlsatPordenone
#sqlsat367February 28, 2015
Non-Clustered (Row-Store) Indexes
Included Columns
Colonne i cui valori non sono indicizzati MA sono
inclusi nelle pagine foglia dell’indice
Permettono di migliorare le prestazioni di
ricerca e restituzione dati
«Indici di copertura»
Maggior spazio occupato
Maggior «fatica» nelle fasi di modifica
#sqlsatPordenone
#sqlsat367February 28, 2015
Utilizzo: Indice Cluster
Ordinamenti
Group by
Range Search
Insert Se e solo se l’indice è costruito su valori sempre
crescenti
Assicura che le pagine dei dati siano i memoria
E se non avete *troppe* insert (>15000Batch/sec)
#sqlsatPordenone
#sqlsat367February 28, 2015
Utilizzo: Non Cluster (Row-Store)
Solo se altissima selettività
Selettività = righe interessate / righe totali
Overhead dato dall’operazione di “Bookmark
lookup”
Nell’execution-plan in SQL 2005 non è visibile
come operatore ad-hoc ma come join tra cluster
(o heap) e non-cluster
#sqlsatPordenone
#sqlsat367February 28, 2015
Utilizzo: Di Copertura
Indici di copertura (Covering Index) Non-Cluster
“Misto” (Non-Cluster + Cluster)
Indici che, da solo, è in grado di soddisfare una query “Copre” tutti i campi della query
Prima chiave dell’indice = prima colonna nella clausola where
Migliora di MOLTO le prestazioni in lettura!
#sqlsatPordenone
#sqlsat367February 28, 2015
Utilizzo: Di Copertura
Tramite le “Included Colums” è possible creare
indici di copertura più efficienti
Non metto tutte le colonne della query come chiavi
Metto solo quelle usate per ricerca i valori (WHERE,
GROUP BY)
Le colonne usate in SELECT … FROM le “includo”
Impatta solo sulle dimensioni delle pagine foglia
#sqlsatPordenone
#sqlsat367February 28, 2015
Column-Store Indexes
In-memory column-store index
La riga di dati viene decomposta nelle singole colonne
Dati memorizzati in segmenti e dizionariSource: http://dl.acm.org/citation.cfm?id=1989448
#sqlsatPordenone
#sqlsat367February 28, 2015
Column-Store Indexes
Permettono l’utilizzo di un nuovo modello di
esecuzione «batch mode execution»
Vector-Based Execution
Vettore di circa 1000 righe
Non è importante l’ordine delle colonne
E’ una buona idea mettere TUTTE le colonne
nell’indice
Grossa differenza con i «row-store» indexes
#sqlsatPordenone
#sqlsat367February 28, 2015
Clustered Column-Store Index
Se usato è l’unico indice che può esistere
sulla tabella
Tutte le colonne della tabelle sono
automaticamente incluse
Supporta uno speciale livello di compression
chiamato “Archivial”
Utilizza un ulteriore tipo di file «Delta Store»
per gestire le modifiche ai dati
#sqlsatPordenone
#sqlsat367February 28, 2015
Non-Clustered Column-Store Index
Rende la tabella read-only
Aggiornabile solo tramite partition switching
Uno solo per tabella
#sqlsatPordenone
#sqlsat367February 28, 2015
Utilizzo: Column-Store Indexes
Tipicamente in un Data Warehouse / Data
Mart
Oppure soluzione DSS (Decision Support
System)
Ideale con uno Star Schema
Ideale per query con group by
#sqlsatPordenone
#sqlsat367February 28, 2015
Performance Considerations
Obbiettivo: abbassare il più possibile le
operazioni di I/O
In lettura
In scrittura
Tanti indici
(potenzialmente) più velocità in lettura
(sicuramente) meno velocità in scrittura
#sqlsatPordenone
#sqlsat367February 28, 2015
Performance Considerations
Regola Aurea per OLTP
POCHI MA BUONI
Regola Aurea per OLAP
Tanti quanti ve ne servono, tanto il caricamento è
one-shot
A meno che non dobbiate fare Real-Time BI
Allora: Ottimo Hardware e, se possibile, Column Store
#sqlsatPordenone
#sqlsat367February 28, 2015
Query Tuning
Possibilità di VEDERE se per UNA query mancano degli indici
XML SHOWPLAN MissingIndexesStatistics
DMVs: sys.dm_db_missing_index_*
Altre DMVs & DMFs molto Utili
sys.indexes & sys.indexes_columns
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats()
#sqlsatPordenone
#sqlsat367February 28, 2015
Query Tuning
Serve TEMPO e PAZIENZA
Analisi I/O, TIME, Execution Plans, Cardinality
Estimation
Oltre che un ambiente di TEST il più possibile
uguale a quello di produzione
Le performance dell’I/O sono determinanti
#sqlsatPordenone
#sqlsat367February 28, 2015
Query Tuning
Occhio alle stored procedure
Se i dati sono molto disomogenei come
distribuzione valutare l’uso di WITH RECOMPILE
In alcuni casi (UPDATE/DELETE) gli indici
aiutano a diminuire le dimensione dei lock!
#sqlsatPordenone
#sqlsat367February 28, 2015
Di cosa non abbiamo parlato
XTP HASH & BW-TREE (RANGE) INDEX
Indexes DMVS
Usage
Physical & Operational Info
Missing Indexes
http://sys2dmvs.codeplex.com/
https://sqlserverperformance.wordpress.com/
#sqlsatPordenone
#sqlsat367February 28, 2015
THANKS!
#sqlsatPordenone
#sqlsat367
Feedback form: http://speakerscore.com/sqlsat367