Robert Panther | CGI Germany
SQL Server Indizes gestern und heute
Verschiedene Varianten im Vergleich
VorstellungName 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!
Vorteile
• einfach und effektiv
• Standardoperatoren
• beinhaltet alle Spalten
Nachteile
• wartungsintensiv bei
häufigen Änderungen
Gruppierter / Clustered Index
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)
Müller (4:835:04)
Nagel (4:835:03)
Schmidt
Schultze
Schmidt (4:835:02)
Schmidt (4:834:03)
Schultze (4:835:01)
Schulz (4:834:01)
Nachname Vorname Ort
01 Schultze Sabine München
02 Schmidt Klaus Leipzig
03 Nagel Maria Wiesbaden
04 Müller Hans Bonn
Nachname Vorname Ort
01 Schulz Alfred Berlin
02 Meier Karlheinz Köln
03 Schmidt Stefanie Hamburg
04 Bauer Markus Frankfurt
Seite 834 Seite 835
Datei 4
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
Vorteile
• viele Indizes möglich
• Standardoperatoren
Nachteile
• nicht alle Spalten
enthalten
• viele Indizes → hoher
Wartungsaufwand
Nicht-Gruppierter / Nonclustered Index
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 • 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
Verwendung durch normale Operatoren
(in JOINs, WHERE-Bedingungen etc.)
Wartung • Neuaufbau des kompletten Indexes (Rebuild)
• Neuorganisation der Index-Blattebene (Reorg)
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
Vorteile
• Erspart sowohl Table
Scans als auch JOINs
• Standardoperatoren
• Auch von einzelnen
Tabellen nutzbar
Nachteile
• Hoher Aufwand bei
Aktualisierungen wegen
redundanten Daten
Indizierte Sicht / Indexed View
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
Vorteile
• Performant und
speichersparend bei
wiederkehrenden
Inhalten
Nachteile
• Bei hoher Selektivität
langsamer als
zeilenbasierter Index
• READONLY
• Wartungsaufwand bei
Änderungen hoch
Spaltenbasierter / Columnstore Index
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
Max.
ca. 1 Mio
Max.
ca. 1 Mio
Col.
1
Col.
2
Col. Col.
n… …
Col.
1
Col.
2
Col. Col.
n… …
Col.
1
Col.
2
Col. Col.
n… …
Delta-Store
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
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
Clustered Columnstore Index
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
Vorteile
• Performant und
speichersparend bei
wiederkehrenden
Inhalten
Nachteile
• Bei hoher Selektivität
langsamer als
zeilenbasierter Index
Nonclustered Columnstore Index (V2)
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
Vorteile
• Kann auch LOB-
Datentypen indizieren
• Index über mehrere
Spalten möglich
• Auch nicht-exakte
Suchen möglich
Nachteile
• Separater
Volltextkatalog
erforderlich
• Spezielle Operatoren
• Suchergebnis
manchmal irritierend
Volltext / Fulltext Index
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 XML-
Instanzen in der Spalte
Einschränkungen • 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
Verwendung über spezielle Methoden (query, values, exist etc.)
Wartung Neuaufbau des kompletten Indexes (Rebuild)
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
Vorteile
• Kann XML-Dokumente
durchsuchen
Nachteile
• Nur für XML-Spalten
verwendbar
• Hoher Speicherbedarf
• Keine Komprimierung
XML Index
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
Vorteile
• Kann Geodaten
durchsuchen
• Zusatzmöglichkeiten
über spezielle
Funktionen
Nachteile
• Nur für Geodatentypen
(Geometry/Geography)
verwendbar
• Nicht mit
Standardoperatoren
nutzbar
Räumlicher / Spatial Index
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)
Spaltenbasiert
(Columnstore)
Gruppiert
(Clustered)
• Abfrage von vielen Spalten
• Abfrage von Zeilenbereichen
• Geringe Schreibaktivität auf
Indexschlüsselspalten
• Suchen/filtern/gruppieren
nach einzelnen Spalten
• große Zeilenmengen
• Geringe Schreibaktivität
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
• 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-with-
xml-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
Top Related