Problemstellung
Der Kunde betreibt eine Datenbank mit wenigen Relationen die jedoch sehr viele Datensätze beinhalten (>= 10.000.000). An den Wochenenden werden zeitgesteuerte Aufträge mit dem SQL Server Agent ausgeführt, die den Zustand der Indexe analysieren und – basiserend auf dem Status – die Indexe entweder reorganisieren oder neu aufbauen. Liefen diese Jobs in der Anfangszeit noch zufriedenstellend, mußte festgestellt werden, dass mit Zunahme der Daten die aufzubringende Zeit deutlich größer wurde. Für die Beschreibung des Problems soll die nachfolgende Datenstruktur als Beispiel dienen:CREATE TABLE dbo.tbl_Employees
(
Id int NOT NULL IDENTITY(1, 1),
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) NULL,
LastName varchar(50) NOT NULL,
Phone varchar(20) NULL,
Fax varchar(20) NULL,
EMail varchar(255) NULL,
CONSTRAINT pk_tbl_Employees PRIMARY KEY CLUSTERED (Id)
);
Zusätzlich wurden für diese Relation noch folgende Indexe definiert:
CREATE INDEX ix_tbl_Employees_Last_First_Middle_Phone ON dbo.tbl_Employees (LastName, FirstName, MiddleName, Phone);CREATE INDEX ix_tbl_Employees_Last_First_Middle_Fax ON dbo.tbl_Employees (LastName, FirstName, MiddleName, Fax);CREATE INDEX ix_tbl_Employees_Last_First_Middle_EMail ON dbo.tbl_Employees (LastName, FirstName, MiddleName, EMail);CREATE INDEX ix_tbl_Employees_Last_First_Middle_Inc_Phone ON dbo.tbl_Employees(LastName, FirstName, MiddleName) INCLUDE (Phone);CREATE INDEX ix_tbl_Employees_Last_First_Inc_Phone ON dbo.tbl_Employees(LastName, FirstName) INCLUDE (Phone);CREATE INDEX ix_tbl_Employees_Last_First_Middle_inc_EMail ON dbo.tbl_Employees(LastName, FirstName, MiddleName) INCLUDE (EMail);
Auf die Frage, warum man denn so viele Indexe auf dieser Relation verwendet, wurde geäußert, dass alle kombinierten Abfragemöglichkeiten berücksichtigt werden sollten. Das Hauptaugenmerk liegt auf den Bestandteilen des Namens sowie die unterschiedlichen Abfragen nach Telefon, Fax und Email. Mit den nachfolgenden Methoden konnte die Verwendung von Indexen um 75% verringert werden:
Indexkonsolidierung für bessere Abdeckung von Abfragen
Bei der Begutachtung der Indexe ist zunächst aufgefallen, dass die Indexe fast ausschließlich die Namen der Mitarbeiter abdecken. Die Attribute LastName, FirstName und Middlename wurden in allen Indexes berücksichtigt. Hier haben leider die Entwickler zu viel des Guten getan. Die Abfragepläne haben gezeigt, dass die meisten Abfragen auf den Nach- und den Vornamen ausgerichtet waren.Die Auswertung über die Verwendung von Indexen kann man schnell und einfach mittels der dmv sys.dm_db_index_usage_stats erhalten. Diese dmv zeigt sehr gut auf, wie oft und – ebenfalls sehr wichtig – auf welche Art ein Index verwendet wird. Die Ausführung der nachfolgenden Abfrage in der betroffenen Datenbank
SELECT OBJECT_NAME(i.object_id) AS ObjectName,
i.name AS Indexname,
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates
FROM sys.indexes i INNER JOIN sys.dm_db_index_usage_stats u
ON (
i.object_id = u.object_id AND
i.index_id = u.index_id
)WHERE database_id = db_id() AND i.object_id = OBJECT_ID('dbo.tbl_Employees');
Das Ergebnis der obigen Abfrage zeigte, dass gut die Hälfte der Indexe nicht benutzt wurden.
Es ist erkennbar, dass Indexe, die zusätzliche Attribute wie Telefon, Fax oder EMail einschließen nie benutzt wurden. Jedoch ist die Anzahl der “user_scans” auf dem clustered index “pk_tbl_Employees” sehr hoch. Ein “user_scan” bedeutet immer einen vollständigen Scan der Relation, der natürlich sehr kostenintensiv ist. Bei der Analyse der Abfragen selbst ist aufgefallen, dass 80% aller Abfragen, die die Relation dbo.tbl_Employees betreffen, folgenden Aufbau hatten:
SELECT Id, Firstname, MiddleName, LastName, (Phone / Fax / EMail, …)
FROM dbo.tbl_EmployeesWHERE LastName LIKE 'F%';
SELECT Id, FirstName, MiddleName, LastName, (Phone / Fax / EMail, …)
FROM dbo.tbl_Employees
WHERE Phone / Fax / EMail LIKE 'Bedingung%'
Die obigen Beispiele sind repräsentativer Natur und es fällt auf, dass in jeder Abfrage der Vorname, Mittelname als auch der Nachname abgefragt wird. Zusätzlich werden dann noch die Kommunikationsdaten hinzugenommen. Aus diesem Grund wurden die entsprechenden Indexe ix_…_inc_… vom Kunden erstellt.
Leider hat der Kunde für jede Variante / Kombination einen separaten Index erstellt. Dieses Verfahren muss bei SQL Server aber nicht angewendet werden, da SQL Server selbst auf Basis der abzufragenden Parameter erkennen kann, welcher Index ideal ist.
Grundsätzlich gilt bei Indexen dass..
- das Attribut IM Index für die Navigation vewendet wird
- Attribute im Index immer der Links -> Rechts Regel folgen (Reihenfolge der Attribute!)
- ein Attribut, dass mittels INCLUDE hinzugefügt wird, für Anzeigen verwendet wird
Generischer Index zur Abdeckung aller Varianten
Fassen wir alle oben genannten Indexe zusammen, ist die gemeinsame Schnittmenge aller Attribute wie folgt aufgeteilt:| Index | Indexattribute | Include-Attribute |
| ix_tbl_Employees_Last_First_Middle_inc_EMail | Last / First / Middle | |
| ix_tbl_Employees_Last_First_Inc_Phone | Last / First | Phone |
| ix_tbl_Employees_Last_First_Middle_Inc_Phone | Last / First / Middle | Phone |
| <= nicht verwendet | ||
| <= nicht verwendet | ||
| <= nicht verwendet | ||
| Neu zu erstellender Index | Last / First / Middle | Phone / Fax / EMail |
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ix_tbl_Employees_Last_First_Middle_inc_EMail')
DROP INDEX ix_tbl_Employees_Last_First_Middle_inc_EMail ON dbo.tbl_Employees;
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ix_tbl_Employees_Last_First_Middle_Inc_Phone') DROP INDEX ix_tbl_Employees_Last_First_Middle_Inc_Phone ON dbo.tbl_Employees;
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ix_tbl_Employees_Last_First_Middle_EMail') DROP INDEX ix_tbl_Employees_Last_First_Middle_EMail ON dbo.tbl_Employees;
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ix_tbl_Employees_Last_First_Middle_Fax')
DROP INDEX . . . ;
CREATE INDEX ix_tbl_Employee_FullCover ON dbo.tbl_Employees
(LastName, FirstName, MiddleName) INCLUDE (Phone, Fax, EMail);
Der obige Index deckt – bis auf eine Abfrage – alle Kombinationen von möglichen Abfragen ab. Tatsächlich wird zu 95% in den Namen der Mitarbeiter gesucht. Primär wird nach den Nachnamen, anschließend nach dem Vornamen gesucht. Die Attribute Phone, Fax und Email werden – kaum – mit durchsucht. Somit sollten sie kein Bestandteil des Index selbst sein. Sie dienen lediglich dazu, auf dem Leaf-Level des Index für die Anzeige / Auswahl zur Verfügung zu stehen. Nach 100 Durchläufen von verschiedenen Abfragen sieht die Verwendung der Index wie folgt aus:
Man kann sehr gut erkennen, dass der zuvor erstellte Index ix_tbl_Employee_FullCover bei fast allen Abfragen einen idealen “user_seek” verwendet hat. Bei lediglich einer Abfragevariante konnte er nicht optimal verwendet werden, da sich das zu filternde Attribut nicht im Index selbst befindet sondern nur für die Anzeige verwendet wird. Bei der problematischen Abfrage handelt es sich um das folgende “Konstrukt”:
SELECT Id, FirstName, MiddleName, LastName, (Phone / Fax / EMail, …)
FROM dbo.tbl_Employees
WHERE Phone LIKE '+49 6150%'
Dieser Abfrage kann keinen Index ideal nutzen, da das Attribut [Phone] weder durch den Clustered Index noch durch den Index ix_tbl_Employee_FullCover gedeckt ist. Dennoch entscheidet SQL Server für diesen Index, da alle Attribute, die ausgegeben werden sollen, vollständig im Index (Suchattribute und INCLUDE-Attribute) vorhanden sind.
CREATE INDEX ix_tbl_Employee_Phone ON dbo.tbl_Employees
(Phone) INCLUDE (FirstName, Lastname, MiddleName, Fax, EMail);
Der obige Index ist für Abfragen nach der Telefonnummer ideal, da bei solchen “Reverseabfragen” in aller Regel keine weiteren Details berücksichtigt werden. Um aber einen – teueren KeyLookup auf den Clustered Index zu vermeiden, werden alle Ausgabeattribute mittels INCLUDE auf den Leaf-Pages des Index abgespeichert. Die erneute Ausführung der Analyse über die Indexnutzung zeigt, dass diese Variante ein voller Erfolg ist.
Fazit
Bei der Erstellung von Indexen sollte zunächst geprüft werden, ob sich Indexe nicht konsolidieren lassen. Sehr häufig werden Indexe nur auf Grund eines Attributs, dass eventuell zusätzlich abgefragt werden soll, erstellt. Durch die Überprüfung und den daraus resultierenden Wegfall von 5 Indexen konten wir das Datenvolumen für die betroffene Relation um 70% reduzieren. Damit sind natürlich eine ganze Menge Vorteile hervorgetreten:- weniger Storage für Daten / Backups
- deutlich verbesserte Maintenance Jobs für die Reorganisation der Indexe
Herzlichen Dank für’s Lesen!
| Beispieldaten | http://www.db-berater.de/files/blogs/Optimierung-von-Multi-Column-Indexes.zip |
| multi-column-indexes | http://db-berater.blogspot.de/2013/02/multi-column-indexes-vor-und-nachteile.html |
| sys.dm_db_index_usage_stats | http://msdn.microsoft.com/de-de/library/ms188755.aspx |
Keine Kommentare :
Kommentar veröffentlichen