Dienstag, 12. Februar 2013

Konsolidierung von Indexes

Bei den Wartungsarbeiten an Datenbanken, die an den Wochenenden durchgeführt werden, haben wir festgestellt, dass in einer – relativ – kleinen Datenbank verhätnismäßig viel Zeit für die Reorganisation der Indexe in Anspruch genomnen wird. Bei der Analyse haben wir festgestellt, dass viele Indexe trotz unterschiedlicher Definitionen ihrer Attribute redundant angelegt worden sind. In diesem Artikel möchte ich zeigen, wie Sie die Indexverwendung analysieren können und Indexe durch “Zusammenlegung” optimieren können.

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.
index analysis 01
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
Statt, wie im obigen Beispiel sechs verschiedene Indexe für alle Varianten von Abfrage zu erstellen, würde ein Index die Anforderungen zu 90% abdecken. Auch der Index ix_Employees_Last_First_Inc_Phone kann vollständig ersetzt werden, obwohl er – anders als die anderen Indexe – das Attribut [Middlename] nicht abdeckt. Das nachfolgende Script und die Auswertung der Indexbenutzung zeigt die sich daraus resultierenden Vorteile:

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 EMail
ix_tbl_Employees_Last_First_Inc_Phone Last / First Phone
ix_tbl_Employees_Last_First_Middle_Inc_Phone Last / First / Middle Phone
ix_tbl_Employees_Last_First_Middle_EMail Last / First / Middle / EMail <= nicht verwendet
ix_tbl_Employees_Last_First_Middle_Fax Last / First / Middle / Fax <= nicht verwendet
ix_tbl_Employees_Last_First_Middle_Phone Last / First / Middle / Phone <= nicht verwendet
Neu zu erstellender Index Last / First / Middle Phone / Fax / EMail
Das nachfolgende Script löscht alle Indexe und erstellt einen neuen Index, der (fast) alle Optionen abdeckt:
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:
index analysis 02
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.
index analysis 03

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
Bevor Sie einen neuen Index erstellen, überprüfen Sie zunächst mit der oben genannten Systemview die bereits vorhandenen 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