indici - come, quando, perchè
DESCRIPTION
Ogni database (relazionale e non) necessità di indici per poter fornire delle prestazioni ottimali. I database relazionali non scappano a questa regola ed, anzi, hanno nell'indicizzaione una grandissima opportunità per fornire prestazioni estreme. In questa sessioni vedremo i tipi di indici che abbiamo a disposizione, come si usano e come NON si usano e come possono migliorare le performance delle applicazioni.TRANSCRIPT
brought to you by
Works with SQL Server from 6.5, on BI from 2003
Specialized in Data Solution Architecture, Database Design, Performance Tuning, BI
Microsoft SQL Server MVP
President of UGISS (Italian SQL Server UG)
Mentor @ SolidQ
Regular Speaker @ SQL Server events
Consulting & Training
Davide Mauri
3
Indici e chiavi
Architettura dello storage
Tipi di indici
Utilizzo degli indici
Gestione e manutenzione
Query Tuning
Agenda
4
Perché una sessione sugli indici?
•Dopo il modello dei dati sono gli strumenti che – utilizzati in modo corretto - permettono di ottenere le performance più alte!
Primary key Insieme (minimo) delle colonne che permettono l’identificazione univoca di una riga tra tutte le altre
Foreign KeyChiavi Primarie “migrate” in una tabella collegata
Indici e chiavi
Index keysInsieme delle colonne che compongono l’indice
ATTENZIONE!INDICI e CHIAVI (PK e FK) non hanno nulla in comune!!!!!
Semplicemente – per motivi per performance – le chiavi (PK e FK) usano gli indici.
Indici e chiavi
I dati presenti in tabelle (ed indici) sono memorizzati in paginegrosse circa 8Kb
L’unità di I/O più piccola per SQL Server è la paginaLe pagine sono raggruppate in extent
Extent = 8 pagine da 8 Kb
Architettura dello storage
RelazionaliClustered
Non Clustered
• Included Columns, Filtered Indexes
• Column Store
«Beyond Relational»XML / Full Text / Spatial
Tipi di indici
Strutture ad albero rovesciatoB+ Trees
Clustered Indexes
Root
Non-LeafNon-Leaf Non-Leaf
Leaf (Table Data)
Leaf (Table Data)
Leaf (Table Data)
Leaf (Table Data)
Leaf (Table Data)
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
Clustered Indexes
Di default è messo sulla PKMa si può spostare!!!
Può essere costruito su colonne non univoche
Una tabella senza indice cluster si chiama “Heap”
Clustered Indexes
Anche in questo caso B+ Trees
Non-Clustered (Row-Store) Indexes
Root
Non-LeafNon-Leaf Non-Leaf
Leaf (Index Data)
Leaf (Index Data)
Leaf (Index Data)
Leaf (Index Data)
Leaf (Index Data)
GRANDI DIFFERENZE con il clusterLe 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
Non-Clustered (Row-Store) Indexes
I puntatori possono essereRow 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
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!
Non-Clustered (Row-Store) Indexes
Included ColumnsColonne i cui valori non sono indicizzati MA sono inclusi nelle pagine foglia dell’indice
Permettono di migliorare, in alcuni casi, le prestazioniIndici di copertura
Non-Clustered (Row-Store) Indexes
La riga di dati viene decomposta nelle singole colonne
Dati memorizzati in segmenti e dizionari
Non-Clustered (Column-Store) Indexes
18
Source: http://dl.acm.org/citation.cfm?id=1989448
Non è importante l’ordine delle colonne
E’ una buona idea mettere TUTTE le colonne nell’indice
La tabella diventa read-only !Quindi aggiornamento tramite swtich partition
Non-Clustered (Column-Store) Indexes
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)
Utilizzo: Indice Cluster
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
Utilizzo: Non Cluster
Tipicamente in un Data Warehouse / Data MartOppure soluzione DSS (Decision Support System)
Ideale con uno Star Schema
Ideale per query con group by
Utilizzo: Non Cluster ColumnStore
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!
Utilizzo: Di Copertura
Tramite le “Included Colums” è possible creare indici di copertura più efficientiNon 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
Utilizzo: Di Copertura
Index in Action!
Numero di indiciMax 249 (Non Cluster)
Max 1 (Cluster)
Numero max di colonne per indice: 16
Dimensione massima chiave dell’indice: 900 byte
Le included columns non contano!Max 1023 included columns per index
Limiti
Obbiettivo: abbassare il più possibile le operazioni di I/OIn lettura
In scrittura
Tanti indici (potenzialmente) più velocità in lettura
(sicuramente) meno velocità in scrittura
Regola Aurea OLTPPOCHI MA BUONI
Performance Considerations
Possibilità di VEDERE se per UNA query mancano degli indiciXML SHOWPLAN
MissingIndexesStatistics
DMVs:
sys.dm_db_missing_index_*
Altre DMVs & DMFs molto Utilisys.indexes & sys.indexes_columns
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats()
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 produzioneLe performance dell’I/O sono determinanti
Query Tuning
Occhio alle stored procedureSe 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!
Query Tuning
Grazie a tutti per la partecipazione
Riceverete il link per il download a slide e demo via email nei prossimi giorni
Per contattarmi
Grazie