SQL Server Indizes gestern und heute

Robert Panther | CGI Germany
SQL Server Indizes gestern und heute
Verschiedene Varianten im Vergleich
Vorstellung
Name
Robert Panther
Rolle
Senior Consultant, CGI
Fachliche
Schwerpunkte
• Datenbanken: Design, Implementierung & Administration
(insbesondere Performance Optimierung und ETL mit SSIS)
• Mobile Anwendungen: Design & Implementierung
• Webanwendungen: Implementierung & Administration
Technologien
• Microsoft SQL Server
• Windows Mobile
• .NET (VB .NET & C#), ASP, ASP.NET
Methoden
Datenbankdesign, Objektorientierte Programmierung, Strukturierte
Programmierung, Schulungskonzeption & Durchführung, Projektleitung
Zertifikate
Informatik Diplom (FH)
MCTS SQL Server 2005 Implementation & Maintenance
MCTS SQL Server 2005 Business Intelligence
MCTS SQL Server 2008 Implementation & Maintenance
MCITP SQL Server 2008 Developer
Spezielles
Autor von mehreren Fachbüchern und Fachartikeln
Leitung der SQL Server Expert Group bei CGI
regelmäßiger Speaker auf Fachkonferenzen
aktives PASS-Mitglied
Vorstellung
Und wer sind Sie?
• Was machen Sie mit SQL Server?
– Entwicklung
– Administration
– Business Intelligence
• Mit welcher SQL Server-Version arbeiten Sie?
–
–
–
–
–
–
2016??
2014
2012
2008 / 2008 R2
2005
2000 oder früher??
Was erwartet Sie?
• Allgemeine Index-Grundlagen
• Ein Überblick über verschiedene Indextypen
mit ihren Vor- und Nachteilen
• Infos zur Indexwartung
• Eine Entscheidungshilfe:
Wann ist welcher Indextyp am besten einzusetzen?
Was Sie nicht erwarten sollten:
• Keine allumfassende Betrachtung jedes Indextyps
• Keine ausführlichen Praxisdemos
1. Index-Grundlagen
Zur Einführung eine kleine Geschichte
Als der Kunde mal wieder über eine zu langsame
Datenbank klagte, bin ich zu meiner bevorzugten
Datenrösterei
gegangen, um dem Kreislauf der Datenbank mit einem
frisch gebrühten Index wieder auf die Sprünge zu
helfen.
Dabei ergab sich folgender Dialog:
Neulich bei SQL-Bucks Coffee
Also dann einen Grande Nonclustered Rowstore
Index mit einem Schuss Page-Compression to go!
Der ideale Index für alles?
Oder wie es in SQL heißt:
CREATE NONCLUSTERED INDEX IX_Name
ON Schema.Tabelle (Spalten)
WITH (DATA_COMPRESSION = PAGE, FILLFACTOR = 90)
Aber bitte aus ökologischem Anbau
und Fair Trade gehandelt!
Es gibt zahlreiche Indexvarianten …
Gemeinsamkeiten der Indexvarianten
• Datenstrukturen, die den Lese-Zugriff beschleunigen
• müssen nach Schreibaktionen aktualisiert werden
• bei einigen Formen ist weitere Pflege erforderlich
(meist in Folge von Defragmentierung)
• die meisten liegen zusätzlich zu den Daten vor
• einige Indexformen ordnen die Daten selbst neu an
Der Index-Steckbrief
Voraussetzungen
Versionen und Editionen von SQL Server
Funktionsweise
Wie arbeitet der Index?
Einschränkungen
Welche Beschränkungen sind zu beachten?
Verwendung
Was muss berücksichtigt werden, damit der Index
genutzt werden kann? (spezielle Operatoren etc.)
Wartung
Wie wird der Index gepflegt?
Danach folgen ggfs. weitere Informationen:
• Syntaxbeispiele (für Erstellung und Verwendung)
• Beschreibung individueller Besonderheiten
• Vor- und Nachteile
• Verwendungsempfehlung
2. Vorstellung der Indexarten
Gruppierter / Clustered Index
Voraussetzungen
in allen Versionen und Editionen verfügbar
Funktionsweise
sortiert die Daten selbst in Reihenfolge der
angegebenen Spalten
Einschränkungen
Es kann nur einen geben!
Verwendung
durch normale Operatoren
(in JOINs, WHERE-Bedingungen etc.)
Wartung
• Neuaufbau des kompletten Indexes (Rebuild)
• Neuorganisation der Index-Blattebene (Reorg)
Gruppierter / Clustered Index
Beispiel für die Erstellung:
CREATE CLUSTERED INDEX PK_Person_BusinessEntityID
ON Person.Person (BusinessEntityID ASC)
Beispiel für die Verwendung:
SELECT *
FROM Person.Person
WHERE BusinessEntityID = 20
Gruppierter / Clustered Index
Besonderheiten:
• Clustered Indizes entsprechen meist (aber nicht
zwingend) dem Primärschlüssel der Tabelle
• daher wird mit dem Clustered Index auch implizit ein
Primary Key Constraint erzeugt
• alternative Syntax:
ALTER TABLE Person.Person
ADD CONSTRAINT PK_Person_BusinessEntityID
PRIMARY KEY CLUSTERED (BusinessEntityID ASC)
• Komprimierung (Row/Page) möglich
Gruppierter / Clustered Index
Gängige Varianten:
a) Id-Spalte vom Typ Integer mit Identitätsspezifikation
als Clustered Primary Key
b) Guid-Spalte mit Default-Wert NewId() als Clustered
Primary Key
c) Bei abhängigen Objekten Id als PK, aber Clustered
Index auf Fremdschlüssel zu übergeordnetem
Objekt
In Einzelfällen kann es aber auch sinnvoll sein, einen
Clustered Index auf ein fachliches Attribut zu legen oder
ganz darauf zu verzichten!
Gruppierter / Clustered Index
Vorteile
• einfach und effektiv
• Standardoperatoren
• beinhaltet alle Spalten
Nachteile
• wartungsintensiv bei
häufigen Änderungen
Verwendungsempfehlung
• bei häufigen Abfragen, die viele Spalten zurückgeben
• Abfragen, die Bereiche von Zeilen zurückgeben
Nicht-Gruppierter / Nonclustered Index
Voraussetzungen
in allen Versionen und Editionen verfügbar
Funktionsweise
zusätzliche sortierte Struktur (Binärbaum),
die auf die eigentlichen Daten verweist
Einschränkungen
• Max. 16 Indexspalten
• Gesamtlänge max. 900 Bytes
• Keine LOB-Datentypen: text, ntext, varchar(max),
nvarchar(max), varbinary(max), xml, image
Verwendung
durch normale Operatoren
(in JOINs, WHERE-Bedingungen etc.)
Wartung
• Neuaufbau des kompletten Indexes (Rebuild)
• Neuorganisation der Index-Blattebene (Reorg)
Nicht-Gruppierter / Nonclustered Index
Beispiel für die Erstellung:
CREATE NONCLUSTERED INDEX
IX_Person_LastName_FirstName_MiddleName
ON Person.Person
(LastName ASC, FirstName ASC, MiddleName ASC)
Beispiel für die Verwendung:
SELECT *
FROM Person.Person
WHERE LastName='Brown'
Nicht-Gruppierter / Nonclustered Index
Besonderheiten:
• nahezu beliebig viele nicht-gruppierte Indizes möglich
• nicht-gruppierte Indizes verweisen lediglich auf die
Daten im Heap oder Clustered Index
• daher zusätzlicher Key Lookup erforderlich
– … sofern Spalten abgefragt werden, die nicht im Index sind
– Gegenmaßnahme: INCLUDE-Spalten
• Komprimierung (Row/Page) möglich
Nicht-Gruppierter / Nonclustered Index
Bauer
Schmidt
Bauer
Müller
Bauer (4:834:04)
Meier (4:834:02)
Schmidt
Schultze
Müller (4:835:04)
Nagel (4:835:03)
Schmidt (4:835:02)
Schmidt (4:834:03)
Schultze (4:835:01)
Schulz (4:834:01)
Datei 4
Seite 834
Seite 835
Nachname Vorname
01 Schulz
Alfred
02 Meier
Ort
Berlin
Nachname Vorname Ort
01 Schultze
Sabine
München
Karlheinz Köln
02 Schmidt
Klaus
Leipzig
03 Schmidt
Stefanie
Hamburg
03 Nagel
Maria
Wiesbaden
04 Bauer
Markus
Frankfurt
04 Müller
Hans
Bonn
Nicht-Gruppierter / Nonclustered Index
Variante: Filtered Index (ab SQL Server 2008)
• Über einfache WHERE-Klausel
• Mit zusätzlichen Einschränkungen verbunden
(z.B. nicht auf Sichten)
• Sinnvoll bei häufigen Abfragen, die nur einen kleinen
Teil der Zeilen betreffen (z.B. bei Sparse Columns)
CREATE NONCLUSTERED INDEX IX_Person_MiddleName
ON Person.Person (MiddleName ASC)
WHERE MiddleName IS NOT NULL
Nicht-Gruppierter / Nonclustered Index
Empfehlung:
• Kombinierte Indizes mit mehreren Spalten nutzen
– Reduzieren die Anzahl der notwendigen Indizes
– Erhöhen die Chance auf abgedeckte Indizes
– Wichtig: Reihenfolge beachten!
• So viel wie nötig, so wenig wie möglich
– Nicht alle Spalten müssen indiziert werden
– Anzahl der Indizes dem Schreib-/Leseverhalten anpassen
Nicht-Gruppierter / Nonclustered Index
Vorteile
• viele Indizes möglich
• Standardoperatoren
Nachteile
• nicht alle Spalten
enthalten
• viele Indizes → hoher
Wartungsaufwand
Verwendungsempfehlung
• wenn häufig nur wenige Zeilen abgefragt werden
• Abfragen durch Indizes abgedeckt werden können
Indizierte Sicht / Indexed View
Voraussetzungen
in allen Versionen und Editionen verfügbar
Funktionsweise
Redundante Kopie (Snapshot) der Daten,
die durch die Sicht abgefragt werden
Einschränkungen
•
•
•
•
•
Verwendung
durch normale Operatoren
(in JOINs, WHERE-Bedingungen etc.)
Wartung
• Neuaufbau des kompletten Indexes (Rebuild)
• Neuorganisation der Index-Blattebene (Reorg)
Sicht muss mit SCHEMABINDING erstellt werden
Index muss UNIQUE CLUSTERED INDEX sein
Nur ein gruppierter Index pro Sicht möglich
Nicht gefiltert
Kein Online Rebuild
Indizierte Sicht / Indexed View
Beispiel für die Erstellung:
CREATE VIEW Person.VW_PersonPhone WITH SCHEMABINDING AS
SELECT pers.FirstName, pers.LastName,
phone.PhoneNumber, ptype.Name AS PhoneType
FROM Person.Person pers
INNER JOIN Person.PersonPhone phone
ON pers.BusinessEntityID=phone.BusinessEntityID
INNER JOIN Person.PhoneNumberType ptype
ON phone.PhoneNumberTypeID=ptype.PhoneNumberTypeID
CREATE UNIQUE CLUSTERED INDEX IX_PersonPhone
ON Person.VW_PersonPhone (PhoneType, LastName,
FirstName, PhoneNumber)
Indizierte Sicht / Indexed View
Beispiel für die Verwendung:
SELECT *
FROM Person.VW_PersonPhone
WHERE PhoneType='Work'
SELECT pers.FirstName, pers.LastName,
phone.PhoneNumber, ptype.Name AS PhoneType
FROM Person.Person pers
INNER JOIN Person.PersonPhone phone
ON pers.BusinessEntityID=phone.BusinessEntityID
INNER JOIN Person.PhoneNumberType ptype
ON phone.PhoneNumberTypeID=ptype.PhoneNumberTypeID
Indizierte Sicht / Indexed View
Besonderheiten:
• durch die Option SCHEMABINDING können keine
Schemaänderungen an den verwendeten Spalten
erfolgen
• selbst Abfragen, die nicht explizit die Sicht
ansprechen, können den Index darauf nutzen
Indizierte Sicht / Indexed View
Vorteile
• Erspart sowohl Table
Scans als auch JOINs
• Standardoperatoren
• Auch von einzelnen
Tabellen nutzbar
Nachteile
• Hoher Aufwand bei
Aktualisierungen wegen
redundanten Daten
Verwendungsempfehlung
• wenn Tabellen häufig gemeinsam abgefragt werden
Spaltenbasierter / Columnstore Index
Voraussetzungen
ab SQL Server 2012 (Enterprise Edition)
Funktionsweise
• Indexdaten werden spaltenbasiert abgelegt
• Index verweist auf RowId oder Clustered Key
Einschränkungen
•
•
•
•
ReadOnly
Nur ein Columnstore Index pro Tabelle
Max. 1024 Spalten
Nicht unterstützte Datentypen:
alle LOB-Typen, sql_variant, alle CLR-Typen
(z.B. hierarchyid, geometry, geography), uniqueidentifier,
rowversion/timestamp, datetimeoffset (scale > 2),
decimal (precision > 18), numeric (precision > 18)
• Außerdem nicht unterstützt: UNIQUE, INCLUDE,
sparse columns, Primary Key, Foreign Key, Filter,
Replication, Change Tracking, CDC, Filestream
Verwendung
durch normale Operatoren (in JOIN, WHERE etc.)
Wartung
Neuaufbau des kompletten Indexes (Rebuild)
Spaltenbasierter / Columnstore Index
Besonderheiten:
• Spaltenreihenfolge in Columnstore Index ist egal
• Index wird stark komprimiert (Vertipaq-Technologie)
• aktive Columnstore Indizes machen die Tabelle
ReadOnly
• kann mit klassischen (zeilenbasierten) Indizes
kombiniert eingesetzt werden
Spaltenbasierter / Columnstore Index
Beispiel für die Erstellung:
CREATE NONCLUSTERED COLUMNSTORE INDEX NCX_Id
ON Person.Person (BusinessEntityID)
Beispiel für die Verwendung (SELECT):
SELECT *
FROM Person.Person
WHERE BusinessEntityID=10
Spaltenbasierter / Columnstore Index
Beispiel für die Verwendung (UPDATE):
ALTER INDEX NCX_Id ON Person.Person DISABLE
GO
UPDATE Person.Person
SET Title='Test'
WHERE BusinessEntityID=10
GO
ALTER INDEX NCX_Id ON Person.Person REBUILD
GO
Spaltenbasierter / Columnstore Index
Vorteile
• Performant und
speichersparend bei
wiederkehrenden
Inhalten
Nachteile
• Bei hoher Selektivität
langsamer als
zeilenbasierter Index
• READONLY
• Wartungsaufwand bei
Änderungen hoch
Verwendungsempfehlung
• Für Tabellen mit seltenen Änderungen, wenigen
Suchspalten und häufig wiederkehrenden Inhalten.
Clustered Columnstore Index
Voraussetzungen
ab SQL Server 2014 (Enterprise Edition)
Funktionsweise
Komplette Tabelle wird spaltenbasiert abgelegt
Einschränkungen
• Nicht unterstützte Datentypen:
alle LOB-Typen, sql_variant, alle CLR-Typen
(z.B. hierarchyid, geometry, geography), uniqueidentifier,
rowversion/timestamp, datetimeoffset (scale > 2),
decimal (precision > 18), numeric (precision > 18)
• Keine anderen Indizes auf der Tabelle möglich
• Außerdem nicht unterstützt: Foreign Key,
(INCLUDE, Filter)
Verwendung
durch normale Operatoren
(in JOINs, WHERE-Bedingungen etc.)
Wartung
• Neuaufbau des kompletten Indexes (Rebuild)
• Neuorganisation der Index-Blattebene (Reorg)
Clustered Columnstore Index
Besonderheiten:
• Es werden keine Spalten explizit angegeben,
da ganze Tabelle als Columnstore abgelegt wird
• Tabelle wird in Gruppen zu maximal 1.048.576 Zeilen
aufgeteilt, die wiederum in Segmente pro Spalte
geteilt werden
• Index wird stark komprimiert (Vertipaq-Technologie)
• Änderungen werden in Deltastore verwaltet und bei
Wartung in eigentlichen Index integriert
Clustered Columnstore Index
Max.
ca. 1 Mio
Col.
1
Col.
2
Max.
ca. 1 Mio
Col.
1
Col.
2
Max.
ca. 1 Mio
Col.
1
Col.
2
Delta-Store
…
Col.
…
Col.
…
Col.
…
Col.
n
…
Col.
n
…
Col.
n
Clustered Columnstore Index
Beispiel für die Erstellung:
CREATE CLUSTERED COLUMNSTORE INDEX CCX
ON Person.Person
Beispiel für die Verwendung:
SELECT *
FROM Person.Person
WHERE BusinessEntityID=10
Clustered Columnstore Index
Vorteile
• Alle Spalten enthalten
• Hohe Kompressionsrate
• Sehr gute Performance
Nachteile
• Einschränkungen auf
Datentypen gelten für
alle Spalten
• Effektivität leidet bei
stark unterschiedlichen
Werten
Verwendungsempfehlung
• Für Tabellen mit einfachen Datentypen und häufig
wiederkehrenden Inhalten.
Nonclustered Columnstore Index (V2)
Voraussetzungen
ab SQL Server 2016 (Enterprise Edition?)
Funktionsweise
• Indexdaten werden spaltenbasiert abgelegt
• Index verweist auf RowId oder Clustered Key
Einschränkungen
• READONLY
• Nicht unterstützte Datentypen:
alle LOB-Typen, sql_variant, alle CLR-Typen
(z.B. hierarchyid, geometry, geography),
rowversion/timestamp
• Nur ein Columnstore Index pro Tabelle
Verwendung
durch normale Operatoren
(in JOINs, WHERE-Bedingungen etc.)
Wartung
Neuaufbau des kompletten Indexes (Rebuild)
Reorg?
Nonclustered Columnstore Index (V2)
Besonderheiten:
• Spaltenreihenfolge in Columnstore Index ist egal
• Index wird stark komprimiert (Vertipaq-Technologie)
• kann mit klassischen (zeilenbasierten) Indizes
kombiniert eingesetzt werden
• Einige Beschränkungen aufgehoben:
Filter, Trigger, UNIQUEIDENTIFIER
• UPDATEABLE: Änderungen werden in Deltastore
verwaltet und bei Wartung in Columnstore integriert
Nonclustered Columnstore Index (V2)
Beispiel für die Erstellung:
CREATE NONCLUSTERED COLUMNSTORE INDEX NCX_Id
ON Person.Person (BusinessEntityID)
Beispiel für die Verwendung:
SELECT *
FROM Person.Person
WHERE BusinessEntityID=10
UPDATE Person.Person
SET Title='Test'
WHERE BusinessEntityID=10
Nonclustered Columnstore Index (V2)
Vorteile
• Performant und
speichersparend bei
wiederkehrenden
Inhalten
Nachteile
• Bei hoher Selektivität
langsamer als
zeilenbasierter Index
Verwendungsempfehlung
• Für Spalten mit häufig wiederkehrenden Inhalten,
nach denen häufig gesucht oder sortiert wird.
Volltext / Fulltext Index
Voraussetzungen
ab SQL Server 2005
Funktionsweise
Auf Datenbankebene wird in ein separater
Volltextkatalog definiert, der pro Tabelle einen
Volltextindex enthalten kann, der wiederum mehrere
Spalten umfassen kann
Einschränkungen
• Komponente muss installiert sein
• Benötigt zusätzlichen Unique Index
Verwendung
spezielle Operatoren zur Abfrage erforderlich
(CONTAINS, FREETEXT)
Wartung
Indizes werden manuell oder asynchron automatisch
aktualisiert, Katalog per REORGANIZE optimiert oder
per REBUILD neu erstellt
Volltext / Fulltext Index
Beispiel für die Erstellung:
CREATE FULLTEXT CATALOG AW2014FullTextCatalog
WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
GO
CREATE FULLTEXT INDEX ON Person.Person
(FirstName LANGUAGE German,
LastName LANGUAGE German)
KEY INDEX PK_Person_BusinessEntityID
ON (AW2014FullTextCatalog) WITH (CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON Person.Person ENABLE
GO
Volltext / Fulltext Index
Beispiel für die Verwendung:
SELECT *
FROM Person.Person
WHERE CONTAINS(Lastname, 'Frank')
SELECT *
FROM Person.Person
WHERE CONTAINS(*, 'Frank')
Volltext / Fulltext Index
Besonderheiten:
• Spaltenreihenfolge ist egal
• setzt pro Tabelle auf Unique Index auf
• Möglichkeit zur Ähnlichkeitssuche
• kann über iFilter auch Dokumente in XML oder
binary-Feldern durchsuchen
• kann auch Flexionsformen und Synonyme suchen
• nutzt Stopplisten, um sog. Noise-Words auszufiltern
Volltext / Fulltext Index
Vorteile
• Kann auch LOBDatentypen indizieren
• Index über mehrere
Spalten möglich
• Auch nicht-exakte
Suchen möglich
Nachteile
• Separater
Volltextkatalog
erforderlich
• Spezielle Operatoren
• Suchergebnis
manchmal irritierend
Verwendungsempfehlung
• wenn komplexe Suchen in mehreren Feldern oder
LOB-Spalten benötigt werden
XML Index
Voraussetzungen
ab SQL Server 2005
Funktionsweise
indiziert alle Tags, Werte und Pfade für die XMLInstanzen in der Spalte
Einschränkungen
•
•
•
•
Verwendung
über spezielle Methoden (query, values, exist etc.)
Wartung
Neuaufbau des kompletten Indexes (Rebuild)
nur für einzelne XML-Spalten
Maximal 249 XML-Indizes pro Tabelle
gruppierter Index auf Tabelle erforderlich
nicht für Variablen oder auf Sichten möglich
XML Index
Besonderheiten:
• primärer XML-Index indiziert alle Tags, Werte und
Pfade
• sekundärer XML-Index baut auf primären XML-Index
auf und indiziert je nach Typ PATH, VALUE oder
PROPERTY
• seit SQL Server 2012 auch selektive XML-Indizes für
einen bestimmten Pfad
XML Index
Beispiel für die Erstellung eines primären XML-Index:
CREATE PRIMARY XML INDEX
PXML_ProductModel_CatalogDescription
ON Production.ProductModel(CatalogDescription);
Beispiel für die Erstellung eines sekundären XML-Index:
CREATE XML INDEX
IXML_ProductModel_CatalogDescription_Path
ON Production.ProductModel(CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR PATH;
XML Index
Beispiel für die Verwendung eines XML-Path-Index:
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adv
enture-works/ProductModelDescription' AS "PD")
SELECT
CatalogDescription.query('/PD:ProductDescription/PD:
Summary') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist
('/PD:ProductDescription/@ProductModelID[.="19"]') = 1
XML Index
Vorteile
• Kann XML-Dokumente
durchsuchen
Nachteile
• Nur für XML-Spalten
verwendbar
• Hoher Speicherbedarf
• Keine Komprimierung
Verwendungsempfehlung
• Wenn XML-Spalten/Dokumente zu durchsuchen sind
Räumlicher / Spatial Index
Voraussetzungen
• ab SQL Server 2008 (alle Editionen)
• mit SQL Server 2012 überarbeitet
Funktionsweise
Raum wird über mehrstufiges Raster aufgeteilt und
dann in Binärbaum abgebildet
Einschränkungen
• Gruppierter Primary Key nötig
• nur für Geodatentypen (geography, geometry)
Verwendung
Über spezielle Funktionen
(z.B. STEquals, STDistance, STIntersects)
Wartung
• Neuaufbau des kompletten Indexes (Rebuild)
• Neuorganisation der Index-Blattebene (Reorg)
Räumlicher / Spatial Index
Besonderheiten (bis SQL 2008 R2):
• GEOMETRY für 2-dimensionale Daten
• GEOGRAPHY für geographische Daten
• Indizierter Raum wird in Raster mit 4 Ebenen geteilt
• Detaillierungsgrad pro Ebene wird bei Indexerstellung
über GRIDS-Parameter angegeben
– LOW: 4x4 = 16 Zellen (DEFAULT)
– MEDIUM: 8x8 = 64 Zellen
– HIGH: 16x16 = 256 Zellen
Räumlicher / Spatial Index
Besonderheiten (ab SQL 2012):
• Detaillierungsgrad wird über AUTO GRID Option
automatisch bestimmt (Default: 12 cells per object für
geography / 8 cells per object für geometry)
• Alternativ auch 8 Ebenen möglich (selektiver)
• bei Verwendung der alten Syntax (manual grid)
werden nach wie vor nur 4 Ebenen genutzt
• page/row compression
(40-50% kleiner, bei 5-10% Performance-Overhead)
Räumlicher / Spatial Index
Beispiel für die Erstellung (ab SQL 2008):
CREATE SPATIAL INDEX SX_Address_SpatialLocation
ON Person.[Address] (SpatialLocation)
USING GEOGRAPHY_GRID
WITH (GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM,
LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16)
Beispiel für die Erstellung (ab SQL 2012):
CREATE SPATIAL INDEX SX_Address_SpatialLocation
ON Person.[Address] (SpatialLocation)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 12)
Räumlicher / Spatial Index
Beispiel für die Verwendung:
DECLARE @Location AS GEOGRAPHY
SELECT TOP 1 @Location=SpatialLocation
FROM Person.[Address]
SELECT TOP 10 SpatialLocation.STDistance(@Location), *
FROM Person.[Address]
ORDER BY SpatialLocation.STDistance(@Location) ASC
Räumlicher / Spatial Index
Vorteile
• Kann Geodaten
durchsuchen
• Zusatzmöglichkeiten
über spezielle
Funktionen
Nachteile
• Nur für Geodatentypen
(Geometry/Geography)
verwendbar
• Nicht mit
Standardoperatoren
nutzbar
Verwendungsempfehlung
• Wenn Abfragen auf Geodaten durchzuführen sind
3. Tipps zur Wartung
Der Füllfaktor / Fillfactor
• Indizes sind in Speicherseiten zu je 8kB abgelegt
• Der Füllfaktor gibt an, wie viel Speicherplatz pro Seite
belegt wird (bezieht sich nur auf Blattebene)
• Mit der Option PAD_INDEX = ON wird der Füllfaktor
auf alle Seiten des Indexbaums angewendet
• REBUILD stellt ursprünglichen Füllfaktor wieder her
• Wird die Seite vorher voll, findet ein Page Split statt
Empfehlung:
• 70-90% (100% bei READONLY)
Wartung von Indizes
• Für die Wartung von Indizes gibt es zwei Varianten:
• REORGANIZE organisiert die Blattebene neu
• REBUILD baut den ganzen Indexbaum neu auf und
stellt dabei den ursprünglichen Füllfaktor wieder her
Empfehlung:
• Indizes regelmäßig (möglichst täglich) warten
• Fragmentierungsgrad berücksichtigen:
– 10-30%: REORGANIZE
– Mehr als 30%: REBUILD
Indexstatistiken
•
•
•
•
•
Zu jedem Index wird eine Statistik erstellt
Bei veralteten Statistiken wird der Index nicht genutzt
Aktualisierung erfolgt explizit oder automatisch
Aktualisierung erfolgt FULLSCAN oder SAMPLED
Statistiken werden auch beim Index REBUILD neu
erstellt
Empfehlung:
• Statistiken regelmäßig aktualisieren (SQL Agent Job)
• Prüfen, ob SAMPLED ausreicht
4. Fazit
Fazit
Allgemein
• Jede Indexart hat ihre eigenen Besonderheiten
• Indizes beschleunigen Lesevorgänge aber
verlangsamen Schreibvorgänge
• Man sollte stets die aktuellen Entwicklungen im Auge
behalten (auch für bestehende Indexvarianten)
• Indizes müssen regelmäßig gewartet werden
– Ein schlecht gewarteter Index ist wertlos
– Dasselbe gilt für veraltete Statistiken
Fazit
Welcher Indextyp ist der richtige?
• Manche Datentypen geben den zu verwendenden
Indextyp vor: XML, geometry/geography, LOB
• Volltextindizes sind außerdem erforderlich, wenn ein
Begriff in mehreren Feldern oder nach ähnlichen
Formen (z.B. Synonymen, Flexionsformen) gesucht
werden soll
• Übrig bleiben die gruppierten oder nicht-gruppierten
Zeilen- oder Spaltenbasierten Indizes …
Fazit
Zeilenbasiert
(Row-Based)
Gruppiert
(Clustered)
•
•
•
Abfrage von vielen Spalten
Abfrage von Zeilenbereichen
Geringe Schreibaktivität auf
Indexschlüsselspalten
Spaltenbasiert
(Columnstore)
•
•
•
•
Nicht-gruppiert
(Non-Clustered)
•
•
•
Abfrage von wenigen Spalten
Abfrage von einzelnen Zeilen
Suche nach verschiedenen
Kriterien
•
•
•
Suchen/filtern/gruppieren
nach einzelnen Spalten
große Zeilenmengen
Geringe Schreibaktivität
Suchen/filtern/gruppieren
nach einzelnen Spalten
große Zeilenmengen
kombinierbar mit
zeilenbasierten Indizes
vor SQL 2016:
keine Schreibaktivität
Links & Literatur
MSDN
•
•
•
•
•
Columnstore Indizes:
http://msdn.microsoft.com/de-de/library/Gg492088(v=SQL.120).aspx
Volltext-Indizes:
http://msdn.microsoft.com/de-de/library/ms187317(v=sql.120).aspx
XML-Indizes: http://msdn.microsoft.com/de-de/library/ms191497.aspx
XML-Datentypmethoden:
http://msdn.microsoft.com/de-de/library/ms190798(v=sql.120).aspx
Räumliche Indizes: http://msdn.microsoft.com/en-us/library/bb895265.aspx
Links & Literatur
Sonstige
•
•
•
•
SQL Server Index Basics:
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics
Uwe Ricken: http://www.db-berater.de/sql-server-blog
Niko Neugebauer: http://www.nikoport.com/columnstore
Getting Started with XML-Indexes:
https://www.simple-talk.com/sql/database-administration/getting-started-withxml-indexes
Links des Referenten
•
•
SQL Server Blog: http://pantheronsql.wordpress.com
CGI: http://www.de.cgi.com / http://www.cgi.com
Links & Literatur
SQL-Abfragen optimieren
Robert Panther
entwickler.press, Juni 2014
ISBN: 978-3868021233
SQL Server Performance Ratgeber
Robert Panther
entwickler.press, Februar 2010
ISBN: 978-3868020304
(Noch wenige Restexemplare zum Sonderpreis direkt beim Autor erhältlich!)
SQL Server 2012 Performance-Optimierung
Holger Schmeling
Addison-Wesley, Mai 2012
ISBN: 978-3827331540
Vielen Dank für Ihre Aufmerksamkeit!
Robert Panther
[email protected]