Robert Panther: SQL Server Indizes

www.informatik-aktuell.de
SQL Server Indizes
Verschiedene Varianten im Überblick
Robert Panther, Executive Consultant
CGI Deutschland Ltd. & Co. KG
© CGI Group Inc. 2013
Agenda
1
Vorstellung
2
Index-Grundlagen
3
Vorstellung der Indexarten
4
Tipps zur Wartung
5
Fazit
Was erwartet Sie?
• Eine Entscheidungshilfe:
Wann ist welcher Indextyp am
besten einzusetzen?
Was Sie nicht erwarten sollten:
• Keine allumfassende
Betrachtung jedes Indextyps
• Keine ausführlichen
Praxisdemos
Vorstellung
Profil: Robert Panther
Ausbildung
Diplom-Informatiker (FH)
Rolle
Executive Consultant
Zertifizierungen
MCITP SQL Server 2008 Developer
MCTS SQL Server 2008 Implementation & Maintenance
MCTS SQL Server 2005 Business Intelligence
MCTS SQL Server 2005 Implementation & Maintenance
Überblick
Schwerpunkte
SQL Server
• Design
• Entwicklung
• Administration
• Performance
• SSIS (ETL)
Sonstige
• .NET (C# & VB)
• Windows Mobile
• Regelmäßiger Speaker auf Konferenzen: BASTA!, SQLCON,
Frankfurter Datenbanktage / IT Tage, Deutsche SQL Server Konferenz (2016)
• Fachbuchautor:
entwickler.press: SQL Server Performance Ratgeber, SQL-Abfragen optimieren
Microsoft-Press: Datenbanken entwickeln mit SQL Server 2012
• Autor von Fachartikeln für diverse Publikationen:
z.B. Windows Developer, dot.net magazin, database pro
• Community: aktives PASS-Mitglied, Leiter der SQL Server Expert Group bei CGI
Vorstellung
Und wer sind Sie?
Was machen Sie mit SQL Server?
• Entwicklung
• Administration
• Business Intelligence
Mit welcher SQL Server-Version arbeiten Sie?
Index-Grundlagen
Es gibt zahlreiche Indexvarianten …
Neulich bei
Ich hätte gerne einen Grande
Nonclustered Rowstore Index mit einem
Schuss Page-Compression to go!
Aber bitte aus biologischem Anbau!
…
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
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
Nachteile
• einfach und effektiv
• Standardoperatoren
• beinhaltet alle Spalten
• 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
Nachteile
• viele Indizes möglich
• Standardoperatoren
• 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
Nachteile
• Erspart sowohl Table Scans
als auch JOINs
• Standardoperatoren
• Auch von einzelnen Tabellen
nutzbar
• 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
Nachteile
• Performant und
speichersparend bei
wiederkehrenden Inhalten
• 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
Nachteile
• Alle Spalten enthalten
• Hohe Kompressionsrate
• Sehr gute Performance
• 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
Nachteile
• Performant und
speichersparend bei
wiederkehrenden Inhalten
• mit klassischen Indizes
kombinierbar
• 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 binaryFeldern durchsuchen
• kann auch Flexionsformen und Synonyme suchen
• nutzt Wordbreaker, um Texte und Begriffe in einzelne
Bestandteile zu zerlegen
• nutzt Stopplisten, um sog. Noise-Words auszufiltern
Volltext / Fulltext Index
Vorteile
Nachteile
• Kann auch LOB-Datentypen
indizieren
• Index über mehrere Spalten
möglich
• Auch nicht-exakte Suchen
möglich
• Separater Volltextkatalog
erforderlich
• Spezielle Operatoren
• Suchergebnis manchmal
irritierend
Verwendungsempfehlung
• wenn komplexe Suchen (z.B. in mehreren Feldern) erforderlich sind
• wenn LOB-Spalten durchsucht werden sollen
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/adven
ture-works/ProductModelDescription' AS "PD")
SELECT
CatalogDescription.query('/PD:ProductDescription/PD:Su
mmary') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist
('/PD:ProductDescription/@ProductModelID[.="19"]') = 1
XML Index
Vorteile
Nachteile
• Kann XML-Dokumente
durchsuchen
• 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
Nachteile
• Kann Geodaten durchsuchen
• Zusatzmöglichkeiten über
spezielle Funktionen
• Nur für Geodatentypen
(Geometry/Geography)
verwendbar
• Nicht mit Standardoperatoren
nutzbar
Verwendungsempfehlung
• Wenn Abfragen auf Geodaten durchzuführen sind
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
Fazit
Vergleich & Weitere Infos
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 des Referenten
•
SQL Server Blog: http://pantheronsql.wordpress.com
•
CGI: http://www.de.cgi.com / http://www.cgi.com
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-with-xml-indexes
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]